MySQLi

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

MySQLi Tutorial: Selecting Multiple Entries

Hey again, I’ll be showing you how to handle multiple rows from a query, using MySQLi, for example:

SELECT * FROM `people`;

The table I’ll be using is the same table from this tutorial. Since you probably already have that table ready, let’s get started!

<?php
/* make our connection, remember the order:
host, username, password, database */
$sql = new mysqli('localhost','username','password','database');
 
/* let's write our query, we'll select everyone, showing the oldest first. */
$query = "SELECT * FROM `people` ORDER BY `age` DESC;";
 
/* set our result information */
$result = $sql->query($query);
 
/* now that we have our result information, we can cycle through the
database rows much like we used to in the procedural days */
echo 'Display using objects.<br />';
while ( $row = $result->fetch_object() ) {
    echo "{$row->name} is {$row->age} years old.<br />";
}
 
/* if you prefer to use associative arrays, you can do the following */
echo 'Display using an associative array.<br />';
while ( $row = $result->fetch_assoc() ) {
    echo "{$row['name']} is {$row['age']} years old.<br />";
}
 
/* ok we're done here, close our connection */
$sql->close();
?>

Running the above code should yield the following results:

Display using objects.
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.
Display using an associative array.
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.

You may wish to check that there are actually results before trying to display them, this is often a good idea. You can do this by check the number of rows returned by your query, you can do this with the following:

<?php
/* so again, make your connection */
$sql = new mysqli('localhost','username','password','database');
 
/* write our query */
$query = "SELECT * FROM `people` ORDER BY `age` DESC;";
 
/* set our result information */
$result = $sql->query($query);
 
if ( $result->num_rows > 0 ) {
    echo "We found {$result->num_rows} people:<br />";
    while ( $row = $result->fetch_object() ) {
        echo "{$row->name} is {$row->age} years old.<br />";
    }
} else {
    echo 'There are no results to display.';
}
 
/* and close up */
$sql->close();
?>

In our case, you should get:

We found 4 people:
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.

And there you have it, a quick and painless way to display information from a query using the MySQLi class. Hope you found it useful.

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

MySQLi Tutorial: Connect, Select, Display, Close.

Here is the table I’ll be using in this example, copy and paste it into your MySQL admin tool, commonly phpMyAdmin:

CREATE TABLE IF NOT EXISTS `people` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `people` (`id`, `name`, `age`) VALUES(1, 'Mike', 26);
INSERT INTO `people` (`id`, `name`, `age`) VALUES(2, 'Joe', 19);
INSERT INTO `people` (`id`, `name`, `age`) VALUES(3, 'Stephanie', 18);
INSERT INTO `people` (`id`, `name`, `age`) VALUES(4, 'Angela', 21);

Ok, lets dive right in, first we’ll need to connect to our database, using mysqli:

<?php
/* so we start by intializing the mysqli class
we will pass our credentials into the class initilization 
the order: host, username, password, database*/
$sql = new mysqli('localhost','username','password','database');
 
/* ok so we've connected, let's write our query, 
we'll set a variable called name */
$name = 'Mike';
$query = $sql->query("SELECT `age` FROM `people` WHERE `name` = '$name'");
$result = $query->fetch_object();
echo "$name is {$result->age} years old.";
 
/* ok we're done, let's close our connection */
$sql->close();
?>

And there we have it, a very simple connect, and display using the mysqli extension in PHP5. If you are coming from the mysql_* procedural functions, MySQLi can take some getting used to, fiddling with simple selects like this should help you get familiar with the syntax of using the class. If you are used to using classes (of your own or pre-made) this should come easily to you.

I hope you found this useful, thanks for reading!

By Mike on March 23, 2011 | MySQLi, PHP, Tutorials
Tags: , , ,