PHP: Connecting to Databases

Derek Bridge

Department of Computer Science,
University College Cork

PHP: Connecting to Databases

Aims: to learn how to write a PHP script that

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

PHP and databases

• PHP has support for over 15 different database management systems, including
  • MySQL
  • Oracle
• When you install PHP on your system, you must configure it so that it has the appropriate library of functions available for your database management 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 Nerdland Bird Sanctuary database has been created and populated with data

 
// Try to connect to database
$dbconnection = mysqli_connect( $host, $user, $password, $dbname );
if ( ! $dbconnection )
{
    die('Unable to connect to database');
}
 
// Query the database
$sql = "SELECT * FROM birds;";
$dbresult = mysqli_query( $dbconnection, $sql );
if ( ! $dbresult )
{
    die('Error in query ' . mysqli_error( $dbconnection ));
}

// Display the results of the query
if ( mysqli_num_rows( $dbresult ) == 0 ) 
{
    echo "<p>No rows found</p>";
}
else 
{
    // Output each row of query results
    while ( $row = mysqli_fetch_assoc( $dbresult ) ) 
    {
        echo "<p>{$row['bnum']} {$row['name']} {$row['species']}</p>";
    }
}
 
// Free up memory and close the database connection
mysqli_free_result( $dbresult );
mysqli_close( $dbconnection );

Connecting to the database server

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

  $dbconnection = mysqli_connect( $host, $user, $password, $dbname );

• In our labs,
  • $host will be 'localhost'
  • $user will be your usual CS id, e.g. 'dtab6' (or probably 'root' at home)
  • $password will not be your normal Windows/Unix password(s); it will be the password you used in CS1106 (or whatever you chose when configuring MySQL at home)
  • $dbname will be the name of your database — the one you used in CS1106, e.g. '2016_dtab6' (or whatever you used when creating the database at home)

Checking that the connection is made

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

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

• The die function causes its parameter to be echoed and then causes execution of the script to terminate

Checking that the connection is made

• An alternative:

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

• Another alternative:

  ($dbconnection = mysqli_connect( $host, $user, $password, $dbname )) ||
      die('Unable to connect!');

• Class exercise: How do these work?

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 mysqli_query function:

  $dbresult = mysqli_query( $dbconnection, $sql );

• The function returns false if the query fails:

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

  The function mysqli_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 ( mysqli_num_rows( $dbresult ) == 0 ) 
  {
      echo '<p>No rows found</p>';
  }
  else 
  {
      …
  }

Processing the result set, continued

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

  if ( mysqli_num_rows( $dbresult ) == 0 ) 
  {
      echo "<p>No rows found</p>";
  }
  else 
  {
      while ( $row = mysqli_fetch_assoc( $dbresult ) ) 
      {
          …do something with $row…
      }
  }

• Class activity: Understand how and why this works, including the case where the result set is empty

Unbounded repetition or bounded repetition

• We don't know how many rows there will be in the result set, hence a while-loop seems most appropriate: unbounded repetition
• In fact, that's not entirely true:
  • it is possible to find out how many rows there are in the result set
  • then we can use a for-loop: bounded repetition
  • but this approach is a little more cumbersome and used rarely, if ever

  $num_of_rows = mysqli_num_rows( $dbresult );
  if ( $num_of_rows == 0 ) 
  {
      echo "<p>No rows found</p>";
  }
  else 
  {
      for ( $i = 0; $i < $num_of_rows; $i++ )
      {
          $row = mysqli_fetch_assoc( $dbresult );
          …do something with $row…
      }
  }

• However, programmers just don't write it this way!

Processing the result set, continued

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

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

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

  if ( mysqli_num_rows( $dbresult ) == 0 ) 
  {
      echo "<p>No rows found</p>";
  }
  else 
  {
      while ( $row = mysqli_fetch_assoc( $dbresult ) ) 
      {
          echo "<p>{$row['bnum']} {$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:

  mysqli_free_result( $dbresult );

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

  mysqli_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

// Try to connect to database
$dbconnection = mysqli_connect( $host, $user, $password, $dbname );
if ( ! $dbconnection )
{
    die('Unable to connect to database');
}

// Insert into the database
$sql = "INSERT INTO donations (snum, bnum, donation)
        VALUES ({$snum}, {$bnum}, {$donation});";
$dbresult = mysqli_query( $dbconnection, $sql );
if ( ! $dbresult )
{
    die('Error in query ' . mysqli_error( $dbconnection ));
}
 
// Close the connection
mysqli_close( $dbconnection );

Notes on insertion

• It is much the same as querying
• But because this is an insertion, not a query, $dbresult does not contain a result set
• So it doesn't make sense to use functions such as mysqli_num_rows, mysqli_fetch_assoc and mysqli_free_result