MySQLi Tutorial: Debugging with MySQLi

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: , ,

MySQLi Tutorial: Prepared Statements

Here I’ll cover the basics of using a prepared statement with MySQLi in PHP. I’ll be using the same table as this tutorial. Let’s begin, shall we?

<?php
/* make the connection */
$sql = new mysqli('localhost','username','password','database');
 
/* we'll look for Mike */
$findname = 'Mike';
 
/* make sure we can prepare the statement */
if ( $prep = $sql->prepare("SELECT * FROM `people` WHERE `name`=?") ) {
    /* first we want to bind $findname to the first ? in our statement
    since it is a string, we'll use 's' */
    $prep->bind_param("s",$findname);
 
    /* execute the statement */
    $prep->execute();
 
    /* bind our results to some useable variables
    you want to bind these in the order of the columns
    in your table, in our case: id, name, age */
    $prep->bind_result($id,$name,$age);
 
    /* now we want to fetch the data from the database */
    $prep->fetch();
 
    /* the order in which you want the variables displayed
    is the order in which they go after the statement */
    printf("row id is: %d, %s is %d years old.",$id, $name, $age);
 
    /* close the statement */
    $prep->close();
 
} else {
    echo "There was a problem preparing your statement";
}
/* close up */
$sql->close();
?>

And there you have it, a very basic, introduction to prepared statements using MySQLi. I hope you found it useful.

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