Tutorial using PHP to Query MySQL

Posted in Tutorials

In this tutorial, we will use PHP to query a MySQL database.  For the purpose of this example, suppose we want to query the last 5 posts from a WordPress MySQL database.  We will need to know the hostname, database username, and database password for the MySQL database.

There are three primary methods to query a MySQL database from PHP…

1. mysql API (kind of out-dated and deprecated in PHP version )

2. mysqli API (Procedural programming.  Allows prepared statements)

3. PDO (Object oriented and works for other types of databases as well.  Allows prepared statements.)

We will be using the mysqli API for this tutorial.

1.  Start by calling PHP function mysqli_connect() to connect to the MySQL database.  We will need to know the hostname, database username, database password, and database name for the MySQL database which we pass into the function …

mysqli_connect will return the “database handle” in variable $db.  To see if the connection was successful or not, check to see if mysqli_connect_errno returns an error number.  It will return 0 if there is no error.  If there is an error, we can call mysqli_connect_error() to determine the error message (as shown above).

2. After connecting successfully, we run a SQL statement by using mysqli_query passing into it the database handle and the SQL statement (ending semicolon not necessary in SQL statement here)…

This will return something in $result.  If that is false, that indicates there is likely a syntax error with our SQL statement.  Otherwise, we can use the return set in $result like this …

We decided to use mysqli_fetch_assoc() because it returns an associate array with keys as column names.  You could have used mysqli_fetch_row(), mysqli_fetch_array(), or mysqli_fetch_object() which will return similar data but arranged in the array structure differently.

After use of the resultset, we should release it resources and close the database handle …

Running this code, we get the result set displayed in a table in our browser …

 

---