MySQLi, what’s the difference between standard queries and prepared statements?

MySQLi, what’s the difference between standard queries and prepared statements?

You may have heard of prepared statements, but don’t quite understand the differences. In this post I’m going to try and help you with that. Well, with your standard query, you would get your information, escape it for safety, build your query and execute.

<?php
/* instantiate our class, and select our database automatically */
$sql = mysqli('localhost','user','password','database');
 
/*
let's assume we've just received a form submission.
so we'll receive the information, and we'll escape it
*/
$name  = $sql->real_escape_string($_POST['name']);
$age   = $sql->real_escape_string($_POST['age']);
$email = $sql->real_escape_string($_POST['email']);
 
/* build the query, we'll use an insert this time */
$query = "INSERT INTO `tablename` VALUES ('$name','$age','$email');";
 
/* execute the query, nice and simple */
$sql->query($query) or die($query.'<br />'.$sql->error);
?>

So, what is an advantage to doing this? An advantages is you can view the full query prior to running it, simply by echoing it out. How does this help? It lets you make sure that your query is building properly before you start executing it.
What is a disadvantage? Well, you have to escape each string for security purposes, that takes a little more time than some people may like to spend.

And how about prepared statements?

<?php
/* instantiate our class, and select our database automatically */
$sql = mysqli('localhost','user','password','database');
 
/*
let's assume we've just received a form submission.
so we'll receive the information, and we'll escape it
this step is not necessary, but why not.
*/
$name  = $_POST['name'];
$age   = $_POST['age'];
$email = $_POST['email'];
 
/* build the query, we'll use an insert this time */
$query = $sql->prepare("INSERT INTO `tablename` VALUES ('?','?','?');");
 
/*
bind your parameters to your query
in our case, string integer string
*/
$query->bind_param("sis",$name,$age,$email);
/* execute the query, nice and simple */
$query->execute();
?>

The beauty of these prepared statements is there are fewer steps, and you need not escape the parameters because the bind_param() will do that for you.

So prepared statements are best suited for queries that take user input, such as an INSERT or UPDATE query, or a DELETE or SELECT with a WHERE clause.

Hope that helps.

By Mike on April 16, 2011 | MySQLi, PHP
Tags: ,

MySQLi Tutorial: Debugging with MySQLi

There will be times when you run into problems with your MySQL statements or connections, there are ways to help you sort out what went wrong, I’ll cover that in this tutorial.

If you ever get an error like these:
Access denied for user ‘username’@’localhost’ (using password: YES)
Unknown database ‘phpmysqlitutorial’

They can be easily solved by double checking your connection details and verifying your database name using a tool such as phpMyAdmin or if your hosting provides it, the MySQL administration tools in CPanel.

I’ll start with showing you how to check for errors on your connection. You’ll need two functions:

mysqli_connect_errno() - Returns an error code
and
mysqli_connect_error() - Returns a string description of the last error
                         produced by your connection
<?php
/* make our connection */
$sql = new mysqli('localhost','username','password','database');
 
/* check for an error code */
if ( mysqli_connect_errno() ) {
    /* oh no! there was an error code, what's the problem?! */
    echo 'There was an error with your connection: '.mysqli_connect_error();
}
 
/* close up */
$sql->close();
?>

If for some reason your queries don’t seem to be inserting, updating, deleting or selecting, you can find out how by using MySQLi’s error properties. You will need:

->errno - Returns the error code of the last error.
and
->error - Returns a string description of that error.
<?php
/* make our connection */
$sql = new mysqli('localhost','username','password','database');
 
/* make a purposely erroneous query, notice there is a missing ' at the end */
$query = "SELECT * FROM `people` WHERE `name`='Mike;";
 
/* try and run this query */
if ( !$sql->query($query) ) {
    echo "Error code ({$sql->errno}): {$sql->error}";
} else {
    echo 'Query was sucessful, proceed.';
}
 
/* alternatively you could shorten that to: */
$result = $sql->query($query) or exit("Error code ({$sql->errno}): {$sql->error}");
 
/* close up */
$sql->close();
?>

And there you have it, two ways to check and make sure your MySQL details and queries run smoothly. So just remember, if you find yourself pulling your hair out because your queries aren’t working, or you connection is failing, print out some errors that can really help you figure out what you might have missed.

By Mike on March 31, 2011 | MySQLi, Tutorials
Tags: , ,

MySQLi Tutorial: INSERT id, no id — no entry.

Often times when you are working with a database you tend to insert more than one row. This could be because you are signing up a user, adding stats, creating a post in a forum software, or all of the above. There are many times when you want to use the new data row directly after it has been inserted, to do this you usually want the `id` field, or something similar (`user_id`, or other auto-incrementing key). MySQLi makes this very easy using the insert_id property. To get this value you would perform a standard insert, and then call the insert_id via your current MySQLi instance. This example uses the same `people` table as the previous tutorials here. Like so:

<?php
/* make your connection */
$sql = new mysqli('localhost','username','password','localhost');
 
/* we will just create an insert query here, and use it,
normally this would be done by form submission or other means */
$query = "INSERT INTO `people` (`id`,`name`,`age`) VALUES (NULL,'Mitch','25');";
 
if ( $sql->query($query) ) {
    echo "A new entry has been added with the `id` of {$sql->insert_id}.";
} else {
    echo "There was a problem:<br />$query<br />{$sql->error}";
}
 
/* close our connection */
$sql->close();
?>

If all goes well you should see: A new entry has been added with the `id` of 5.

Otherwise you may see your query and a detailed error message from MySQL saying what went wrong, you can then use this to solve the issue.

By Mike on March 30, 2011 | MySQLi, Tutorials
Tags: , ,