PHP and MySQL - Selecting Data

When performing an operation on a MySQL database, such as selecting, inserting, updating and deleting data, the first thing that needs to be done is to connect to the database, as previously described.

In terms of selecting data, the SQL 'select' statement needs to be used. This takes the following format.

SELECT column_name FROM table_name

Where multiple columns need to be selected they can be separated by commas.

SELECT column_name1, column_name2, column_name3 FROM table_name

If all the columns in a table are required, then the comma separated list can be replaced by a single '*'.

SELECT * FROM table_name

The following table of data, called 'person', will be used in the example below for selecting data.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20

Note that by default, dates in a MySQL database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

The example below will select all records, in 'lastname', 'firstname' and 'dob' order, from the table 'person', where the 'dob' is between 1 January 1980 and 31 December 1989, and if any records are returned displays them in a table.

<?php

   // Connect to the database.
   require_once('database-connect.php');
   
   // Query parameters.
   $dobLower = "1980-01-01";
   $dobUpper = "1989-12-31";
 
   try
   {

      // Prepare the query.
      $results = $connect->prepare("SELECT id, firstname, lastname, title, dob
                                    FROM person
                                    WHERE dob BETWEEN ? AND ?
                                    ORDER BY lastname, firstname, dob");

      // Bind the parameters.
      $results->bindParam(1, $dobLower);
      $results->bindParam(2, $dobUpper);

      // Execute the query.
      $results->execute();

   } catch(Exception $e) {

      // If query fails, display an error and exit.
      echo "Error retrieving person information.";
      exit;
   }

   // Assign the results to a variable.
   $people = $results->fetchAll(PDO::FETCH_ASSOC);

   // Check to see if any records have been returned.
   if (!$people)
   {

      // Display a message if no records exist for the criteria.
      echo "<p>There are no people that match the search criteria.</p>";

   }
   else
   {

      // Display the results in a table.
      echo "<table>";
         echo "<tr>
                  <td>id</td>
                  <td>firstname</td>
                  <td>lastname</td>
                  <td>title</td>
                  <td>dob</td>
               </tr>";
               
         foreach ($people as $person)
         {
            echo "<tr>
                     <td>" . $person["id"] . "</td>
                     <td>" . $person["firstname"] . "</td>
                     <td>" . $person["lastname"] . "</td>
                     <td>" . $person["title"] . "</td>
                     <td>" . $person["dob"] . "</td>
                  </tr>";
         }
      echo "</table>";
   }

?>

The above example uses the 'foreach' loop, which is a variation on the 'for' loop, to go through the records that have been returned and display them as rows in a table. The output can be seen below. A '.' is used to connect two parts of a string together when outputting the information onto the screen using the 'echo' statement.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
4 Alan White Mr 1989-03-20