This looks like a lot of work to just get a single string, but by preparing a query like this, you can optimize the system for speed and access the same information many times. The codes notes from the php5 manual link above show how you would do this:
????//put this function as a method in your
//existing DB class
???? public function prepare ( $query = '' ) {
???? ?? ?? return new PS( $this, $query );
???? }
put this class definition after your DB class in the same document
class PS extends mysqli_stmt {
??public function __construct ( $mysqli, $query ) {
???? ?? ?? parent::__construct( $mysqli, $query );
???? }
}
See? I??m not the only one naming my classes DB, heh… the technique above would be excellent for storing large amounts of single parameter driven queries that need to be iterated through. By the way, I spent some time trying to store queries using just the prepare_query() method, but I couldn??t get the stored objects to work. The reason is because all methods associated with this prepared statement need to be their own instance of the mysql_stmt object.
Lastly, let??s take a look at the multi-query:
$query = "SELECT CURRENT_USER();"; $query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5;";
I personally found this a bit hard to follow, but see if you can understand what??s going on here. First, we execute the query syntax as a multi query:
$db->multi_query($query) or die ($db->error());
Then, start a do block:
do {
Next, we store the result of the query using the mysqli??s store_result method and put a check around it to make sure it executes properly:
if ($result = $db->store_result()) {
if yes, start another iterator here for each row (note: we??re in the first query)
while ($row = $result->fetch_row()) {
now, we print the data out (we know it??s only one piece of info, the username, so just the first element of the row array is needed):
printf("%s
", $row[0]);
} // end while loop
Next we close the result set:
$result->close();
} // end if block
check for another queries data here, if yes, print a break tag:
if ($db->more_results()) {
printf("
");
} //end if
I understand if this code block looks confusing?? but think about what this line is doing here:
} while ($db->next_result());
What??s going on here is for each query that is run, the first inner loop will iterate over all it??s values:
while ($row = $result->fetch_row()) { //print out data here }
then, when the loop is done, whatever result set you are in is closed, and then you run a check to see if there are more results (i.e. the results of another query ) available. If yes, an html break tag is printed out to separate the results, and the process is run all over again.
In Conclusion
In summary, the mysqli class can be very powerful for doing repetitive data tasks and makes it much easier to encapsulate robust methods into your own custom classes. If you haven??t yet taken advantage of it in php5, or done much Object Oriented coding, it may comprise the best of both worlds for you, as it is a quick and easy way to both begin working with OO easily inside php5, as well as make your database calls much simpler.
About the Author
Ben Robinson is an open source coder who enjoys working on dynamic web applications, especially in PHP and mysql, in the L.A.M.P. environment. His website is TierraLogic Systems (http://www.tierralogic.com/) if you??d like to pay him a visit.
