Accessing MySQL with PHP
MySQL is a popular database choice for PHP applications. Therefore, we use this page to focus on PHP-based MySQL functions that access a MySQL database.
We begin with a short introduction of PHP. Following that, we provide description and examples of various PHP-based MySQL functions. We categorize these examples based on SQL queries: (1) queries that view (existing) MySQL information/data (SHOW, DESCRIBE, and SELECT), (2) queries that create/drop/alter MySQL tables (CREATE, DROP, and ALTER ), and (3) queries that update table data (INSERT, DELETE, UPDATE, and REPLACE). We conclude this chapter by providing a list of newly added optimized MySQL-based PHP functions.
For the sake of consistency, we continue to use our earlier example of a fictitious "Blue Sky Toys" company. As described earlier, the company's database manages a chain of stores distributed across various locations. The database maintains records of employees, locations, items (mainly toys), and their inventory. It handles this record-keeping by taking help from four tables: employees (columns: employee_id, employee_name, date_of_birth, store_id, and employee_address), stores (columns: store_id and store_location), items (columns: item_code, item_name, item_type, and item_price), and inventory (columns: store_id, item_code, and item_quantity).
Introduction to PHP
Let us first begin with a brief introduction of PHP; for a comprehensive introduction of PHP, we refer readers to our PHP module.
PHP is used (mostly) as a part of HTML and is embedded between the tags "<?php" and "?>". Like any other modern programming language, PHP relies upon several programming constructs like variables, statements, functions, conditional-expressions, and loops. Before moving further, let us use the next few paragraphs to summarize these constructs. Also, we will see usage of these constructs in examples throughput this chapter.
PHP variables are fundamental elements of the PHP language (and for that matter, of any programming language!). Variables in PHP start with "$" sign and can store values that are used by a PHP program. As an example, "$var1 = 1000;" defines a variable ($var1) and assigns a value of 1000 to it.
Simply put, a PHP statement is a single line of code, representing a single unit of execution. This execution can be an expression followed by an assignment of that value (from expression) to a variable, or it can be a declaration of a new variable etc. Statements ends with a semi-colon.
PHP provides conditional expressions for executing different tasks based upon the value of a conditional variable or an expression. It offers four types of conditional expressions: (a) if, (b) if-else, (c) if-elseif-else, and (d) switch. PHP provides four looping constructs: (1) while loop (2) do-while loop, (3) for loop, (4) foreach loop.
Now that we are done with a rather rapid introduction of PHP, let us write a simple PHP program.
The program (provided below) stores two numbers in two variables, $var1 and $var2 and then adds them. It uses a trivial function (add_two_numbers()) for doing the addition. This function takes two numbers and returns the sum of these two numbers. The program passes the two variables to this function, the function returns the sum of these numbers, and then the program assigns the returned value to a new variable, $sum_of_two variable.
The program also uses PHP's echo to print the output; this output would get printed on the browser, when we access this program using a browser. Lastly, note that the above program uses an HTML tag ("<br>") inside PHP code to provide a line break.
<!doctype html>
<html>
<head> Program to add two numbers <br><br></head>
<body>
<?php
function add_two_numbers($num1, $num2) {
$sum = $num1 + $num2;
return ($sum);
}
$var1 = 1000;
$var2 = 100;
$sum_of_two = add_two_numbers($var1, $var2);
echo "The first number is $var1 <br>";
echo "The second number is $var2 <br>";
echo "The sum of these numbers is $sum_of_two <br>";
?>
</body>
</html>
If we were to save this HTML page as file "addition.php" and access it from browser (let us say, "http://localhost/addition.php"), then we would see the output provided below. Note that for this program to run, the webserver must be running and the webserver must be integrated with PHP module; once again, we recommend our PHP module for more details on getting started with PHP.
Figure: A Sample PHP Example
PHP Functions to access MySQL: Getting Started
The very first step to access a MySQL database is to establish a connection to the database server. PHP provides mysql_connect() function to do this task; this function takes three arguments: address of the host which runs the database server, username, and password for the database.
We provide below a simple PHP example that connects to a local database sever.
This example uses mysql_connect() function to establish the connection; the arguments to this function are user credentials and the name of the host running the database server. We pass "user1001"/ "password1001" as username/password and "localhost" as the host that runs the database server; localhost means that the database server is running on the local machine. If the connect call (mysql_connect()) fails, then the example uses mysql_errno() and mysql_error() functions to prints the error number and the error string. Once we are all done with the operations, the program closes the connection to database server.
<?php
$db = mysql_connect("localhost", "user1001","password1001");
if (!$db) {
$errno = mysql_errno();
$error = mysql_error();
echo "mysql_connect() failed with error: $error ($errno)";
} else {
echo "mysql_connect() succeeded";
}
mysql_close();
?>
To run the above program, let us store this program in a file called "mysqldb_connect.php" and then access it using a browser. For this to work, the location location should be accessible to the web server (in Fedora/Red Hat Linux, the default location for web server is "/var/www/html/"). The next step is to ensure that the web server itself is running. If we were to load this file from a browser (let us say, "http://localhost/mysqldb_connect.php"), then the browser would display the following output.
Figure: Basic MySQL Connect
However, if an error occurs, then we should check if the MySQL database is running or not. On Linux (Fedora/Red Hat), the command "service mysqld status" should tell us if the mysqld is running or not. If mysqld is not running, then we need to start the service using "service mysqld start". Most likely, we need to have root access for running these service commands.
Once we are connected to the database server, we need to select a database on the server (the local database server can have a large number of databases). For this, we first need to find names of all databases sitting on the database server; PHP's mysql_list_dbs() function does exactly this. Upon success, this function returns a resource id and we can use mysql_fetch_object() function to retrieve names of available databases. And upon failure, mysql_list_dbs() returns FALSE.
Once we have a list of available databases, we can use mysql_select_db() to select a particular database. This function returns TRUE when selection succeeds and FALSE when selection fails. Needless to say, the selection will fail if we pass a database name that does not exist in the database server.
As a database server can contain several databases, a database itself can contain several tables. We can easily list all the tables present in the database using a simple "SHOW TABLES" SQL query. With PHP, we can use mysql_query() function to run an SQL query and mysql_fetch_row() to retrieve rows of a query result.
Lastly, we put these concepts together and write a simple PHP program that connects to a database server, shows available databases, connects to the "blue_sky_toys" database, and lists all the tables available in this database. For reasons of brevity, the example uses a small function (show_error()) to prints error number and error name when we run into a failure.
<?php
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "bluesky_root","bst_password_root")) {
show_error("mysql_connect");
return;
}
/* List available databases */
$dbs = mysql_list_dbs();
if (!$dbs) {
show_error("mysql_list_dbs");
return;
} else {
echo "Available databases are: <br>";
while ($row = mysql_fetch_object($dbs)) {
echo $row->Database;
echo "<br>";
}
echo "<br>";
}
/* Connect to the "blue_sky_toys" database */
if (!mysql_select_db("blue_sky_toys")) {
show_error("mysql_select_db");
return;
}
/* Next, print all the tables in the "blue_sky_toys" database */
$query = "SHOW TABLES FROM blue_sky_toys";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query");
return;
} else {
echo "List of all tables available with database blue_sky_toys: <br>";
while ($row = mysql_fetch_row($result)) {
echo $row[0], "<br>";
}
}
/* Once done, end the connection */
mysql_close();
?>
If we were to load this file on a browser (let us say, "http://localhost/mysqldb.php"), then the browser would display the output provided below. For better readability, we provide the text output of the browser instead of providing a snapshot of the browser; the remaining examples in this chapter follow the same suite. OutpuBegin: Available databases are: information_schema blue_sky_toys test
List of all tables available with database blue_sky_toys: employees inventory items storesQueries to View Information/Data
At this point, we are all set to execute SQL queries! As a first step, we begin by looking at SQL queries that view existing MySQL information/data: SHOW, DESCRIBE, and SELECT queries.
As we saw earlier, we can use mysql_query() to run an SQL query; upon success, this query returns a resource id. We can use this resource id to get additional information about the query output. First, we can call mysql_num_rows() to get the number of rows present in the query. Second, we can call mysql_fetch_row() to get the current row from the output; for retrieving all the rows from the queries, we need to run mysql_fetch_row() in a loop.
We provide a simple example that runs a SELECT query on the "employees" table in the "blue_sky_toys" database. We have already seen the usage of SHOW query in the earlier section. We would see the usage of DESCRIBE query in the next section.
<?php
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "bluesky_root","bst_password_root")) {
show_error("mysql_connect");
return;
}
/* Connect to the "blue_sky_toys" database */
if (!mysql_select_db("blue_sky_toys")) {
show_error("mysql_select_db");
return;
}
/* See all the records from the "employees" table */
$query = "SELECT * FROM employees";
$result = mysql_query($query);
if ($result) {
$num_rows = mysql_num_rows($result);
echo "<br>Table Values (total_rows: $num_rows): <br>";
while ($row = mysql_fetch_row($result)) {
echo "ID: ", $row[0], "\tName: ", $row[1], "\tBirthDate: ", $row[2];
echo "\tStore ID: ", $row[3], "\tAddress: ", $row[4], "<br>";
}
} else {
show_error("mysql_query");
return;
}
/* Once done, end the connection */
mysql_close();
?>
If we were to load this page using a browser (for example, "http://localhost/mysqldb_view_info.php"), then the output would show records of all employees working with the Blue Sky Toys company.
Table Values (total_rows: 5): ID: 101 Name: Homer Simpson BirthDate: 1955-07-23 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 103 Name: Peter Griffin BirthDate: 1967-06-28 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 121 Name: Lois Griffin BirthDate: 1968-01-18 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 120 Name: Marge Simpson BirthDate: 1965-05-21 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 112 Name: Ned Flanders BirthDate: 1960-03-07 Store ID: 2 Address: 740 Evergreen Terrace, Springfield
Queries to Create/Drop/Alter Tables
SQL provides queries that create new tables, drop existing tables, or modify the structure of existing tables: CREATE, DROP, and ALTER queries. We can continue to use mysql_query() to run these types of queries as well.
We present an example that demonstrates these queries. The program drops an existing table ("stores") and then recreates it. The program also uses DESCRIBE query to print columns of this table.
<?php
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "bluesky_root","bst_password_root")) {
show_error("mysql_connect");
return;
}
/* Connect to the "blue_sky_toys" database */
if (!mysql_select_db("blue_sky_toys")) {
show_error("mysql_select_db");
return;
}
/* First, let us drop the "stores" table */
$query = "DROP TABLE stores";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (drop table)");
return;
}
/* Next, let us (re)create the "stores" table */
$query = "CREATE TABLE IF NOT EXISTS stores (store_id SMALLINT PRIMARY KEY, store_location CHAR (128))";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (create table)");
return;
}
/* Let us describe this table */
$query = "DESCRIBE stores";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (describe)");
return;
} else {
$num_rows = mysql_num_rows($result);
echo "<br>Table Values (total_rows: $num_rows): <br>";
while ($row = mysql_fetch_row($result)) {
echo "Field: ", $row[0], " Type: ", $row[1], "<br>";
}
}
/* Once done, end the connection */
mysql_close();
?>
Upon loading this page using a browser (for example, "http://localhost/mysqldb_drop_create.php"), we would see the following output.
Table Values (total_rows: 2): Field: store_id Type: smallint(6) Field: store_location Type: char(128)
Queries to Update Data
Lastly, let us look at queries that update table data. These queries can update table data by adding a new record (INSERT), by modifying an existing record (UPDATE/REPLACE), or by deleting an existing record (DELETE). A REPLACE query takes an existing record and replaces it with a new record.
We present a small example that illustrates the usage of these queries and also provides an insight into the format of these queries. For the sake of simplicity, we do not provide an example of the REPLACE query.
<?php
$user_data[] = array(112, "Ned Flanders", "1960-03-07", 1, "740 Evergreen Terrace, Springfield");
$user_data[] = array(121, "Lois Griffin", "1968-01-18", 2, "31 Spooner Street, Quahog");
function show_table ($str) {
echo "<br> Printing table employees $str <br>";
$query = "SELECT * FROM employees";
$result = mysql_query($query);
if ($result) {
$num_rows = mysql_num_rows($result);
echo "Table Values (total_rows: $num_rows): <br>";
while ($row = mysql_fetch_row($result)) {
echo "ID: ", $row[0], "\tName: ", $row[1], "\tBirthDate: ", $row[2];
echo "\tStore ID: ", $row[3], "\tAddress: ", $row[4], "<br>";
}
}
}
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "bluesky_root","bst_password_root")) {
show_error("mysql_connect");
return;
}
/* Connect to the "blue_sky_toys" database */
if (!mysql_select_db("blue_sky_toys")) {
show_error("mysql_select_db");
return;
}
/* Print table */
show_table("");
/* Add some records to the "employees" table */
foreach ($user_data as $element) {
$query = "INSERT INTO employees values('$element[0]','$element[1]', '$element[2]', \
'$element[3]', '$element[4]')";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (insert)");
}
}
/* Print table */
show_table("after INSERT queries");
/* Delete a record */
$query = "DELETE FROM employees where employee_id=110";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (delete)");
}
/* Print table */
show_table("after DELETE queries");
/* Update a record */
$query = "UPDATE employees set store_id=2 where employee_id=112";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (update)");
}
/* Print table */
show_table("after UPDATE Queries");
/* Once done, end the connection */
mysql_close();
?>
If we were to load this page using a browser (for example, "http://localhost/mysqldb_update.php"), we would see the following output.
Printing table employees Table Values (total_rows: 3): ID: 101 Name: Homer Simpson BirthDate: 1955-07-23 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 103 Name: Peter Griffin BirthDate: 1967-06-28 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 120 Name: Marge Simpson BirthDate: 1965-05-21 Store ID: 1 Address: 742 Evergreen Terrace, Springfield Printing table employees after INSERT queries Table Values (total_rows: 5): ID: 101 Name: Homer Simpson BirthDate: 1955-07-23 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 103 Name: Peter Griffin BirthDate: 1967-06-28 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 121 Name: Lois Griffin BirthDate: 1968-01-18 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 120 Name: Marge Simpson BirthDate: 1965-05-21 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 112 Name: Ned Flanders BirthDate: 1960-03-07 Store ID: 1 Address: 740 Evergreen Terrace, Springfield Printing table employees after DELETE queries Table Values (total_rows: 5): ID: 101 Name: Homer Simpson BirthDate: 1955-07-23 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 103 Name: Peter Griffin BirthDate: 1967-06-28 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 121 Name: Lois Griffin BirthDate: 1968-01-18 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 120 Name: Marge Simpson BirthDate: 1965-05-21 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 112 Name: Ned Flanders BirthDate: 1960-03-07 Store ID: 1 Address: 740 Evergreen Terrace, Springfield Printing table employees after UPDATE Queries Table Values (total_rows: 5): ID: 101 Name: Homer Simpson BirthDate: 1955-07-23 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 103 Name: Peter Griffin BirthDate: 1967-06-28 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 121 Name: Lois Griffin BirthDate: 1968-01-18 Store ID: 2 Address: 31 Spooner Street, Quahog ID: 120 Name: Marge Simpson BirthDate: 1965-05-21 Store ID: 1 Address: 742 Evergreen Terrace, Springfield ID: 112 Name: Ned Flanders BirthDate: 1960-03-07 Store ID: 2 Address: 740 Evergreen Terrace, Springfield
Optimized MySQL Functions
PHP5 provides a new set of improved and optimized variants of mysql functions; these are the recommended APIs for use. Typically, the names of these functions start with "mysqli_" instead of "mysql_"; the letter "i" in the name probably stands for "improved"! In this section, we briefly describe some of these functions.
Functions mysqli_connect(), mysqli_query(), and mysqli_select_db() have an updated signature. mysqli_connect() is the new improved equivalent to mysql_connect(). The notable difference between mysqli_connect() and mysql_connect() is that the newer version also takes the name of database. Thus, mysqli_connect() is equivalent to two steps: mysql_connect() and mysql_select_db(). Its return value is a resource id that can be used with subsequent functions. Likewise, mysqli_query() is the new improved variant of mysql_query(); unlike mysql_query(), mysqli_query() also take the resource id returned from mysqli_connect(). Along the same lines, mysqli_select_db() also accepts the resource id returned from mysqli_connect() step.
Here is the changed signature of these functions:
$rid = mysqli_connect($db_host, $user, $password, $db_name); $query_rid = mysqli_query($rid, $query); $ret_val = mysqli_select_db($rid, $database_name);
Some of the improved functions continue to retain their old-style function signature. These functions are as follows:
$num_rows = mysqli_num_rows($result); $row = mysqli_fetch_row($result); $result = mysqli_close($db); $errno = mysqli_errno(); $error = mysqli_error();
Lastly, the improved functions set introduces two new functions to handle failure during the connect step. For non-connect steps, we can continue to use mysqli_errno() and mysqli_error() functions. These two new functions are:
$errno = mysqli_connect_errno(); $error = mysqli_connect_error();