Page 1 of 1

Mysqli Class

Posted: Wed Nov 17, 2010 5:01 pm
by evans123
Ive been working on this mysqli database class for my website for a little while now and was wondering if anybody would be able to improve and help me sort out a few errors.

When i try and enter a new row into the table, i get the following error message with all mysqli erro reporting on. I know the value is already in the database so it can't insert it but i have no idea on how to handling this kind of error.

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Duplicate entry 'test' for key 1' in ../classes/database.php:214 Stack trace: #0 ../classes/database.php(214): mysqli_stmt->execute() #1 ../templates/header.php(137): nmdb->query('INSERT INTO use...', Array) #2 ../index.php(16): include('/home/champions...') #3 {main} thrown in ../database.php on line 214

How easy would it be to add some form of debugging/error handling functions?

Code: Select all

class nmdb {
	
	/**
	 * The mysqli database object
	 *
	 * @access private
	 * @var object
	*/
	private $mysqli;
	
	/**
	 * The mysqli prepared statement database object
	 *
	 * @access private
	 * @var object
	*/
	private $stmt;
	
	/**
	 * The mysqli prepared statement results set
	 *
	 * @access private
	 * @var object
	*/
	private $meta;
	
	/**
	 * Whether a mysqli transaction is in progress
	 *
	 * @access private
	 * @var bool
	*/
	private $transaction_in_progress;
	
	/**
	 * Whether to show SQL/DB errors
	 *
	 * @since 0.1.0
	 * @var bool
	 */
	var $show_errors = true;
	
	/**
	 * The last error during query.
	 *
	 * @since 0.1.0
	 * @var string
	 */
	var $last_error = '';
	
	/**
	 * Amount of queries made
	 *
	 * @since 0.1.0
	 * @var int
	 */
	var $num_queries = 0;
	
	/**
	 * Count of rows returned by previous query
	 *
	 * @since 0.1.0
	 * @var int
	 */
	var $num_rows = 0;
	
	/**
	 * Count of affected rows by previous query
	 *
	 * @since 0.1.0
	 * @var int
	 */
	var $rows_affected = 0;
	
	/**
	 * Saved result of the last query made
	 *
	 * @since 0.1.0
	 * @var array
	 */
	var $last_query;
	
	/**
	 * Results of the last query made
	 *
	 * @since 0.1.0
	 * @var array|null
	 */
	var $last_result;
	
	/**
	 * Saved queries that were executed
	 *
	 * @since 0.1.0
	 * @var array
	 */
	var $queries;
	
	/**
	 * Whether the database queries are allowed to be executed, when the check_connection() function executes it may change this variable depending on whether there is an active connection to the database.
	 *
	 * @since 0.1.0
	 * @var bool
	 */
	var $ready = false;
	
	/**
	 * Constructor
	 *
	 * @access public
	 * @since 0.1.0
	 */
	public function __construct() {}
	
	/**
	 * Destructor
	 *
	 * @access public
	 * @since 0.1.0
	 */
	public function __destruct() { $this->close(); }
	
	/**
	 * Opens a connection to the database
	 *
	 * @access private
	 * @since 0.1.0
	 */
	private function connect() {
		// Check to see if the database object has already been assigned to the mysqli variable.
		if( !$this->mysqli )
			$this->mysqli = @new mysqli(xxx, xxx, xxx, xxx);
		// Check to see if we made a successful connection to the database
		return $this->mysqli->ping();
	}	
	
	/**
	 * Closes the connection to the database
	 *
	 * @access private
	 * @since 0.1.0
	 * @var bool
	 */
	private function close() {
		// Check to see if mysqli database object has been assigned
		if( $this->mysqli ) {
			//Check to see if the connection is alive and then close the database connection
			if( $this->mysqli->ping() ) {
				if( $this->mysqli->close() ) {
					$this->mysqli = NULL;
					$this->stmt = NULL;
					$this->meta = NULL;
					return true;
				}
			}
		}
		return false;
	}	
 	
	/**
	 * Handles the dubugging of the database
	 *
	 * @access private
	 * @since 0.1.0
	 */
	private function error() {
		if( $this->mysqli )
			if( $this->mysqli->errno ) {
				if($this->show_errors)
				$this->last_error = 'Mysqli - ['.$this->mysqli->errno.'] '.$this->mysqli->error;
				echo $this->last_error;
			}
		if( $this->stmt )
			if( $this->stmt->errno ) {
				if($this->show_errors)
				$this->last_error = 'Stmt - ['.$this->stmt->errno.'] '.$this->stmt->error;
				echo $this->last_error;
			}
	}
	
	/**
	 * Query the database
	 *
	 * @access public
	 * @since 0.1.0
	 * @var array
	 */
	public function query($sql, $params) {
		mysqli_report(MYSQLI_REPORT_ALL);
		// Try and connect to database
		if( !$this->connect() ) {
			$this->error();
		} else {
			// Prepare statement, if an error occurs go to error function
			if($this->stmt = $this->mysqli->prepare($sql) or $this->error()) {
			// Bind parameters if the sql query requires them
			if( $this->stmt->param_count > 0 )
				if(!$this->bind_params($params))
					$this->error();	
		
			// Execute prepared query
			if( !$this->stmt->execute() )
				$this->error();
				
			// Store result set from prepared statement
			$this->stmt->store_result();
				
			// Log relevant information
			$this->queries[] = array('query' => $sql, 'parameters' => $params);
			$this->rows_affected = $this->stmt->affected_rows; // Returns number of rows affected by Insert, Update or Delete
			$this->num_rows = $this->stmt->num_rows; // Returns number of rows in the statements result set.
			$this->num_queries++;
			
			// Get result set metadata from the prepared statement
			$this->meta = $this->stmt->result_metadata();
			
			// Bind results if there are rows available. If the query doesn't output and results we need to assign an empty array to last_result.
			if($this->stmt->num_rows > 0)
				$this->last_result = $this->bind_results();
			else
				$this->last_result= array();
			
			// Free the stored result from memory for the given statement handle
			$this->stmt->free_result();
			
			// Close prepared statement, mysqli connection and result metadata
			$this->stmt->close();
			
		}
			$this->close();
			
			// Reset database object variables to avoid having problems when doing multiple queries.	
			$this->stmt = NULL;
			$this->mysqli = NULL;
			$this->meta = NULL;
			
		}

	}

	/**
	 * Dynamically bind parameters
	 *
	 * @access private
	 * @since 0.1.0
	 * @var array
	 */
	 
	 private function bind_params($params) {
	
		 // Check to see if the parameters passed are in an array and if the array contains any elements
	 	if( is_array($params) && count($params) > 0 ) {

			// Initial string with the types of the parameters
			$types = '';
			
			// Loop through each of the elements in the $params array, determing its type.
			foreach( $params as $param ) {   
				
				if(is_int($param))
					$types .= 'i'; // Interger
				elseif (is_float($param))
					$types .= 'd'; // Double
				elseif (is_string($param))
					$types .= 's'; // String
				else
					$types .= 'b'; // Blob or unkown
			}
	 	}
		
		// The first element in the array needs to be the string of types.
		$bind_names[] = $types;

		// Loop through the parameters in the array, adding them to the $bind_names array.
		for ( $i=0; $i<count($params); $i++ ) {

			// We need to give each element an arbitrary name because we don't have any idea on what to call them based upon there variable
            $bind_name = 'bind' . $i;       //give them an arbitrary name
			
			// Add the parameter to the variable variable
            $$bind_name = $params[$i];
			
			// Associate the varaible as an element in the array
            $bind_names[] = &$$bind_name;
        }

		// Call the function bind_param with dynamic params
		return call_user_func_array(array($this->stmt, 'bind_param'), $bind_names);
	 }
	 
	/**
	 * Dynamically bind results
	 *
	 * @access private
	 * @since 0.1.0
	 * @var array
	 */

	 private function bind_results() {
		 
		 	$results = array();

               while ( $field = $this->meta->fetch_field() ) {
                   $parameters[] = &$row[$field->name];
               } 
       
            call_user_func_array(array($this->stmt, 'bind_result'), $this->refValues($parameters));

            while ( $this->stmt->fetch() ) { 
               $x = array(); 
			   print_r($row);
               foreach( $row as $key => $val ) { 
                  $x[$key] = $val; 
               } 
               $results[] = $x; 
            }
			return $results;
			
	}
 
     function refValues($arr){
        if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
        {
            $refs = array();
            foreach($arr as $key => $value)
                $refs[$key] = &$arr[$key];
            return $refs;
        }
        return $arr;
    }
	
}
 

Re: Mysqli Class

Posted: Wed Nov 17, 2010 8:37 pm
by Luke
First of all, can you give me some example code? Show me how you are using this class. I want to see it in action.

Second, are you familiar with the use of exceptions? I'm just curious.

And third, while I do advocate building your own class because it will teach you a lot more about OOP and Design theory than using a pre-existing library, I will advise that you also try out a few existing database classes/libraries. There are tons of them out there (believe me, this wheel has already been invented, and reinvented). If nothing else, it may give you some inspiration on how to design your own class.

After you've posted some example code I'll come back and see if I can give you some advice on error handling. Thanks! :)

Re: Mysqli Class

Posted: Wed Nov 17, 2010 8:38 pm
by Luke
Oh and also, I'm moving this to Theory and design because it is actually more of a PHP theory/design question than a database question. ;)

Re: Mysqli Class

Posted: Thu Nov 18, 2010 2:12 am
by evans123
You basically query the database using the statements below, you will be given an array of anwsers, for any query that has a result set i.e a SELECT statement.

Query:

Code: Select all

__autoload('database'); // Load class
$db = new nmdb; // Reference to class

$db->query('SELECT timestamp FROM xx', array());
$db->query('SELECT ip FROM xx WHERE ip = ?', array($_SERVER['REMOTE_ADDR']));
$db->query('SELECT xx FROM users', array());
$db->query('INSERT INTO xx VALUES (?, ?, ?, ?, ?, ?)', array("test", "test", 2,'', 'aaa@aaa.com',time()));
Result Example:

Code: Select all

Array ( [0] => Array ( [username] => test ) [1] => Array ( [username] => test1 ) ) 
Yeah i have come across execeptions before, not quite sure how to correctly set them up, but i don't want to use the die(), id rather just echo the error code and then exit the script.

Re: Mysqli Class

Posted: Thu Nov 18, 2010 3:50 am
by evans123

Code: Select all

try {
$db->query('SELECT timestamp FROM xxx', array());
print_r($db->last_result);
echo "<br/>";
echo "Rows Affected:".$db->rows_affected."<br/>";
echo "Num Rows:".$db->num_rows."<br/>";
echo "Num Queries:".$db->num_queries."<br/>";

$db->query('SELECT ip FROM xxx WHERE ip = ?', array($_SERVER['REMOTE_ADDR']));
print_r($db->last_result);
echo "<br/>";
echo "Rows Affected:".$db->rows_affected."<br/>";
echo "Num Rows:".$db->num_rows."<br/>";
echo "Num Queries:".$db->num_queries."<br/>";

$db->query('SELECT username FROM xxx', array());
print_r($db->last_result);
echo "<br/>";
echo "Rows Affected:".$db->rows_affected."<br/>";
echo "Num Rows:".$db->num_rows."<br/>";
echo "Num Queries:".$db->num_queries."<br/>";

$db->query('INSERT INTO xxx VALUES (?, ?, ?, ?, ?, ?)', array("test", "test", 2,'', 'aaa@aaa.com',time()));
print_r($db->last_result);
echo "<br/>";
echo "Rows Affected:".$db->rows_affected."<br/>";
echo "Num Rows:".$db->num_rows."<br/>";
echo "Num Queries:".$db->num_queries."<br/>";

echo print_r($db->queries);

}
catch(Exception $e){
	echo $e->getMessage();
	echo "An unexcepted error occured";
}

	}
This actually catches the errors now, and it doesnt die anymore, so the page is still visible. I take it that i didn't need to close the mysqli connection or stmt?

So if i got my code to recognize when an error occured then i could just say that the sql couldnt be executed and therefore no results will be returned, or an insert error occured of some kind.

Re: Mysqli Class

Posted: Thu Nov 18, 2010 6:11 am
by VladSun

Re: Mysqli Class

Posted: Thu Nov 18, 2010 7:14 am
by evans123
When you change a variable in a try block it reverts back to its previous value when you next access it, what's the point in this. Almost renders exceptions as pointless surely?

Re: Mysqli Class

Posted: Thu Nov 18, 2010 7:29 am
by VladSun
8O I can't get what you mean:

Code: Select all

$a = 1;

try
{
	$a = 2;
	0/0;
}
catch (Exception $E)
{
	echo $E->getMessage();
}

var_dump($a);
[text]int 2[/text]

Re: Mysqli Class

Posted: Thu Nov 18, 2010 7:49 am
by evans123
I probably coded something wrong. It variable would just change back to its state before the try and catch block.

Can you give me a brief description on the correct place to use and why to use exceptions.

Should i turn all error_reporting off?

Re: Mysqli Class

Posted: Thu Nov 18, 2010 9:26 am
by VladSun
:oops:
Should be:

Code: Select all

<?php

$a = 1;

try
{
	$a = 2;
	throw new Exception('Something is really wrong :)');
}
catch (Exception $E)
{
	echo $E->getMessage();
}

var_dump($a);
Apperantly, division by zero rises an E_WARNING only ...

Re: Mysqli Class

Posted: Thu Nov 18, 2010 9:29 am
by VladSun
evans123 wrote:I probably coded something wrong. It variable would just change back to its state before the try and catch block.

Can you give me a brief description on the correct place to use and why to use exceptions.

Should i turn all error_reporting off?
Exceptions are used only in exceptional situations :)

http://ciaweb.net/pear-exception-use-guidelines.html

Re: Mysqli Class

Posted: Thu Nov 18, 2010 9:33 am
by evans123
Say i have an insert statement which fails how can i assign this to the query result to say that it failed to execute?