Getting PHP to talk to MySQL

PHP contains standard functions for working with a MySQL database. On this page we will describe several of these functions and provide a simple example of a PHP script that accesses a MySQL database.

The Process
The following steps are required to get PHP to work with MySQL.
 * 1) Connect to the database server.
 * 2) Select the database to use.
 * 3) Build a SELECT statement (a query).
 * 4) Perform the query.
 * 5) Display the results.

Connecting to the Database
To avoid having to type login details throughout the program, we can use variables to store certain information about our MySQL connection: $db_host = 'localhost';  // server name $db_database = 'cpsc110';   // database name $db_username = 'root'; $db_password = 'test1234';

The statement to connect to the database then becomes: $connection = mysql_connect($db_host, $db_username, $db_password); if (!$connection) { die ("Could not connect to the database: " . mysql_error); } Note how this code generates an error message if the connection fails.

Selecting the Database
Once a successful connection to the server has been made, you need to select the specific database that you will be using in your program. The assumption here is that the database has already been created (using PhpMyAdmin or the MySQL command-line interface): $db_select = mysql_select_db($db_database); if (!$db_select) { die ("Could not select the database: " . mysql_error); }

Constructing a SELECT Query
A SELECT query can be constructed simply as a string literal: $query = "SELECT * FROM books NATURAL JOIN authors";

Or you can use variable assignments and concatenation to break up the query: $select = ' SELECT '; $column = ' * '; $from = ' FROM '; $tables = ' books '; $where = ' NATURAL JOIN authors '; $query = $select.$column.$from.$tables.$where;

Executing the Query
To execute the query, we use the mysql_query function: $result = mysql_query($query); if (!$result) { die ("Could not query the database: " . mysql_error); } If the query is successful, the results are returned as result set corresponding to a table with rows and columns. To display the results, you have to process each row of the table.

Using (displaying) the Results
The mysql_fetch_row function can be used in a loop to retrieve each row of the result set. The row is an array and columns can be referenced by their numeric indexes. The columns are arranged in order according to the query's column order or according to the DB table itself, if SELECT * was used. while ($result_row = mysql_fetch_row($result)) { echo 'Title: '. $result_row[1]. ' '; echo 'Author: '. $result_row[4]. ' '; echo 'Pages: '. $result_row[2]. ' '; }

Closing the Connection
It's a good idea to close the connection when you are done processing: mysql_close($connection);

Putting it Together

 * Here's a complete example that uses a database of light bulb jokes: A PHP/MySQL Example.


 * Here's a complete example that solves the PHP/MySQL problem from homework 8--i.e, it adds forms to the page to select jokes by category and a second form to insert new jokes: A PHP/MySQL Example with HTML Forms