Lecture: MySQL and PHP

Accessing MySQL via PHP

In Homework #8 you used the command line interface to work with MySQL. Your first step was to connect to the MySQL server. Then you provided a password that matched the provided user name. Next, you selected a database. Then you could SQL commands to create tables, insert data, select data, or whatever else you wanted to do with the selected database.

A PHP script uses the same steps when working with MySQL.

Connecting to the MySQL Server

For a PHP script to access a MySQL database, the first step is to establish a connection between the script and MySQL. The function to do that is mysqli_connect(). That function has four parameters: the server name, the login id, the password, and the database name.

The PHP command to issue a query is mysqli_query(). The two parameters are the database connection and the query string. The query string could be a select statement, or an insert command, or delete, or anything else.

The result of the query is an array. Each row in the array equates to the rows that the command line would show you. To access the rows, use the function mysqli_fetch_array() or the function mysqli_fetch_object(). Those functions return a different row each time you call them.

Here is a simple program to print the contents of our warehouse table. This code uses fetch_array to pull out each record from the results array. The $row variable is an associative array. The indexes of $row are the table's field names. Note that the these index names are case sensitive.

<?php

// connect the database
$DBconn = new mysqli ("deltona.birdnest.org", "my.dannellys2", "xxx", "my_dannellys2_default");

// submit the query
$query = "select * from warehouse;";
$result = mysqli_query ($DBconn, $query);

// debugging code to make sure the query worked
$rowcount = mysqli_num_rows($result);
echo ("row count = $rowcount <P>");

// output the query result, using associtive array method
while ($row = mysqli_fetch_array ($result))
  {
   echo ("whnum: " . $row["WhNumb"] . "<br>");
   echo ("City: " . $row["City"] . "<br>");
   echo ("Floors:" . $row["Floors"] . "<P>" );
  }

?>

Displaying the MySQL Table in an HTML Table

Here is another example very similiar to the one above. This example uses fetch_object instead of fetch_array. This example also has nicer output by creating an HTML table.

<center>
<h3>Simple Database Test</h3>

<table rules=all>
<tr>
<td bgcolor=lightgrey>WhNumb
<td bgcolor=lightgrey>City
<td bgcolor=lightgrey>Floors


<?php

// connect the database
$DBconn = new mysqli ("deltona.birdnest.org", "my.dannellys2", "xxx", "my_dannellys2_default");

// submit the query
$query = "select * from warehouse;";
$result = mysql_query ($DBconn, $query);

// output the query results as an HTML table
while ($row = mysqli_fetch_object ($result))
  {
   echo ("<tr>");
   echo ("<td> $row->WhNumb");
   echo ("<td> $row->City");
   echo ("<td> $row->Floors");
  }
?>

</table>
<P>
<hr>
<P>
</center>
<i>done</i>

Using a form to Add new records

The next example script creates a form to add new records. That form data is processed by this same script.

Before starting the PHP portion of the script, the file contains all the HTML tags that we need to display our table.

The first task of the PHP portion of the script is to connect to the database. That is just cut-and-pasted from the examples above. Note that xxxxx is not really my password.

The while loop inside the script is nearly the same as the above example. The only change in the while loop is that the echo command is done in one line instead of four lines.

After the PHP script is done, the file simply contains an HTML form. The form contains three text inputs and one submit button. To keep everything as simple as possible, I named my form inputs the same as my table fields. Hence, because the warehouse table contains a field named "City", I named the middle text input "City". Likewise for "WhNumb" and "Floors". The action of the form is to call this same script.

Meanwhile, in the middle of the script... After we connect to the database, but before we select and display all records, we need to check if a form is asking to add a new record. So, did the user navigate straight to this web page, or is this page being displayed because the user just hit the "Add Record" button? If the post data contains a value for the input named "City" and/or the other text input fields, then we know the server is calling this script because someone entered a city and hit the submit button. So, if post['city'] is not empty, then we need to do an INSERT before we do the SELECT.

<!-- Database Test 3 -->
<!-- Allows Records to be Added to the Warehouse Table -->
<!-- Contains one form that is also processed by this script -->

<html>
<hr>
<table rules=all border=5>
<tr>
<td bgcolor=black colspan=3 align=center><font color=white>Existing Warehouses
<tr>
<td bgcolor=lightgrey>WhNumb
<td bgcolor=lightgrey>City
<td bgcolor=lightgrey>Floors


<?php
// connect the database

// connect the database
$DBconn = new mysqli ("deltona.birdnest.org", "my.dannellys2", "xyz", "my_dannellys2_default");


if (isset($_POST['City']))
  {
   $WhNumb = $_POST['WhNumb'];
   $City   = $_POST['City'];
   $Floors = $_POST['Floors'];
   $query  = "INSERT INTO warehouse VALUES ('$WhNumb', '$City', $Floors)";
   $result = mysqli_query ($DBconn, $query);
  }

// submit and process the query for existing warehouses
$query = "select * from warehouse;";
$result = mysqli_query ($DBconn, $query);
while ($row = mysqli_fetch_object ($result))
   echo ("<tr> <td> $row->WhNumb <td> $row->City <td> $row->Floors");
?>

</table>
<P>
<hr>
<P>

<form action=dbtest3.php method=post>
<pre>
       New Warehouse Info:
WhNumb <input type=text name="WhNumb">
  City <input type=text name="City">
Floors <input type=text name="Floors">
       <input type=submit value="Add Record">
</pre>
</form>

<P>
<hr>
</html>

Using a form to Delete records

The next expansion to this application is to allow the user to delete particular records. The screen shot below shows the output of my script. To allow a user to delete a record, I added a new column to the table. Each last cell of each row contains an HTML form. In other words, there are five different forms on that screen - four inside the HTML table and one form for inserting at the bottom of the page. Each of the five submit buttons calls the script that generates this page.

But those four tiny forms inside the table don't have any fields to process? How does the script know which record is suppose to be deleted?

The answer is to use a "hidden" form input. In other words, something like
<input type='hidden' name='recid' value='XXX'>
So the recid form field tells the script which record to delete from the database.

Of course, "XXX" is not really the value to use. The value of the hidden input needs to somehow identify the record that is to be deleted.