questo script dovrebbe connettendosi un MYSQL dovrebbe vedere delle tabelle
e permettere di modificare dei dati entro contenuti lanciando index.php
ma mi richiama delle tabelle con il nome non corretto , sicuramente e colpa del ( testo evidenziato in grassetto)
ma non riesco a risolvere
quello che vorrei ottenere e visualizzare , modificare , salvare i record della tabella
scusate ma sono un po gnocco a 57 anni .......
qualcuno mi può aiutare
index php
- Codice: Seleziona tutto
<?php
function debug($var) {
echo '<pre>'. print_r($var, 1) .'</pre>';
}
// Require and initialize
require_once('database.php');
$db = Database::getInstance();
[b]$results = $db->select('all', array('games', 'genres'), array(
'fields' => array(
'Games.*',
'Genres.name AS genreName',
'Genres.id as genreId'[/b]
),
'conditions' => array('Games.genre_id' => 'Genres.id')
), 25, 0);
debug($results);
darabse.php
- Codice: Seleziona tutto
<?php
/**
* database.php
*
* A wrapper class for accessing, abstracting and manipulating a MySQL database.
*
* Copyright 2006-2009, Miles Johnson - http://www.milesj.me
* Licensed under The MIT License - Modification, Redistribution allowed but must retain the above copyright notice
* @link http://www.opensource.org/licenses/mit-license.php
*
* @package DataBasic - Basic Database Access
* @created January 30th 2008
* @version 1.10
* @link http://www.milesj.me/resources/script/database-handler/
* @changelog
* - 02/08/09 v1.10 Added a delete() method and fixed bugs within select(). First official public release
* - 02/01/09 v1.9 Added a select() method. Also added backticks (`) to table names and column names
* - 11/20/08 v1.8 Added update() and insert() methods for easier use
* - 06/27/08 v1.7 Fixed an issue with magic_quotes and mysql_real_escape_string() in bind()
* - 04/28/08 v1.6 Added the optimize() function to clean overhead
* - 04/11/08 v1.5 Rewrote the execute() method; Added definitions for external configuration
* - 03/24/08 v1.4 Added a debugging system which stores queries in an array, accessible with getQueries()
* - 03/20/08 v1.3 Added the getInstance() singleton to manage all database connections in one instance
*/
define('DB_HOST', 'localhost');
define('DB_NAME', '+++++++++++');
define('DB_USER', '+++++++++');
define('DB_PASS', '********');
class Database {
/**
* Contains the database instance
* @property instance
*/
private static $instance;
/**
* Holds the database connection link
* @property mixed
*/
private $connection = NULL;
/**
* Contains database connection information
* @property array
*/
private $db = array(
'server' => DB_HOST,
'database' => DB_NAME,
'username' => DB_USER,
'password' => DB_PASS
);
/**
* A list of all queries being processed on a page
* @property array
*/
private $queries;
/**
* Number of successful queries executed
* @property int
*/
private $executed;
/**
* Holds data for SQLs and binds
* @property array
*/
private $data;
/**
* If enabled, logs all queries and executions
* @property boolean
*/
private $debug = true;
/**
* Connects to the database on class initialize; use getInstance()
* @return void
*/
private function __construct() {
$this->connect();
}
/**
* Connects and returns a single instance of the database connection handle
* @return instance
*/
public static function getInstance() {
if (!isset(self::$instance)){
self::$instance = new Database();
}
return self::$instance;
}
/**
* Attempts to connect to the MySQL database
* @return boolean
*/
private function connect() {
$this->queries = array();
$this->executed = 0;
$this->connection = mysql_connect($this->db['server'], $this->db['username'], $this->db['password']);
if ($this->connection) {
if (!mysql_select_db($this->db['database'], $this->connection)) {
trigger_error('Database::connect(): '. mysql_error() .'. ('. mysql_errno() .')', E_USER_ERROR);
}
}
return $this->connection;
}
/**
* Binds a paramater to the sql string
* @param string $sql
* @param array $params
* @param boolean $clean
* @return string
*/
public function bind($sql, $params, $clean = true) {
if (is_array($params) && isset($sql)) {
foreach ($params as $param => $value) {
if (is_string($param) && isset($value)) {
if (substr($param, 0, 1) != ':') {
$param = strval(':'. $param);
}
if ($clean === true) {
$value = $this->clean($value);
}
$sql = str_replace($param, trim($value), $sql);
}
}
} else {
trigger_error('Database::bind(): Params given are not an array', E_USER_WARNING);
}
return $sql;
}
/**
* Cleans an sql string to prevent unwanted injection
* @param string $value
* @return string
*/
public function clean($value) {
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
if (function_exists('mysql_real_escape_string')) {
$value = mysql_real_escape_string($value, $this->connection);
} else {
$value = mysql_escape_string($value);
}
return $value;
}
/**
* Executes the sql statement after being prepared and binded
* @param string $sql
* @param int $dataBit
* @return mixed
*/
public function execute($sql, $dataBit) {
$result = mysql_query($sql, $this->connection);
unset($this->data[$dataBit]);
if ($result === false) {
$failure = mysql_error() .'. ('. mysql_errno() .')';
trigger_error('Database::execute(): '. $failure, E_USER_ERROR);
} else {
++$this->executed;
}
if ($this->debug === true) {
$this->queries[] = array(
'statement' => $sql,
'timestamp' => date('m/d/Y g:i:sa', time()),
'executed' => (isset($failure)) ? $failure : 'true',
);
}
return $result;
}
/**
* A basic method to select data from a database; can either return many rows, one row, or a count
* @param string $finder
* @param string $tableName
* @param array $options - fields, conditions, order, group
* @param int $limit
* @param int $offset
* @return mixed
*/
public function select($finder, $tableName, $options, $limit = NULL, $offset = 0) {
$execute = true;
$dataBit = time();
if (!empty($tableName)) {
// Table
if (is_array($tableName)) {
$tables = array();
foreach ($tableName as $as => $table) {
if (is_int($as)) $as = $table;
$tables[] = $this->__backtick($table) ." AS ". $this->__backtick(ucfirst($as));
}
$table = implode(', ', $tables);
} else {
$table = $this->__backtick($tableName);
}
// Fields
if ($finder == 'count') {
$fields = "COUNT(*) AS `count`";
} else {
if (!empty($options['fields']) && is_array($options['fields'])) {
$this->__buildFields($dataBit, $options['fields'], 'select');
$fields = implode(', ', $this->data[$dataBit]['fields']);
} else {
if (is_array($tableName)) {
$fields2 = array();
foreach ($tableName as $as => $t) {
if (is_int($as)) $as = $t;
$fields2[] = $this->__backtick(ucfirst($as)) .".*";
}
$fields = implode(', ', $fields2);
} else {
$fields = '*';
}
}
}
$sql = "SELECT ". $fields ." FROM ". $table;
// Conditions
if (!empty($options['conditions'])) {
if (is_array($options['conditions'])) {
$this->__buildConditions($dataBit, $options['conditions']);
} else {
$execute = false;
trigger_error('Database::select(): Conditions/Where clause supplied must be an array', E_USER_WARNING);
}
$sql .= " WHERE ". implode(' AND ', $this->data[$dataBit]['conditions']);
}
// Order
if (!empty($options['order'])) {
if (is_array($options['order'])) {
$orders = array();
foreach ($options['order'] as $column => $dir) {
$orders[] = $this->__backtick($column) ." ". $dir;
}
$order = implode(', ', $orders);
$sql .= " ORDER BY ". $order;
}
}
// Group
if (!empty($options['group'])) {
if (is_array($options['order'])) {
$groups = array();
foreach ($options['order'] as $order) {
$groups[] = $this->__backtick($order);
}
$group = implode(', ', $groups);
} else {
$group = $this->__backtick($options['order']);
}
$sql .= " GROUP BY ". $group;
}
// Limit, offset
if ($finder == 'first') {
$limit = 1;
$offset = NULL;
}
if (is_int($limit) && isset($limit)) {
$sql .= " LIMIT ";
if (is_int($offset) && isset($offset)) {
$sql .= ":offset,";
$this->data[$dataBit]['binds'][':offset'] = $offset;
}
$sql .= ":limit";
$this->data[$dataBit]['binds'][':limit'] = $limit;
}
// Binds
$sql = $this->bind($sql, $this->data[$dataBit]['binds']);
// Execute query and return results
if ($execute === true) {
$query = $this->execute($sql, $dataBit);
if ($finder == 'count' || $finder == 'first') {
if ($fetch = $this->fetch($query)) {
return ($finder == 'count') ? $fetch['count'] : $fetch;
}
} else {
$rows = array();
while ($row = $this->fetchAll($query)) {
$rows[] = $row;
}
return $rows;
}
}
}
return NULL;
}
/**
* Builds a suitable SQL UPDATE query and executes
* @param string $tableName
* @param array $columns
* @param array $conditions
* @param int $limit
* @return result
*/
public function update($tableName, $columns, $conditions, $limit = 1) {
$execute = true;
$dataBit = time();
if (!empty($tableName) && !empty($columns) && !empty($conditions)) {
$this->data[$dataBit]['binds'][':limit'] = $limit;
// Create columns => value
if (is_array($columns)) {
$this->__buildFields($dataBit, $columns, 'update');
} else {
$execute = false;
trigger_error('Database::update(): Columns/Fields supplied must be an array', E_USER_WARNING);
}
// Build the where clause
if (is_array($conditions)) {
$this->__buildConditions($dataBit, $conditions);
} else {
$execute = false;
trigger_error('Database::update(): Conditions/Where clause supplied must be an array', E_USER_WARNING);
}
// Bind the SQL query
$sql = "UPDATE ". $this->__backtick($tableName) ." SET ". implode(', ', $this->data[$dataBit]['fields']) ." WHERE ". implode(' AND ', $this->data[$dataBit]['conditions']) ." LIMIT :limit";
$sql = $this->bind($sql, $this->data[$dataBit]['binds']);
if ($execute === true) {
return $this->execute($sql, $dataBit);
}
}
return NULL;
}
/**
* Builds a suitable SQL INSERT query and executes
* @param string $tableName
* @param array $columns
* @return mixed
*/
public function insert($tableName, $columns) {
$execute = true;
$dataBit = time();
if (!empty($tableName) && !empty($columns)) {
if (is_array($columns)) {
$this->__buildFields($dataBit, $columns, 'insert');
} else {
$execute = false;
trigger_error('Database::insert(): Columns/Fields supplied must be an array', E_USER_WARNING);
}
// Bind the SQL query
$sql = "INSERT INTO ". $this->__backtick($tableName) ." (". implode(', ', $this->data[$dataBit]['fields']) .") VALUES (". implode(', ', $this->data[$dataBit]['values']) .")";
$sql = $this->bind($sql, $this->data[$dataBit]['binds']);
if ($execute === true) {
if ($query = $this->execute($sql, $dataBit)) {
return $this->getLastInsertId();
}
return NULL;
}
}
return NULL;
}
/**
* Builds a suitable SQL DELETE query and executes
* @param string $tableName
* @param array $conditions
* @param int $limit
* @return mixed
*/
public function delete($tableName, $conditions, $limit = 1) {
$execute = true;
$dataBit = time();
if (!empty($tableName) && !empty($conditions)) {
// Build the where clause
if (is_array($conditions)) {
$this->__buildConditions($dataBit, $conditions);
} else {
$execute = false;
trigger_error('Database::delete(): Conditions/Where clause supplied must be an array', E_USER_WARNING);
}
// Build the SQL query
$sql = "DELETE FROM ". $this->__backtick($tableName) ." WHERE ". implode(' AND ', $this->data[$dataBit]['conditions']);
// Limit, offset
if (is_int($limit) && isset($limit)) {
$sql .= " LIMIT :limit";
$this->data[$dataBit]['binds'][':limit'] = $limit;
}
$sql = $this->bind($sql, $this->data[$dataBit]['binds']);
if ($execute === true) {
return $this->execute($sql, $dataBit);
}
}
return NULL;
}
/**
* Fetches the first row from the query
* @param result $query
* @param bool $asObject
* @return array
*/
public function fetch($query, $asObject = false) {
while ($row = $this->fetchAll($query, $asObject)) {
return $row;
}
}
/**
* Fetches all rows from the query
* @param result $query
* @param bool $asObject
* @return array
*/
public function fetchAll($query, $asObject = false) {
if ($asObject === true) {
$result = mysql_fetch_object($query);
} else {
$result = mysql_fetch_assoc($query);
}
return $result;
}
/**
* Count the number of returned rows from the query result
* @param result $query
* @return int
*/
public function countRows($query) {
return intval(mysql_num_rows($query));
}
/**
* Gets the last inserted id from a query
* @return int
*/
public function getLastInsertId() {
return intval(mysql_insert_id($this->connection));
}
/**
* Returns an array of queries that have been executed; used with debugging
* @return array
*/
public function getQueries() {
return $this->queries;
}
/**
* Returns the total successful queries executed
* @return int
*/
public function getExecuted() {
return intval($this->executed);
}
/**
* Optimizes and cleans all the overhead in the database
* @return void
*/
public static function optimize() {
$db = self::getInstance();
$query = $db->execute('SHOW TABLES');
while ($table = $db->fetchAll($query)) {
$db->execute('OPTIMIZE TABLE '. $table[0]);
}
}
/**
* Builds the data array for the specific SQL
* @param int $dataBit
* @param array $columns
* @param string $type
* @return void
*/
private function __buildFields($dataBit, $columns, $type = 'select') {
switch ($type) {
case 'update':
foreach ($columns as $column => $value) {
$this->data[$dataBit]['fields'][] = $this->__backtick($column) ." = ". $this->__formatColumnType($value, $column);
$this->data[$dataBit]['binds'][':'. $column] = $value;
}
break;
case 'insert':
foreach ($columns as $column => $value) {
$this->data[$dataBit]['fields'][] = $this->__backtick($column);
$this->data[$dataBit]['values'][] = $this->__formatColumnType($value, $column);
$this->data[$dataBit]['binds'][':'. $column] = $value;
}
break;
case 'select':
foreach ($columns as $column) {
if (strpos(strtoupper($column), ' AS ')) {
$column = str_replace(' as ', ' AS ', $column);
$parts = explode('AS', $column);
$this->data[$dataBit]['fields'][] = $this->__backtick(trim($parts[0])) .' AS '. $this->__backtick(trim($parts[1]));
} else {
$this->data[$dataBit]['fields'][] = $this->__backtick($column);
}
}
break;
}
}
/**
* Builds the data array conditions for the SQL
* @param int $dataBit
* @param array $conditions
* @return void
*/
private function __buildConditions($dataBit, $conditions) {
foreach ($conditions as $column => $clause) {
if (is_array($clause)) {
$operator = (isset($clause['operator'])) ? $clause['operator'] : '=';
$value = (isset($clause['value'])) ? $clause['value'] : '';
} else {
$operator = '=';
$value = $clause;
}
if (strpos($value, '.') !== false) {
$valueClean = $this->__backtick($value);
} else {
$valueClean = $this->__formatColumnType($value, $column, 'where_');
}
$this->data[$dataBit]['conditions'][] = $this->__backtick($column) ." ". $operator ." ". $valueClean;
$this->data[$dataBit]['binds'][':where_'. $column] = $value;
}
}
/**
* Determines the column values type
* @param mixed $value
* @param string $column
* @return mixed
*/
private function __formatColumnType($value, $column, $prefix = '') {
// Empty
if (empty($value) || !isset($value)) {
$cleanValue = "''";
// NULL
} else if ($value === NULL) {
$cleanValue = 'NULL';
// NOW(), etc
} else if (strtoupper($value) == $value && strpos($value, '(') !== false && strpos($value, ')') !== false) {
$cleanValue = (string)$value;
// Boolean
} else if (is_bool($value)) {
$cleanValue = (bool)$value;
// Integers, Numbers
} else if (is_numeric($value) || is_int($value)) {
$cleanValue = ":". $prefix . $column;
// Strings
} else if (is_string($value) && strlen($value) > 0) {
$cleanValue = "':". $prefix . $column ."'";
}
return $cleanValue;
}
/**
* Backticks columns, tables, etc
* @param string $var
* @return string
*/
private function __backtick($var, $tick = true) {
$var = strval(trim($var));
if (strpos($var, '.') !== false) {
$v = explode('.', $var);
$var = "`". $v[0] ."`.";
if ($v[1] == '*') {
$var .= '*';
} else {
$var .= "`". $v[1] ."`";
}
} else {
$var = ($tick === true) ? "`$var`" : $var;
}
return $var;
}
/**
* Disable clone from being used
*/
private function __clone() { }
}