PHP and MySQLi Extension
In the open source community the most popular data base driven web application is comprised of the webserver Apache, the backend database MySQL and the scripting language PHP, which along with Linux, the operating system, have come to be known by the acronym LAMP. When a request is made to Apache for a particular webpage, it is the PHP engine that interprets the script, connects to the MySQL server, performs database queries, construct the HTML output and returns them to Apache.
PHP database driven web application basically consists of the following steps:
1. Open a connection to the server and the database.
2. Perform a query, which may be in response to a user prompted input.
3. Process the query results and present them to the user.
4. Close the connection.
To make use of new functionality in MySQL 5 (actually, in MySQL 4.1.2 and above) and to provide greater security and speed, PHP provides a new and improved methods (procedural as well as object oriented programming) for connecting to MySQL. This is known as the mysqli extension. To make this extension available, however, you must compile PHP by specifying the complete path to the mysql_config file.
There are basically three pre-defined MySQL classes:
1. mysqli, representing the PHP connection to MySQL
This contains methods for connecting to the server and sending queries
2. mysqli_result, representing the result set from a MySQL database query
This contains methods for working with the result set, such as fetching rows and columns
3. mysqli_stmt, representing a prepared statement
This contains methods for constructing and executing a prepared statement
Though class methods are, of course, for object oriented programming, similarly named functions are also available for use in procedural programming. Just note for now that in object oriented programming, you don't need to specify a connection handle or a result handle. The connection and result set information are stored in $mysqli and mysqli_$result objects. Shortly, we will present examples of both object oriented and procedural programing styles.
Connecting to MySQL
For object oriented style, a mysql connection resource is created with new and closed with the method close(). For example, the following code snippet will attempt to connect user joel to the Jolux database or terminate the script by calling the die() function if there is an error connecting, i.e., mysqli_connect_errno() greater than zero. (Note that there no object oriented style equivalent to mysqli_connect_errno()).
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<font color='red'>Eeeek! Cannot connect user $user, " .
" password $password </font>");
}
echo "Connected successfully";
$connection->close();
?>
mysqli_connect (hostname, username, password, database)
mysqli_close (resource)
mysqli_connect() returns a connection resource on success or false otherwise. mysqli_close() closes a connection and returns true on success.
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = @mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeek! Cannot connect user $user, " .
" password $password </font>");
echo "Connected successfully";
mysqli_close($connection);
?>
Performing Queries
The object method query(), or its procedural style equivalent function mysqli_query(), is used to send a query statement to the currently active database. It has the following prototype:
query( string)
mysqli_query (connection, string)
mysqli_query() returns false on error or it returns a resource handle. The query string must NOT end in a semicolon. For example, the following script connects to mysql and creates a table.
Object Oriented Style.
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'>Eeeeeeeek. Errrrrror!</font>");
}
$sql = "CREATE TABLE my_table (name varchar(32), price decimal(7,2))";
if ($connection->query($sql)) {
echo ("Table successfully created.");
} else {
echo ("Eeeeek! Cannot create table");
}
$connection->close();
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = @mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font>");
$sql = "CREATE TABLE my_table (name varchar(32), price decimal(7,2))";
$result = mysqli_query ($connection, $sql)
or die("Eeeeek! Cannot create table");
echo "Table successfully created";
mysqli_close($connection);
?>
mysqli_affected_rows(connection)
Here's a script that inserts a row into the table.
Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'>Eeeeeeeek. Errrrrror!!</font>");
}
$sql = "INSERT INTO my_table (name, price) values ('Syoktong', 155.25)";
if ($connection->query($sql)) {
echo "Rows inserted: ", $connection->affected_rows;
} else {
echo "Eeeeek! Problem executing query";
}
$connection->close();
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = @mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font>");
$sql = "INSERT INTO my_table (name, price) values ('Syoktong', 155.25)";
mysqli_query ($connection, $sql) or die("Problem executing query");
echo "Rows inserted: ", mysqli_affected_rows($connection);
mysqli_close($connection);
?>
Example: Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'>Eeeeeeeek. Errrrrror!</font>");
}
$sql = "SELECT name, phone FROM customers";
$result = $connection->query($sql)
or die("Eeeeek! Cannot execute query");
$rows = $result->num_rows;
$fields = $result->field_count;
echo "Number of rows in result set: $rows";
echo "<br>Number of fields in result set: $fields";
$connection->close();
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = @mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font>");
$sql = "SELECT name, phone FROM customers";
$result = mysqli_query ($connection, $sql)
or die("Eeeeek! Cannot execute query");
$rows = mysqli_num_rows($result);
$fields = mysqli_num_fields($result);
echo "Number of rows in result set: $rows";
echo "<br>Number of fields in result set: $fields";
mysqli_close($connection);
?>
There are a number of functions that can be used to manipulate the result set in addition to the mysqli_num_rows(), mysqli_num_fields() and mysqli_affected_rows() we saw in the previous section.
Method fetch_array() or function mysqli_fetch_array(result, option) — Perhaps the most important function for manipulating the result set, mysqli_fetch_array() returns an associative array corresponding to a row that is fetched, or it returns boolean false if there are no more rows or when there's an error. The optional second argument is a constant which can be any of the following: MYSQLI_NUM (meaning, the array be indexed numerically starting with zero), MYSQLI_ASSOC (as an associative array using the field names as keys), or MYSQLI_BOTH (the default, meaning both).
Example: Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'>Eeeeeeeek. Errrrrror!</font>");
}
$result = $connection->query("SELECT name, phone FROM customers")
or die("Eeeek! Problem executing query");
print "<table border=1>\n";
print "<tr><td><b>NAME</b></td><td><b>PHONE</b></td></tr>";
while ($row = $result->fetch_array()) {
echo "<td>", $row['name'], "</td><td>", $row['phone'], "</td></tr>\n";
}
echo "</table>\n";
$connection->close();
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = @mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font>");
$result = mysqli_query ($connection, "SELECT name, phone FROM customers") or die();
print "<table border=1>\n";
print "<tr><td><b>NAME</b></td><td><b>PHONE</b></td></tr>";
while ($row = mysqli_fetch_array($result)) {
echo "<td>", $row['name'], "</td><td>", $row['phone'], "</td></tr>\n";
}
echo "</table>\n";
mysqli_close($connection);
?>
The method fetch_assoc() or the procedural function mysqli_fetch_assoc(result) and the method fetch_row() or the function mysqli_fetch_row(result) — These are similar to mysqli_fetch_array(result, MYSQLI_ASSOC) and mysqli_fetch_array(result, MYSQLI_NUM), respectively. For example, here's a code snippet that uses mysqli_fetch_assoc() to print the customer name and phone number.
PHP Code:
while ($row = $result->fetch_assoc()) {
echo "<td>", $row['name'], "</td><td>", $row['phone'], "</td></tr>\n";
}
PHP Code:
while ($row = mysqli_fetch_assoc($result)) {
echo "<td>", $row['name'], "</td><td>", $row['phone'], "</td></tr>\n";
}
PHP Code:
while ($row = $result->fetch_row()) {
echo "<td>", $row[0], "</td><td>", $row[1], "</td></tr>\n";
}
PHP Code:
while ($row = mysqli_fetch_row($result)) {
echo "<td>", $row[0], "</td><td>", $row[1], "</td></tr>\n";
}
The method fetch_object() or the procedural function mysqli_fetch_object(result) — Returns an object corresponding to the fetched row, or boolean false if there are no more rows or there's an error. The following uses mysqli_fetch_object() to also print the customer name and phone number.
PHP Code:
<?php;
while ($row = $result->fetch_object()) {
echo "<td>", $row->name, "</td><td>", $row->phone, "</td></tr>\n";
}
?>
PHP Code:
<?php;
while ($row = mysqli_fetch_object($result)) {
echo "<td>", $row->name, "</td><td>", $row->phone, "</td></tr>\n";
}
?>
mysqli_fetch_field_direct(field_number), mysqli_fetch_field() and mysqli_fetch_fields() — Returns various kinds of information about a column. fetch_field_direct() accepts the field number offset (zero-based) as argument and returns information for that specified column. fetch_field() and fetch_fields() are identical, the single difference is that the singular fetch_field() returns a single column whereas the plural fetch_fields() returns an array of columns.
Example: Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'>Eeeeeeeek. Errrrrror!</font>");
}
$sql = "SELECT name, phone FROM customers";
$result = $connection->query ($connection, $sql)
OR die("Eeeek! Query failed.");
$f = $result->fetch_field_direct(1);
echo "Column Name: $f->name<br>";
echo "Table Name: $f->table<br>";
echo "Data Type: $f->type<br>";
echo "Max Length: $f->max_length<br>";
$connection-> close;
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = @mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font>");
$sql = "SELECT name, phone FROM customers";
$result = mysqli_query ($connection, $sql) or die("Eeeek! Query failed.");
$f = mysqli_fetch_field_direct($result,1);
echo "Column Name: $f->name<br>";
echo "Table Name: $f->table<br>";
echo "Data Type: $f->type<br>";
echo "Max Length: $f->max_length<br>";
mysqli_close($connection);
?>
Example: Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'>Eeeeeeeek. Errrrrror!</font>");
}
$sql = "SELECT name, income FROM customers";
$result = $connection->query ($sql)
OR die("Eeeek! Query failed.");
$fields = $result->fetch_fields();
foreach($fields as $f) {
echo "-------------------------<br>";
echo "Column Name: $f->name<br>";
echo "Table Name: $f->table<br>";
echo "Data Type: $f->type<br>";
echo "Max Length: $f->max_length<br>";
}
$connection-> close;
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font");
$sql = "SELECT name, income FROM customers";
$result = mysqli_query ($connection, $sql)
OR die("Eeeek! Query failed.");
$fields = mysqli_fetch_fields($result);
foreach($fields as $f) {
echo "-------------------------<br>";
echo "Column Name: $f->name<br>";
echo "Table Name: $f->table<br>";
echo "Data Type: $f->type<br>";
echo "Max Length: $f->max_length<br>";
}
mysqli_close($connection);
?>
Multiple Queries
The mysqli_multi_query() function executes one or more queries which are concatenated by a semicolon. To retrieve the result set from multi_query() use mysqli_store_result() and mysqli_next_result() for all subsequent result set retrieval. To check if there are more rows in the result set, use mysqli_more_results(). Use mysqli_free_result() to free up resources used by mysqli_store_result(), especially if you're concerned with the memory occupied by a large result set.
For queries that do not return a result set (for example, an INSERT), mysqli_store_result() will return FALSE, in which case, check that the function mysqli_errno() (or the object property errno) is zero, meaning that everything's okay. For example, the following script inserts multiple records into a table. An error number of zero indicates success.
Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<p><font color='red'><font</font>");
}
$sql = "INSERT INTO my_table values ('Bahal', 135.90);"
. "INSERT INTO my_table values ('Tanduay', 350.75);"
. "INSERT INTO my_table values ('Kulafu', 295.85)";
$connection->multi_query($sql);
if ($connection->errno > 0) {
die("Eeeeek! Problem executing multi query");
}
$connection->close();
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = mysqli_connect($server, $user, $password, $database )
or die("<font color='red'><font");
$sql = "INSERT INTO my_table values ('Bahal', 135.90);"
. "INSERT INTO my_table values ('Tanduay', 350.75);"
. "INSERT INTO my_table values ('Kulafu', 295.85)";
mysqli_multi_query($connection, $sql);
if (mysqli_errno($connection) > 0) {
die("Eeeeek! Problem executing multi query");
}
mysqli_close($connection);
?>
Object Oriented Style
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<font color='red'>Eeeeeeeek. Errrrrror!</font");
}
$sql = "SELECT name, phone FROM customers;"
. "SELECT name, price FROM my_table";
if ($connection->multi_query($sql)) {
do {
if ($result = $connection->store_result()) { // get a result set
while ($row = $result->fetch_row()) {
echo $row[0], " => ", $row[1], "<BR>";
}
$result->free(); // free result resource
}
if ($connection->more_results()) {
echo "---------------------------------<BR>";
}
} while ($connection->next_result());
}
$connection->close();
?>
PHP Code:
<?php
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = mysqli_connect($server, $user, $password, $database )
or die("<font color='red'>Eeeeeeeek. Errrrrror!</font");
$sql = "SELECT name, phone FROM customers;"
. "SELECT name, price FROM my_table";
if (mysqli_multi_query($connection, $sql)) {
do {
if ($result = mysqli_store_result($connection)) { // get result set
while ($row = mysqli_fetch_row($result)) {
echo $row[0], " => ", $row[1], "<BR>";
}
mysqli_free_result($result); // free result resource
}
if (mysqli_more_results($connection)) {
echo "---------------------------------<BR>";
}
} while (mysqli_next_result($connection));
}
mysqli_close($connection);
?>
Code:
Magno Alda => 233-1207 Aning Bulhog => 564-1367 Mina Tai => 345-1908 --------------------------------- Syoktong => 155.25 Bahal => 135.90 Tanduay => 350.75 Kulafu => 295.85
Prepared Statements
PHP supports two types of prepared statements: bound parameters and bound results.
In a bound parameter prepared statement, a query template is constructed with parameter markers indicated by a question mark (?) using mysqli_stmt_prepare() which returns TRUE on success or FALSE on failure. This template is sent to the MySQL server once. On requests to execute the query, the parameter markers are filled with data and delivered to the MySQL server where a complete query is formed and then executed.
You can use markers in the VALUES() list of an INSERT statement or in a comparison with a column in a WHERE clause to specify a comparison value, but you can't use them for identifiers such as column names or for operators such as equals (=). For example, the following are valid statements.
PHP Code:
$stmt->prepare("INSERT INTO my_table (name, price) VALUES (?,?)");
$stmt->prepare("SELECT name FROM my_table WHERE price < ?");
i to indicate an integer
d to indicate a double or a float
b to indicate a blob
s to indicate all other data types
For example, the following specifies $name as data type string and $price as data type double.
PHP Code:
$stmt->bind_param('sd', $name, $price);
1. Initialize a statement handle using mysqli_stmt_init()
2. Set up the prepared statement using mysqli_stmt_prepare()
3. Associate or bind the parameter markers to the variables using mysqli_stmt_bind_param()
4. Execute the prepared statement using mysqli_stmt_execute(). All parameter markers will be replaced with the appropiate data.
5. Deallocate the statement handle using mysqli_stmt_close()
Here's a function in object oriented style that uses a bound prepared statement. The mysqli_stmt_affected_rows() is used to determine how many rows have been affected (i.e., inserted).
PHP Code:
<?php
function insert_my_table($connection, $name, $price) {
$sql = "INSERT INTO my_table (name, price) VALUES (?, ?)";
$stmt = $connection->stmt_init(); // initialize statement handle
if ($stmt->prepare($sql)) { // set up prepared statement
$stmt->bind_param('sd', $name, $price); // bind variables to markers
if ($stmt->execute()) // execute prepared statement
echo "Inserted rows: ", $stmt->affected_rows, "<br>";
}
$stmt->close(); // deallocate statement handle
}
?>
PHP Code:
<?php
function insert_my_table($connection, $name, $price) {
$sql = "INSERT INTO my_table (name, price) VALUES (?, ?)";
$stmt = mysqli_stmt_init($connection); // initialize statement handle
if (mysqli_stmt_prepare($stmt, $sql)) { // set up prepared statement
mysqli_stmt_bind_param($stmt, 'sd', $name, $price); // bind variables to markers
if (mysqli_stmt_execute($stmt)) // execute prepared statement
echo "Inserted rows: ", mysqli_stmt_affected_rows($stmt), "<br>";
}
mysqli_stmt_close($stmt); // deallocate statement handle
}
?>
1. associate a variable to a column in the result set using mysqli_stmt_bind_result(), and
2. place the data for the bound column into the variable using mysqli_stmt_fetch()
To illustrate, here's a function written in object oriented style that uses a prepared query statement (in this case, a bound parameter statement) and then binds the values of the columns of the query result set to the specified variables.
PHP Code:
<?php
function price_above($connection, $price) {
$sql = "SELECT name, price FROM my_table WHERE price > ?";
$stmt = $connection->stmt_init(); // initialize prepared statement
if ($stmt->prepare($sql)) { // set up prepared statement
$stmt->bind_param('d', $price); // bind variable to marker
$stmt->execute(); // execute the statement
$stmt->bind_result($name, $price); // bind columns to variables
while ($stmt->fetch()) // fetch result into variables
echo "$name is $price<br>";
}
$stmt->close(); // deallocate statement handle
}
?>
In our case, we've combined the two functions in one file, testfunc.php, and included it in a "require" statement in our main script, tester.php, that calls the functions. Here's what tester.php contains.
PHP Code:
<?php
// File name: tester.php
require "testfunc.php";
$server = 'localhost';
$user = 'joel';
$password = 'Eeeeeek';
$database = 'Jolux';
$connection = new mysqli($server, $user, $password, $database );
if (mysqli_connect_errno()) {
die("<font color='red'>Eeeeeeeek. Errrrrror!</font");
}
insert_my_table($connection, 'Bahalina', 345.85);
price_above($connection, 300.00);
$connection->close();
?>
Code:
Inserted rows: 1 Tanduay is 350.75 Bahalina is 345.85