Showing posts with label multi_query. Show all posts
Showing posts with label multi_query. Show all posts

Sunday, November 8, 2009

PHP: Extending mysqli for Simplified Multi Queries and Data Retrieval

Extending mysqli
Foreward (to my first php tutorial)
I decided to make this my first php/mysql tutorial, because in learning php/mysql I had a lot of problems with mysqli because I was using stored procedures and prepared statements. I believe most of my questions got answered in the user comments of the php documentation, but I personally think some of the points I will make in this blog cover features that are implemented in strange and specific ways by mysqli.

Explaination
In this tutorial I will be making a subclass of mysqli that takes care of some of the less obvious issues of mysqli functionality. The main points covered will be:

  • Connecting to a mysql database with php
  • Creating arrays from mysql output
  • Defining the Class, Constructor, and Destructor

class SQLConnection extends mysqli
{

public function __construct($username, $password, $database)
{
parent::__construct(null, $username, $password, $database);
}

public function __destruct()
{
$this->close();
}

}


Info about this code:

  • extends - Extends is the keyword for subclassing in php. If this doesn't mean anything to you, there is more info on it here.
  • Constructor Parameters - This constructor doesn't completely extend the mysqli constructor. If your project requires more functionality for connecting, you can expand the parameters of the constructor. I chose to keep a simple constructor because in most cases the most you need to log into a mysql database is a username, password, and database name. The first parameter of the parent constructor (currently set to null) tells mysqli to connect to the local host, which is what should be used if the mysql server is on the same computer as php.
  • Destructor - The destructor exists to destroy the connection when the object is destroyed. It is more of a good coding practice than neccessity, idealy, the connection will be closed by a call from outside this object, when the connection is no longer being used.

Getting Query Results


public function result_array($result)
{
$data = $result->fetch_all(MYSQLI_ASSOC);
$result->free();
return $data;
}

public function mq_result_array()
{
$this->next_result();
$result = $this->store_result();
return $this->result_array($result);
}


Info about this code:

  • result_array - mysqli returns results from queries in the form of mysqli_result objects. This function takes the data from a result and makes an array.
  • fetch_all(MYSQLI_ASSOC) - This function returns an array filled with associative arrays. The key strings correspond with the column names in the result table. Example: $data[5]['test_column'] Would return the value from row 5 of the column named 'test_column', in the results table stored by $data. Note: fetch_all does not exist before php version 5.3, I've included an alternative for older versions at the end of this tutorial.
  • $result->free() - This frees up system resources used by the result.
  • mq_result_array - mysqli returns results from multi_query's different than other queries. The results must be obtained by a call to next_result(). mq_result_array automatically grabs the next result and returns it as an array.

Closing Extra Results


public function close_results(){
while($this->more_results())
{
$this->next_result();
if($result = $this->use_result())
{
$result->free();
}
}
}


Info about this code:

  • close_results - An important part of mysqli is calling use_result or store_result on all results returned by any queries you make. If another query is made before using all results from the previous query, a session out of sync error occurs. To help avoid this error from occuring, close_results closes out any results left when it is called by scanning through the remaining results. This function is especially useful when calling stored procedures because they return an extra status result, which the mysqli documentation doesn't warn about.
  • while($this->more_results()) - This loop scans until $this->more_results() returns false, indicating that all results have been processed.
  • $this->next_result() - This must be called so that the next call to use_result() will return the next available result.
  • $result = $this->use_result() - This statement stores a reference to an unbuffered result. The result is unbuffered due to the call use_result() instead of the call store_result(). Unbuffered is ideal in this situation because the result is being freed up and not used so there is no reason to buffer it in php.
  • $result->free() - Frees resources associated with the used result.

Trying it out
Since this class is just intended to add some functionality, following are some simple examples that use the added functionality:

Create and Demo a Test Database (Requires MySQL permissions to create database/tables/procedures)
Note: Replace username and password with your own


require_once(dirname(__FILE__) . '/SQLConnection.php');
$connection = new SQLConnection('username','password','');
$connection->multi_query(''.
'CREATE SCHEMA IF NOT EXISTS test;'.
'USE test;'.
'DROP TABLE IF EXISTS TESTVALUES;'.
'CREATE TABLE TESTVALUES(intvalue INT, strvalue VARCHAR(255));'.
'INSERT INTO TESTVALUES(intvalue, strvalue)'.
'VALUES (0, \'zero\'), (1,\'one\'), (2, \'two\');');
$connection->close_results();
$connection->multi_query(''.
'SELECT * FROM TESTVALUES WHERE intvalue > 0;'.
'SELECT * FROM TESTVALUES WHERE CHAR_LENGTH(strvalue) = 4;');
$result = $connection->mq_result_array();
echo 'intvalue > 0: ';
print_r($result);
$result = $connection->mq_result_array();
echo 'charcount = 4: ';
print_r($result);
$connection->close_results();
$connection->close();
?>



Hopefully that code runs on your setup. Drop me a comment if there are any problems.

Troubleshooting from experience:

  • If you are using stored procedures, they must be called with multi_query, and they return an extra result that needs to be closed before you make another query. (When it comes to closing results I try to err on the safe side).
  • On the MySQL side of things, stored procedures with parameters don't use the query cache, so it can sometimes be better to hardcode your select statements in php.
  • Queries should not send unprocessed user input because of the possibility of SQL injection. Use mysql_real_escape_string() or prepared statements (not covered in this tutorial) to avoid this security pitfall.

Older version compatibility:
This class requires PHP 5, the following functions are for versions before 5.3 when mysqli::fetch_all() wasn't available.



public function result_array($result)
{
if(version_compare("5.3", phpversion(),"<=")) {
$data = $result->fetch_all(MYSQLI_ASSOC);
}
else
{
$data = $this->fetch_all($result);
}
$result->free();
return $data;
}

private function fetch_all($result)
{
$all_rows = array();
while ($all_rows[] = $result->fetch_assoc()){}
array_pop($all_rows);
return $all_rows;
}


The source code from this tutorial can be downloaded here.