Tutorials

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

PHP Tutorial: foreach() lock there is a key

When programming in PHP you will often find yourself using arrays. But what if you have a large array, with 50 items? How can you access the data stored in it without having to type each key? You can send the array through a foreach(). What a foreach() does is executes a block of code for each item in an array. There are two ways to use a foreach(), once is to use only the values, and the other is to use both keys and values. I’ll start with the values only way.

<?php
/* our array */
$media = array('Vinyl','8-Track','Cassette','Compact Disc');
 
?>My Favourite media types are:<ol><?php
/* so foreach item in $media, give the $value */
foreach ( $media as $value ) {
    echo "<li>$value</li>\n";
}
?></ol>

This should yield:

My Favourite media types are:

   1. Vinyl
   2. 8-Track
   3. Cassette
   4. Compact Disc

“Ok Mike, got it, but what about my keys?” Right, keys, well using your keys in a foreach() is also a very common method. Here is how it is done:

<?php
/* our array */
$media = array(
1=>'Vinyl',
2=>'8-Track',
3=>'Cassette',
4=>'Compact Disc');
?>My Favourite media types are:<br /><?php
/* so foreach item in $media, give me the 
key as $key and the value as $value */
foreach($media as $key => $value) {
    echo "$key: $value<br />\n";
}
?>

This should yield:

My Favourite media types are:
1: Vinyl
2: 8-Track
3: Cassette
4: Compact Disc

Now, obviously this is a very simple example, but it gives you the basics how to to initiate a foreach() loop. I hope you found this useful.

By Mike on March 29, 2011 | PHP, Tutorials
Tags: , , ,