Getting PHP to talk to MySQL

From Notes

Jump to: navigation, search

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.

Contents

Setting Up

For MAMP users, there is a good tutorial available here.

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';     // To create a user use 'privileges' from home pg   
$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: <br />" . 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: <br />" . 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: <br />" . 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] . '<br />';
  echo 'Author: ' . $result_row[4] . '<br />';
  echo 'Pages: ' . $result_row[2] . '<br />';
}

Closing the Connection

It's a good idea to close the connection when you are done processing:

mysql_close($connection);

Putting it Together

Personal tools
NSF K-12