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

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

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