PHP: Accessing Databases

Derek Bridge

Department of Computer Science,
University College Cork

PHP: Accessing Databases

Aims: to learn how to write a PHP script that

• connects to a database management system
• selects a database
• retrieves data from the database tables
• processes the data that it retrieves using while loops
• inserts data into the database tables

PHP and databases

• PHP has support for over 15 different database management systems, including
  • MySQL
  • Oracle
• The libraries of functions to support these must be included when PHP is compiled and installed on your system
• But the functions in each library have different names
  • the downside is that your script ends up being tied to one particular database management system

A small example

Assume the Sudury Bird Sanctuary database has been created and populated with data

<?php 
 // Try to connect to database
 $dbconnection = mysql_connect($host, $user, $password);
 if ( ! $dbconnection )
 {
    die('Unable to connect!');
 }
 
 // Try to select database
 $dbselection = mysql_select_db($dbname);
 if ( ! $dbselection )
 {
    die('Unable to select database');
 }
 
 // Query the database
 $sql = "SELECT * FROM birds;";
 $dbresult = mysql_query($sql);
 if ( ! $dbresult )
 {
    die('Error in query ' . mysql_error());
 }
 // Display the results of the query
 if (mysql_num_rows($dbresult) == 0) 
 {
    echo 'No rows found';
 }
 else 
 {
    // Output each row of query results
    while ($row = mysql_fetch_assoc($dbresult)) 
    {
       echo "<p>$row[num] $row[name] $row[species]</p>";
    }
 }
 
 // Free up memory and close the connection
 mysql_free_result($dbresult);
 mysql_close($dbconnection);
?>

Connecting to the database server

• To communicate with a MySQL database server, you open a connection:

  $dbconnection = mysql_connect($host, $user, $password);
  

• In our labs,
  • $host will be 'cosmos.ucc.ie'
  • $user will be your usual CS id, e.g. 'dtab1'
  • $password will not be your normal Windows/Unix password(s); it will be the password we sent in an email before Christmas
  • the name of your database (see later) was also given in that email

Checking that the connection is made

• The function mysql_connect returns a 'link identifier' or false if a connection cannnot be made
• We should check that a connection was made
• Here's how:

  $dbconnection = mysql_connect($host, $user, $password);
  if ( ! $dbconnection )
  {
     die('Unable to connect!');
  }
  

Checking that the connection is made

• An alternative:

  if ( ! $dbconnection = mysql_connect($host, $user, $password) )
  {
     die('Unable to connect!');
  }
  

• Another alternative:

  $dbconnection = mysql_connect($host, $user, $password) or
     die('Unable to connect!');
  

• Class exercise: How do these work?

Selecting a database

• Once connected to the server, you select a database for use

  $dbselection = mysql_select_db($dbname);
  if ( ! $dbselection )
  {
     die('Unable to select database');
  }
  

• The database name ($dbname) will be the name we sent you in an email just before Christmas

Creating a query and executing it

• The query is written in SQL, and stored as a string in a PHP variable:

  $sql = "SELECT * FROM birds;";
  

• Then it is executed using the mysql_query function:

  $dbresult = mysql_query($sql);
  

• The function returns false if the query fails:

   if ( ! $dbresult )
   {
      die('Error in query ' . mysql_error());
   }
  

  The function mysql_error returns a string describing the most recent error

Processing the result set

• If the query was successful, the result set has been stored in $dbresult
• There are various things we can now do, e.g. find out how many rows in the result:

  if (mysql_num_rows($dbresult) == 0) 
  {
     echo 'No rows found';
  }
  else 
  {
     ...
  }
  

Processing the result set, continued

• Each time we call the mysql_fetch_assoc function,
  • it returns a row from the result set and moves onto the next row, or
  • it returns false, when there are no more rows
• So, we'll usually want to write some kind of loop:

  if (mysql_num_rows($dbresult) == 0) 
  {
     echo 'No rows found';
  }
  else 
  {
     while ($row = mysql_fetch_assoc($dbresult)) 
     {
        ...do something with $row...
     }
  }
  

Processing the result set, continued

• The function mysql_fetch_assoc returns each row as an associative array
• E.g. after the first time it is called in the example above, $row will contain:

  ('num' => 1,
   'name' => 'Stevie',
   'species' => 'swan')
  

• So we can use $row['num'], $row['name'] and $row['species']:

  if (mysql_num_rows($result) == 0) 
  {
     echo 'No rows found';
  }
  else 
  {
     while ($row = mysql_fetch_assoc($result)) 
     {
        echo "<p>{$row['num']} {$row['name']} {$row['species']}</p>";
     }
  }
  

Freeing the space and closing the connection

• The result set ($dbresult) may occupy a lot of memory. Once you've finished with the results of the query, you can free the memory:

  mysql_free_result($dbresult);
  

• If no further queries are to be run, you can close the connection:

  mysql_close($dbconnection);
  

• You don't have to do either of these: the memory will be freed and the connection closed automatically when the script terminates

Inserting values

Imagine that $snum, $bnum and $donation contain values, e.g. from a form, and that we have validated them

<?php
 // Try to connect to database
 $dbconnection = mysql_connect($host, $user, $password);
 if ( ! $dbconnection )
 {
    die('Unable to connect!');
 }
 
 // Try to select database
 $dbselection = mysql_select_db($dbname);
 if ( ! $dbselection )
 {
    die('Unable to select database');
 }
 
 // Insert into the database
 $sql = "INSERT INTO donations (snum, bnum, donation)
         VALUES ({$snum}, {$bnum}, {$donation});";
 $dbresult = mysql_query($sql);
 if ( ! $dbresult )
 {
    die('Error in query ' . mysql_error());
 }
 
 // Close the connection
 mysql_close($dbconnection);
?>

Notes on insertion

• It all looks much the same
• But because this is not a query, $dbresult does not contain a result set
• So it doesn't make sense to use functions such as mysql_num_rows, mysql_fetch_assoc and mysql_free_result