PHP scripts are used to send SQL statements (e.g., SELECT, UPDATE, DELETE, INSERT, DROP AND CREATE) to a MySQL database to be executed and the result (e.g., retrieval, insertion, deletion, or selection of data) is returned to the PHP script or an error can be displayed if no result is returned.

Test Database

It is a good idea to test the database to see if it will return the desire results before writing a lot of code for the other pages.

1. Write the following PHP script code block below the first closing PHP script code block (e.g., ?>):
   
   

   ?>
   
   <?php
   // TEST database to see if it will return a SELECT query
   if($db_connection)
   {
   // 3. COMMUNICATE with Database: Store reference of SQL STATEMENT in a variable
   $query_employees = "SELECT * FROM employees";
   // Store a REFERENCE of the QUERY in a variable from the SQL variable using the mysql_query method.
   $recordset_employees = mysql_query($query_employees);
   // Store the RESULT of the query into an associative array using the mysql_fetch_assoc method.
   $row_employees = mysql_fetch_assoc($recordset_employees);
   print ("<p>". $row_employees["FirstName"] . " " . $row_employees["LastName"] ."</p>");
   }
   ?>

   
   
   CODE EXPLANATION:
   

   • The $query_employees variable stores a reference the the query that will be made (e.g., SELECT * FROM employees). The asterisk ( * ) (Shift+8) is used as a wildcard to select ALL COLUMNS from the table.
   • The $recordset_employees variable is used to store a REFERENCE of the QUERY from the SQL variable using the mysql_query method.
   • The $row_employees variable is used to store the RESULT of the query into an associative array using the mysql_fetcth_assoc method.
2. CHECK POINT: Save the file and preview it in a browser. You should see ONLY the FIRST RECORD of the FirstName and LastName columns of the database displayed. If you don't, ensure that you specify the database table name and not the database name.
   
   
   TIP: If you get the error "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in..." use the following debugging techniques:

   • There may be something wrong with the SQL query statement (e.g., SELECT * FROM employees). Copy and paste it in phpMyAdmin to see if it returns the correct result when the SQL is executed.
   • Assign a print statement to the query (e.g., print ($query_employees = "SELECT * FROM employees";)) to trace the result. You may find there is an error with the single or double quotes, etc.
   • Write a conditional statement on the end of the query. This will return the result from phpMyAdmin which may be a more useful error message. For example, if you change the query to read "SELECT FirstNameX FROM employees" and then rewrite the code to:
     $recordset_employees = mysql_query($query_employees) or die(mysql_error());.
     The error message that will get displayed will be the same as if you had executed the SQL in phpMyAdmin:
     Unknown column 'FirstNameX' in 'field list'
3. Comment out the print statement and then use the $row_employees variable as an argument for a while loop and then wrap and modify the print statement in a while loop to retrieve all of the records from the database.
   
   

   // Store the RESULT of the query into an associative array using the mysql_fetch_assoc  method.
   $row_employees = mysql_fetch_assoc($recordset_employees);
   // Comma delimited version -------------------------------------------------------------
   // Loop to create ROWS -------------------------	
   while($row_employees = mysql_fetch_assoc($recordset_employees))
   {
   	// Loop to create COLUMNS (FIELDS) -------------
   	foreach ($row_employees as $field)
   	{
   		 print $field . ", ";
   	}
   	print "<br/>";
   }
   }
   ?>

   
   
   CODE EXPLANATION:
   

   • The mysql_fetch_assoc() method is used to loop through an associate array of elements to create the COLUMNS (FIELDS).
   • The foreach loop iterates through the $row_employees array to create the ROWS.
   • The print command displays the results of the loop.
   • A <br> tag was added to the second print statement to break each record onto a separate line.
4. CHECK POINT: Save the file and preview it in a browser again. You should see all of the current records returned from the database like a CVS (Comma Separated Values) file:
   
   
   
5. Comment out while loop with /* and */.
   WHY: So that another example can be displayed using a table.
   
   

   // Store the RESULT of the query into an associative array using the mysql_fetch_assoc  method.
   $row_employees = mysql_fetch_assoc($recordset_employees);
   // Comma delimited version -------------------------------------------------------------
   // Loop to create ROWS -------------------------	
   /* while($row_employees = mysql_fetch_assoc($recordset_employees))
   {
   	// Loop to create COLUMNS (FIELDS) -------------
   	foreach ($row_employees as $field)
   	{
   		 print $field . ", ";
   	}
   	print "<br/>";
   } */
   
   }
   ?>

6. Write (or copy) the following highlighted code. Comments are optional:
   WHY: To create a table of the data from the database.
   
   

   // Create Table version -------------------------------------------------------------
   // 1. Print static opening table tag
   print "<table border='1' style='border-collapse:collapse'>";
   // 2. Print static table header row with table header data
   print "<tr><th>EmployeeID</th><th>PasswordID</th><th>First Name</th>
              <th>Last Name</th><th>City</th><th>State</th>
   		      <th>State Date</th><th>Department</th><th>Note</th>
   		      <th>Image</th><th>Date Entered</th>
   	  </tr>";
   // 3. Loop through data to print dynamic tags for rows
   while ($row = mysql_fetch_row($recordset_employees))
    {
   	 print '<tr>'; 	 
      foreach ($row as $field)
      {
        print '<td>' . $field . '</td>'; 
      }
        print '</tr>';
    }
   // 4. Print static closing table tag
   print "</table>";
   } // end of "if" statement ------------------------------------------------------------
   ?>
   

   
   
7. CHECK POINT: Save the file and preview it in a browser again. You should see all of the current records returned from the database in a HTML / PHP generated table.
   
   

Create Connection Script

A connection script will be converted from the previous code that will be used by all pages within the web site or app.

1. Create an includes folder in the site root directory.
2. Select File > Save As... and save a copy of the file as connection.inc.php in the includes folder.
   
   
   CAUTION: Notice the .inc as part of the file name.
3. Comment out the print statement (e.g., // print ("Database connected successfully.");).
4. Delete the second PHP script code block as it was only used to test the database. This file will be used as a PHP server-side include later.
5. Save the file again.