PHP provides various methods for sending SQL queries to your MySQL database. The core SQL commands such as SELECT, INSERT, UPDATE and DELETE can be used to query and manage online data.

Now we have an understanding of PHP and a MySQL database how do the two interact? PHP offers three routes to querying your MySQL database.

  • The PHP MySQL extension
  • The PHP mysqli extension
  • PHP Data Objects (PDO)

The PHP MySQL Extension

The PHP MySQL extension was the first to allow interaction between PHP applications and MySQL databases. If you are using a version of MySQL above version 4.1.3 it is recommended that you use the newer mysqli extension or PDO (PHP Data Objects) therefore I not going to look at this technique.

The mysqli Extension – mysql(improved)

Introduced in PHP5, the mysqli extension builds on the original MySQL extension. This improved version of the extension has more sophisticated and secure SQL transaction support. As PHP5 supports object-oriented as well as procedural code, mysqli code can be written in either style.

PHP Data Objects (PDO)

PHP Data Objects are a generic way to connect to a database that may or may not by MySQL.

This gives you the flexibility of changing the database backend without having to alter your access methods.

If you want jump ahead to the PDO section.

mysqli – The ‘i’ is for ‘Improved’

At first we will use mysqli because of its improved security. We will also use examples written procedurally rather than using the object oriented approach as this will hopefully prove a gentler introduction to database integration.

Connecting to the MySQL Server with the mysqli extension

The first task with any database transactions is to tell PHP about our MySQL server. For this the mysqli extension uses the mysqli_connect() method.

This function takes arguments that identify the server to use. These are:

mysqli_connect(database host,  database user, database user password)

PHP methods return a value. With mysqli_connect it returns a link identifier that be used to create a variable identify the connection. Create the variable as follows:

$connection = mysqli_connect("myserver.com",  "mylogin", "mypassword");

Alternatively the connection details could be created as variables first:

<?php
$hostname = "myserver.com";
$user = "mylogin";
$password = "mypassword";
$connection = mysqli_connect($hostname, $user, $password);
?>

Once connected the connection variable is an optional argument for most mysqli functions.

To trap whether the connection succeeds we could add the following conditional logic to see if a connection variable was successfully created.

if (!$connection)
{
echo "Server connection Unsuccessful";
exit;
} 

Connecting to the MySQL Database with the mysqli extension

Once connected to MySQL server we can then choose a specific database to connect to. To do so we use the mysqli_select_db() function that accepts a database name as its argument. Ie

mysqli_select_db("connection identifier", "database_name");

As with the connection to MySQL we can use conditional logic to check the database has been successful.

<?php
mysqli_select_db($mydbconnection, "database_name") or die ('Could not connect to database');
?>

Tip: It is a good idea to place the MySQL connection and database connection code in an include as you are likely to need it in more than one file.

An example connection include file would be:

<?php
$hostname = "myserver.com";
$user = "userName";
$password = "xxxx";
$connection = mysqli_connect($hostname, $user, $password);
if(!$connection){
echo "Could not connect to server"; 
};
mysqli_select_db($connection, 'php01db');
if(!mysqli_select_db($connection, 'php01db')){
echo "Could not connect to database"; 
};
?>

Creating an SQL Query with mysqli_query()

Any SQL statement created in PHP needs to be placed within a mysqli_query() function. Ie

mysqli_query($connection, "SELECT * FROM products");

This can be assigned to a variable as follows:

$results = mysqli_query($connection, "SELECT * FROM products");

Very often you will find it useful to build the SQL query as variable ie

<?php
$query = "SELECT * FROM products";
$result = mysqli_query($query);
?>

Outputting the Query Results with mysqli_fetch_array()

The variable to which the mysqli_query() function has been assigned holds a recordset based on the SQL query. In order to output this we need to use another MySQL PHP function mysqli_fetch_array() which retrieves the values from the record set and stores them in an array. By default the mysqli_fetch_array() function retrieves one value from the record set, ie

<?php
print_r(mysqli_fetch_array($result));
//outputs the array for the first record only
//uses print_r to dump the array for testing
?>

Therefore to extract all query results we will use a loop, in this case a while loop.

<?php while($row = mysqli_fetch_array($result)){
echo "{$row['product_name']}";
}
?>

The result is that $row is an associate array. In fact with mysqli_fetch_array() the results can be accessed as either an associate or indexed array. To access values from the associate array we use the string name of the field name. To access values from the indexed array we use a numeric value – 0 been the first field extracted from the database, the others counted off from left to right.

When using mysqli_fetch_array() it can take an additional argument used to specify either an associate or indexed array. These arguments are MYSQL_ASSOC, MYSQL_NUM and MYSQL_BOTH (the default).

There are also two other variants of mysqli_fetch_array(). To extract an associate array you could just use mysqli_fetch_assoc() and you can use mysqli_fetch_row() for an indexed array.

Therefore:

mysqli_fetch_array($result, MYSQL_ASSOC) is the same as mysql_fetch_assoc()
mysqli_fetch_array($result, MYSQL_NUM) is the same as mysql_fetch_row()

Single Row Results

If you only expect one result from your SQL query, as for example when you pass the primary key for a table to the query you do not need to loop the results array. Simply create the array and then reference fields in it as required ie:

$row = mysqli_fetch_array($result);
echo "<h1>{$row['product_name']}</h1>";

This technique is used on ‘drilling deeper’ examples.

Close the Connection

Once records have been extracted it is good practice to close the database connection and also to free up memory by emptying the record set. The mysqli_close PHP function can be used to explicitly disconnect from the server. However, PHP will automatically close any open database connections that are no longer needed automatically so it can be omitted.

Preparing SQL for Queries

One of the main advantages of mysqli is the ability to run prepared statements. Before, PHP could only pass a fully formed SQL statement to MySQL as a query to be run. Now, PHP can pass a query without any data to MySQL. It does this once, and MySQL does some of the work in preparing to run the query at this point. Next, you associate variables to the columns, which is called binding the parameters. Finally, the query is executed. Why go to this trouble? It can provide a substantial performance increase, is more convenient for the developer, as well as more secure. It may seem a bit strange if this is the first time you are encountering it, but it is actually quite simple. Here is an example:

<?php
require_once('includes/connection.inc.php'); 
$stmt = mysqli_prepare($connection, "SELECT product_id, product_name FROM products WHERE product_id = ?");
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "i", $id);
$id = 12;
/* bind result variables */
mysqli_stmt_bind_result($stmt, $product_id, $product_name);
/* execute query */
mysqli_stmt_execute($stmt);
/* fetch result */
mysqli_stmt_fetch($stmt);
?>

The above example introduces a number of new mysqli methods.

A SQL statement is created using the mysqli_prepare method that takes a connection and a string as parameters. The string is SQL with variables replaced with question marks (?).

$stmt = mysqli_prepare($connection, "SQL HERE");

The question marked placeholders are replaced through parameter binding using mymqli_stmt_bind_param. This takes the prepared SQL as its first parameter then a string listing the parameter types and thirdly variables to bind to the each parameter. The question mark placeholders in the SQL are then replaced with the bound parameters. In the example below the variable $id is an integer and it replaces the first question mark placeholder in the prepared statement.

mysqli_stmt_bind_param($stmt, "i", $id);
$id = 12;

You may have more than one parameter to bind. As such you could have:

mysqli_stmt_bind_param($stmt, "is", $id, $name);
$id = 12;
$name = "Fred";

Here the ‘is’ means that the first bound ‘i’ parameter is an integer, the second ‘s’ a string. You can have as many bound parameters as required in the SQL ie:

mysqli_stmt_bind_param($stmt, "is", $id, $firstname, $surname);
$id = 12;
$firstname = "Fred";
$surname = "Bloggs";

The types of data that can be bound are:

i – integer
d – double
s – string
b – blob

The SQL statement will return values. So these are bound to result variables using mysqli_stmt_bind_result. The first parameter of this method is the prepare statement whilst this is followed by the variables you wish to bind to the results from the SQL. Given an SQL statement of:

SELECT product_id, product_name FROM products

You could use the following to create two variables ‘$product_id’ and ‘$product_name’ to hold the results from the query. The values from the query are allocated in order.

mysqli_stmt_bind_result($stmt, $product_id, $product_name);

Finally the SQL is executed to run the query using mysqli_stmt_execute and the results of the query are ‘fetched’ using mysqli_stmt_fetch.

/* execute query */
mysqli_stmt_execute($stmt);
/* fetch result */
mysqli_stmt_fetch($stmt);

The variables ‘$product_id’ and ‘$product_name’ could now be echoed as required.

Search/Results Architecture Options

When creating a search/results there are two approaches you could adopt.

Firstly create a search HTML page with a FORM that sends the data, either by $_POST or $_GET, to a results page. The results page would have the PHP logic in to make the query.

Search and Results

Alternativelty the search and results can be combined in one file. All the PHP logic to do the search will need to be in the same file. This is achieved by simply sending the FORM data back to the same file by putting no action in the FORM element as follows

<form action="" method="post"> 

Search and Results on same page

With this technique there is a requirement for some conditional logic so that the PHP to run the query only runs when the form is submitted. With a form of method ‘post’ this can be achieved by just looking for the presence of the $_POST super global ie

if(isset($_POST)){
// do logic
}

Single Result Prepared Statements

One place where it is always recommended to use a prepared statement is in a query built ‘on-the-fly’ from HTML form or query string , that is one that uses super global variables $_GET and $_POST.

The following code would be placed either in the results page in a two page architecture or inside conditional logic checking to see if the form was submitted in a single page architecture.

$stmt = mysqli_prepare($connection, "SELECT product_id, product_name FROM products WHERE product_id = ?");
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "i", $_POST['product_id']);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $product_id,$product_name);
/* execute query */
mysqli_stmt_execute($stmt);
/* fetch SINGLE ROW data */
mysqli_stmt_fetch($stmt);
/* close Statement */
mysqli_stmt_close($stmt);

In the above example the mymqli_stmt_bind_param method binds the ‘product_id’ value posted from a form of method ‘post’. As this is an integer we use ‘i’ to bind it to the ? placeholder.

The mysqli_stmt_bind_result method then create two variables $product_id and $product_name to hold the query results.

In the above example we are querying against the primary key so we would only expect one record to be returned as a result. When this is the case you only need to run the mysqli_stmt_fetch method to access the result bound variables. If you expect more than one result then you will need to loop around mysqli_stmt_fetch to retrieve each set of results.

Multiple Results Prepared Statements

In the next example we will search our data by product name so could reasonably expect more than one match to our query. This would require a prepared statement as follows:

$stmt = mysqli_prepare($connection, "SELECT product_id, product_name FROM products WHERE product_name like ?");
/* bind parameters for markers */
$queryParam = "%".$_POST['product_name']."%";
mysqli_stmt_bind_param($stmt, "s", $queryParam);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $product_id,$product_name);
/* execute query */
mysqli_stmt_execute($stmt);
/* store result */
mysqli_stmt_store_result($stmt);

In this example as we are doing a SQL LIKE query we need to place our value from the $_POST in the % wildcards. As such we concatenate a variable called $queryParam.

$queryParam = "%".$_POST['product_name']."%";

This is then bound as a string ‘s’ with mysqli_stmt_bind_param to the ? placeholder in the SQL.

In this example we haven’t yet used mysqli_stmt_fetch to retrieve the results of the query. That is because, as we expect more than one value, we’ll loop around mysqli_stmt_fetch instead as follows:

while(mysqli_stmt_fetch($stmt)){
echo "{$product_id}";
echo "<a href=\"details-example.php?pid={$product_id}\">{$product_name}</a><br />";
}

How Many Rows?

In the above example we could also use mysqli_stmt_num_rows($stmt) to find out how many rows of data were returned from the query. The code for this may look like:

echo "Total Number of products: ". mysqli_stmt_num_rows($stmt) . "<hr>";

Inserting a Record

To insert a new record into a MySQL database we use the same connection information as outlined for the example above but create a SQL INSERT statement. That is:

INSERT INTO tablename(field1, field2, fieldx) VALUES (value1, value2, valuex)

As with search/results pages you can use single or double page architecture to run this PHP. With double page architecture the page with the INSERT logic may have no HTML content but merely run the PHP logic and then use a header redirect to send the user to another page. PHP pages that perform a task but have no visible content are commonly used by developers.

As the values to be inserted will be coming in $_POST from a HTML form we are best advised to use mysqli to prepare a SQL statement. The code would be as follows:

$stmt = mysqli_prepare($connection, "INSERT INTO products(
product_name, 
cert_id, 
product_desc, 
product_image, 
product_price, 
release_date, 
product_offer) 
VALUES (?,?,?,?,?,?,?)");
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "ssssdsi", $_POST['product_name'], 
$_POST['cert_id'],
$_POST['product_desc'],
$_POST['product_image'],
$_POST['product_price'],
$_POST['release_date'],
$_POST['product_offer']);
/* execute query */
mysqli_stmt_execute($stmt);
/* get Errors */
$errorMsg = mysqli_connect_errno();
/* get No of Rows affected */
$noRows = mysqli_stmt_affected_rows($stmt);
/* close statement */
mysqli_stmt_close($stmt);

The above is similar to the search/results example with some additions. You will notice that there are many more bound variables than in the previous examples but the binding procedure remains the same with the placeholders and types.

There is also error handling. The mysqli_connect_errno provides an error message if something goes wrong. This has been feed into a variable we have chosen to call $errorMsg.

Finally we have used mysqli_stmt_affected_rows to ascertain how many rows were affected by the query. This is another useful way of knowing whether the query was successful.

Updating Records

When updating records use the SQL UPDATE statement, the syntax which is as follows:

UPDATE table
SET fieldtitle2 = value2, fieldtitle3 = value3
WHERE fieldtitle1 = value1

When preparing SQL that updates a record we will need to have some way of identifying the records to be updated first. This could either be a search or list page so the user can pick up the correct record and with it its primary key via a querystring for example:

echo "<a href=\"update.php?product_id={$row['product_id']}\">Update - {$row['product_name']}</a>";

The architecture could be as follows with ‘list.php’ used to identify the record you wish to change, ‘form.php’ providing a HTML form populated with the current values for that records and finally ‘update.php’ doing the actual PHP logic to run the SQL update.

Update architecture

In the above example the ‘form.php’ would then need a SQL SELECT query based on the value sent as the product_id to extract a unique record. An example query based on a querystring would be as follows:

$stmt = mysqli_prepare($connection, "SELECT product_id, cert_id, product_name, product_desc, product_image, product_price, release_date, product_offer FROM products WHERE product_id = ?");
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "i", $_GET['pid']);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $product_id, $cert_id, $product_name, $product_desc, $product_image, $product_price, $release_date, $product_offer);
/* execute query */
mysqli_stmt_execute($stmt);
/* fetch SINGLE ROW data */
mysqli_stmt_fetch($stmt);
/* close Statement */
mysqli_stmt_close($stmt);

As in the INSERT example we use the prepare SQL technique to build our SQL statement. Again we have some error handling as well as feedback on how many rows have been affected.

$stmt = mysqli_prepare($connection, "UPDATE products SET 
product_name = ?, 
cert_id = ?, 
product_desc = ?, 
product_image = ?, 
product_price = ?, 
release_date = ?, 
product_offer = ?
WHERE product_id = ?");
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "ssssdsii", 
$_POST['product_name'], 
$_POST['cert_id'],
$_POST['product_desc'],
$_POST['product_image'],
$_POST['product_price'],
$_POST['release_date'],
$_POST['product_offer'],
$_POST['pid']);
/* execute query */
mysqli_stmt_execute($stmt);
/* get Errors */
$errorMsg = mysqli_connect_errno();
/* get No of Rows affected */
$noRows = mysqli_stmt_affected_rows($stmt);
/* close statement */
mysqli_stmt_close($stmt);

Deleting Records with PHP and MySQL

To delete a record it is again important that the record has been correctly identified via the primary key. A suggested architecture for this could be:

Delete Architecture

Given the difficulty in reversing a SQL DELETE statement you should offer the user the opportunity to double check/confirm the record that they are about to delete. Also it is advisable to use a $_POST variable rather than a $_GET from a querystring to run the SQL delete. The ‘form.php’ page in this architecture performs both tasks. The product chosen from the list is presented in a HTML form of method ‘post’. If the form is posted the ‘delete.php’ file runs the SQL delete logic. On the ‘form.php’ a simple link back to ‘list.php’ allows the user to bail out of the deletion process.

In the example below the SQL DELETE statement expects to be feed a variable product_id via $_POST. The following code would be placed in ‘delete.php’.

$stmt = mysqli_prepare($connection, "DELETE FROM products WHERE product_id = ?");
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "i", $_POST['pid']);
/* execute query */
mysqli_stmt_execute($stmt);
/* get Errors */
$errorMsg = mysqli_connect_errno();
/* get No of Rows affected */
$noRows = mysqli_stmt_affected_rows($stmt);
/* close statement */
mysqli_stmt_close($stmt);

Warning: It is very difficult to retrieve data once it has been deleted. There is a strong argument for NEVER deleting a record but flag it for archiving/removal with an SQL UPDATE rather than the dangerous DELETE.