Question:

How do I display the contents of a database table on the internet?

Answer:

Step 1: Connect to MySQL

The first step is to make a database connection. This can be done via mysql_connect (or mysqli_connect) to connect to MySQL and mysql_select_db (or mysqli_select_db) to select the actual database.

Example

//-----
//make sure you supply valid arguments for the following placeholders.
//DB_HOST
//DB_USER
//DB_PASSWORD
//DB_NAME
//-----

//Make the connection
$dbc = mysql_connect('DB_HOST', 'DB_USER', 'DB_PASSWORD')
            or die('Could not connect to database: ' . mysql_error());

//select the database
mysql_select_db('DB_NAME');

Step 2: Create and execute SQL query

The next step is to create a query and execute it. This is done via mysql_query. In the case of a SELECT statement (like our example) the function returns the result, hence the reason why we assign the return value to a variable. In the case of INSERT, DELETE or UPDATE the function returns TRUE if the function succeeded or FALSE if it did not.

$query = "SELECT fruit_category_id,
                 fruit_category_desc
          FROM   fruit_category";

$result = mysql_query($query);

Once the query has executed we can use mysql_fetch_array (or mysqli_fetch_array)to extract each row from the $result variable (known as a resource).

while($row = mysql_fetch_array($result, MYSQL_BOTH))
  {
  echo '<p>Category Id: ' . $row[0] . ' Description: ' . $row[1] . '</p>';
  //do some other stuff…
  }

Output:

Category Id: 1 Description: Stone Fruit
Category Id: 2 Description: Berries
Category Id: 3 Description: Melons

mysql_fetch_array returns an array of one row of data and increments the internal data pointer by one. If there is no more data to return, then the function returns FALSE. The second parameter in the call to mysql_fetch_array refers to how the returned array is indexed. In the case of MYSQL_BOTH it is indexed numerically (0 being the first field selected, 1 the second field and so forth) and by field name. I.e fruit_category_id and fruit_category_desc.

Thus $row[0] and $row['fruit_category_id'] would have been interchangeable in our example.

Finally if you have a lot of queries running on this page you may consider 'freeing up' the memory used for the result set once you've finished with it. This can be done by calling mysql_free_result.

mysql_free_result($result);

Additional notes:

Once mysql_fetch_array reaches the last row, it doesn't reset the internal data pointer. Thus subsequent calls to mysql_fetch_array with the same result set will always return FALSE (i.e. no more records). You can reset the data pointer with the mysql_data_seek. This function can also be used with mysql_num_rows to ensure that the index value is within range of the current result set.

PHP Reference Manual

mysql_connect - Connect to MySQL

mysql_fetch_array - Fetch 1 row of data from the result

mysql_free_result - Free up the memory used by the result resource

mysql_query - Execute query

mysql_select_db - Select a Database

MySQL

How do I limit the number of records displayed?
Learn about the LIMIT clause and begin the steps towards pagnating results (query results over several pages).

How do I display a field value once?
Learn how to use SQL with PHP to format your results on screen

Comments or questions relating to this article have been disabled. They will be back soon.