How to Run MySQL Queries with PHP

Obtaining Data from a MySQL Database Using the PHP Mysqli Object

Mar 29, 2009

SQL statements are used to obtain data from databases. A PHP programmer can use them with the mysqli object to build a very powerful application.

SQL (Structured Query Language) is the computer language with which humans (such as PHP programmers) communicate with the majority of databases (such as MySQL). It is pseudo-English - meaning that the queries can be understood relatively easily by anyone who reads them for example:

Create database if not exists db_demo;

This means, “create a database named db_demo (unless it already exists). Another example is:

Select user, host from user;

Which means, “display the contents of two fields (user and host) from the table named ‘user’”. These two queries show that there are different types of query and the type of query uses affects the result returned to the PHP application. Fortunately PHP handles all types of queries equally as well.

PHP and MySQL Query Results

It is, therefore, just a matter of sending the queries to the database and then processing the results.

Sending Queries to MySQL from PHP

The PHP programmer carries out all of these by creating a PHP mysqli (MySQL – improved) object:

$user = "db_user";

$user_password = "db_password";

$db_name = "db_demo";

$db = new mysqli ("localhost", $user, $user_password, $db_name);

Here a new mysqli object is instantiated and this automatically connects to the database. Next the object’s query method is used to send SQL to the database:

$sql = "insert into articles

(title, url)

values

('How to Use the PHP Text Database API',

'http://www.suite101.com/functions/article/edit.cfm/103536')";

if (!($db->query($sql))) {

echo $db->error . "<br>";

} else {

echo "Added " . $db->affected_rows . " record(s)<br>";

}

In the example above only true or false is returned, but the query methods can also be used to return a record set:

$sql = "select * from articles";

if ($rs = $db->query($sql)) {

If the record set has been obtained then it can be processed record by record. Each record in the record set is accessed as an array (containing both associative and numerical elements):

echo "<table width=100%>";

while ($record = $rs->fetch_array(MYSQLI_BOTH))

#Alternatives are MYSQLI_ASSOC and MYSQLI_NUM

{

echo "<tr>

<td>" . $record['title'] . "</td>

<td>" . $record[2] . "</td>

</tr>";

}

echo "</table>";

}

Finally the memory used by the record set and the connection can be freed up once they’ve been finished with:

$rs->close();

$db->close();

At the end of this process a new record will have been added to the database and the web page user will see the records stored in the table displayed in the web browser.

Summary

SQL is the language with which humans communicate with many databases. The PHP programmer can use SQL to write queries that they can send to MySQL databases by using the mysqli object and its query method.

Once the resulting record set is returned to the PHP application then the programmer can process the results and load the records into arrays by using the fetch_array method. They can then use the information elsewhere in their application or display them to the users of their web site.

The copyright of the article How to Run MySQL Queries with PHP in PHP Programming is owned by Mark Alexander Bain. Permission to republish How to Run MySQL Queries with PHP in print or online must be granted by the author in writing.