script che visualizza una tabella modifica i dati e salva   facebook   twitter   google+

Discussioni su PHP, Apache e MySQL

Moderatore: spacemarc

Rispondi
ciarliradio
Membro junior
Messaggi: 1
Iscritto il: 18 gen 2010, 21:11

script che visualizza una tabella modifica i dati e salva

Messaggio da ciarliradio » 18 gen 2010, 21:46

Sto cercando di capire perchè non funziona questa cosa
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 :confused:

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() { }
}

Rispondi