Chapter 8 - MySQL and PHP

By: Albert Ritzhaupt

 
 


Chapter Topics

What's a Database

One chapter devoted to database theory, MySQL, and PHP is not sufficient.  In academia, database theory and application will be covered in full courses that span 16-week semesters.  Consequently, this chapter only scratches the surface on databases.  I suggest that anyone that is interested in learning about databases take a course in database theory and application.  

"A database is an organized collection of data (Wikipedia, 2005)."  In today's information age, we use databases for virtually everything ranging from electronic transactions over the Internet to monitoring weather patterns.  The data that is stored in a database can be used for future transactions, reports, analysis, data mining and so forth. When evaluating the definition of a database there are two important parts: organization and data. Organization refers to a predefined structure to store data.

There have been a number of database models developed over the last 60-years.  Arguably the most important database model to be discovered is the relational database model developed by E.F. Codd in 1970 (Wikipedia, 2005).  Relational database management systems, such as MySQL, Oracle, DB2 or Access are all approximations of the mathematical relational model defined by Codd.

back to top

What's SQL

"The Structured Query Language (SQL) is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems (W3C, 2005)."  SQL is a fourth generation programming language used to access and manipulate database systems.  When a programmer plans to use a database to store and retrieve information, he or she must first have some experience with SQL since it is the language that will be used.

SQL can be embedded within PHP programs.  These PHP programs will execute, interact with the database in some way using SQL, and deliver current information to your users.  It is important to note that SQL can be broadly subdivided into two different groups: data manipulation language (DML), and data definition language (DDL).  This chapter will focus only on DML because it is intended to be a programmer's perspective.  Furthermore, we will assume that the database have already been constructed using DDL.

back to top

What's MySQL

MySQL is an implementation of a relational database management system that traditionally runs on Linux and UNIX systems.  It is arguably the world's most popular open source database system and is friendly to the Linux/Apache/PHP environment.  "MySQL is a small, compact database server ideal for small - and not so small - applications(Lycos, 2005)."  If PHP was jelly, MySQL would undoubtedly be peanut butter. To learn more about MySQL, visit their website at http://www.mysql.com.

back to top

Opening a Connection

Just as you opened a connection to a file in the previous chapter for reads and writes, you must first open a connection to the MySQL relational database management system.  Database connections are a little more sophisticated than file connections, however.  For security reasons,  database connections will typically require a username and password combination to authenticate.  The source code below shows the mysql_connect() function in PHP.  In addition to connecting to the database, you must also specify the name of the database you want to use.  It is important to note that if you do not have permission granted to the database, this will likely cause errors, which is why the die() function is called

$username="someUser";
$password="somePassword";
$hostname="localhost";
$database="my_database";
$conn = mysql_connect($hostname, $username, $password);
mysql_select_db($database, $conn) or die( "Unable to select database");

The program above first creates four variables: username, password, hostname, and database.  The username and password are given to you by an administrator.  The hostname is the name of the database server that you would like to connect to.  'localhost' is the database on the current system.  Finally, the database variable refers to the name of the database that you plan to use.  Again, an administrator must provide you access.

back to top

Closing a Connection

Closing a connection to a database is just as important as closing a connection to a file.  It is also just as simple.  Using the source code in the connection example above, to close the connection you simply need to call the mysql_close().  The example source code below.

$username="someUser";
$password="somePassword";
$hostname="localhost";
$database="my_database";
$conn = mysql_connect($hostname, $username, $password);
mysql_select_db($database, $conn) or die( "Unable to select database"); 

...

mysql_close(); 

You can almost use the same setup for all of your database connections.  Put the connection at the top and the close at the bottom.   Everything that you do with the database should appear between the two.

back to top

Select Statement

To facilitate our discussion, we are going to use the same database throughout the upcoming examples.  The example dataset is a fictitious  set of students.  The data definition of the table can be seen in the figure below.   The GPA field is defined as a float, the studentID is defined as an auto increment integer, and the other fields are defined as variable characters in MySQL.

+-----------+-----------+-----------+----------------+------+
| studentID | firstName | lastName | classification | GPA |
+-----------+-----------+-----------+----------------+------+

In this section, we are going to learn about the select statement which is used to retrieve information from a database based on a set of rules, such as criteria.  Our first example is a program that selects all the records in the table.  The source code for the first program can be seen in the figure below.

$username="someUser";
$password="somePassword";
$hostname="localhost";
$database="my_database";
$conn = mysql_connect($hostname, $username, $password);
mysql_select_db($database, $conn) or die( "Unable to select database"); 

$query = "Select * from Student";
$result = mysql_query($query);

echo "<table border=1>";
//captures all the information returned
while ($record = mysql_fetch_array($result) ) {
 //returns an array
 $studentID=$record[0];
 $firstName=$record[1];
 $lastName=$record[2];
 $classification=$record[3];
 $gpa=$record[4];

 echo "<tr>";
 echo "<td>$studentID</td>";
 echo "<td>$firstName</td>";
 echo "<td>$lastName</td>";
 echo "<td>$classification</td>";
 echo "<td>$gpa</td>";
 echo "</tr>";
}
echo "</table>";

mysql_close(); 

This program first connects to the database.  Then it executes a query using the wild card (*) option in SQL, which returns every field in the table.  To execute the query, we use the mysql_query() function, which accepts a query parameter written in SQL.  After the result is returned, we use the mysql_fetch_array() function, which grabs the current record as an array.  The fields are ordered in the same way the table is ordered.  Then we simple print out the values in a table.  For an example of this program, click the link below.

>>Program Example 1

In our next example, we are going to select based on some criteria.  The criteria in this case will be the GPA.  The user will provide a GPA value to an HTML form, press submit, and the result will show all the students with a GPA above the criteria provided.  The source code for the HTML form can be seen in the figure below.

<html>
<form action="example2.php" method="post">
 GPA: <input name="gpa"><br>
 <input type="submit" value="Show">
</form>
</html>

To save space,  the connection and closing portion of the code will not be shown.   The program below is almost identical to the one above with one exception, we use the GPA as a criteria in the select statement.  Notice that the select statement here is enhanced with the 'where' clause that states the criteria of only showing those students with a GPA above the threshold.

$GPAin = $_POST['gpa'];
$query = "Select * from Student where GPA > " . $GPAin;
$result = mysql_query($query);

echo "<table border=1>";
//captures all the information returned
while ($record = mysql_fetch_array($result) ) {
 //returns an array
 $studentID=$record[0];
 $firstName=$record[1];
 $lastName=$record[2];
 $classification=$record[3];
 $gpa=$record[4];

 echo "<tr>";
 echo "<td>$studentID</td>";
 echo "<td>$firstName</td>";
 echo "<td>$lastName</td>";
 echo "<td>$classification</td>";
 echo "<td>$gpa</td>";
 echo "</tr>";
}
echo "</table>";

Play around with the GPA criteria to see that it works.  For an example of this program, click the link below.

>>Program Example 2

In our last select statement example, we will filter on classification to show only those students in a particular classification, such as Seniors.  The user will have the option to select a classification to filter on.  Also, we will only report the students first name, last name, and GPA.  The source code for the HTML form can be seen in the figure below.

<html>
<form action="example3.php" method="post">
 Classification: <select name="classification">
	<option value="Freshman">Freshman 
 <option value="Sophomore">Sophomore 
	<option value="Junior">Junior
 <option value="Senior">Senior
 </select>
 <br>
 <input type="submit" value="Show">
</form>
</html>

To save space,  the connection and closing portion of the code will not be shown.  The program below also contains similar features to the others with two important differences.  The query does not use the wildcard.  It includes the names of the fields in the database.  Also, notice that the single quotes are concatenated around the value of the classification.  This must be done because the fields are defined in MySQL as VARCHAR (variable character).  You must do this for any text formatted field.  

$class = $_POST['classification'];
$query = "Select firstName, lastName, GPA from Student ";
$query = $query . "where classification = '" . $class . "'";
$result = mysql_query($query);

echo "<table border=1>";
//captures all the information returned
while ($record = mysql_fetch_array($result) ) {
 //returns an array
 $firstName=$record[0];
 $lastName=$record[1];
 $gpa=$record[2];

 echo "<tr>";
 echo "<td>$firstName</td>";
 echo "<td>$lastName</td>";
 echo "<td>$gpa</td>";
 echo "</tr>";
}
echo "</table>";

Notice that the record array now indexes the order of the field named in the select statement above.  For an example of this program, click the link below.

>>Program Example 3

back to top

Summarizing Data

SQL select statements  are not limited to just retrieving records.  You can also use SQL to summarize data based on five aggregation functions: min, max, avg, count, and sum.  When summarizing data with SQL, one needs to be mindful of what information they are trying to summarize and why.  For example,  using our students table, it probably would not make sense to some the numeric field GPA.   A more meaningful summary might be max or average.  In this section, we are going to provide two summary examples.

In our first example, we are going to find the average GPA by student classification.  To use a summary function in SQL, you must use the group by clause.  The group by clause groups the records by a field or set of fields for analysis.  In the source code example below, we select classification and the average of GPA grouped by the classification field.

$query = "Select classification, avg(GPA) from Student ";
$query = $query . "group by classification";
$result = mysql_query($query);

echo "<table border=1>";
//captures all the information returned
while ($record = mysql_fetch_array($result) ) {
 //returns an array
 $classification=$record[0];
 $avg_GPA=$record[1];

 echo "<tr>";
 echo "<td>$classification</td>";
 echo "<td>$avg_GPA</td>";
 echo "</tr>";
}
echo "</table>";

To save space,  the connection and closing portion of the code will not be shown. Notice that the avg() function encapsulates the numeric field of interest.  Also, notice the group by clause comes after the 'from' and if it where there, the 'where' clause. For an example of this program, click the link below.

>>Program Example 4

In our next example, we are going to allow the user to identify the minimum GPA or the maximum GPA of students by classification.  An HTML for with two variables is shown in the figure below.

<html>
<form action="example5.php" method="post">
 Classification: <select name="classification">
	<option value="Freshman">Freshman 
 <option value="Sophomore">Sophomore 
	<option value="Junior">Junior
 <option value="Senior">Senior
 </select><br>
 Min or Max: <select name="minmax">
	<option value="min">Minimum 
 <option value="max">Maximum
 </select><br>
 <input type="submit" value="Show">
</form>
</html>

To save space,  the connection and closing portion of the code will not be shown.  As you can see, the HTML form collects the classification and whether it should identify the minimum of maximum.  The program will have to use these two parameters to deliver the desired results.  The source code below captures and utilizes the parameters of interest.

$class = $_POST['classification'];
$minmax = $_POST['minmax'];
$query = "Select $minmax(GPA) from Student ";
$query = $query . "where classification='$class'";
$result = mysql_query($query);

echo "<table border=1>";
//captures all the information returned
while ($record = mysql_fetch_array($result) ) {
 //returns an array
 $GPA=$record[0];

 echo "<tr>"; 
 echo "<td>$GPA</td>";
 echo "</tr>";
}
echo "</table>";

One thing to keep in mind is that the min() or max() functions will potentially lead to precision loss.  For example, a GPA of 3.59 might appear as a longer number, like 3.5899999.  This can be remedied by the use of PHP's formatting functions.  For an example of this program, click the link below.

>>Program Example 5

back to top

Inserting Data

Unfortunately, in the following examples, I will not be able to provide you an interactive program.  I do not want to jeporadize the server I am running this on.  In the next few examples, I will simply show the source code for inserting, updating, and deleting records in a database.  Regardless, the examples will prove useful in your use of PHP.

Inserting records into a database is a simple task, you just need to use the insert statement.  The insert statement follows a simple syntax.  You simply state the table you want to insert into, the name of the fields, and the values of the fields that correspond to the fields listed.  Notice that for numeric fields, you do not need to use single quotes.

$query = "insert into Student (firstName, lastName, ";
$query = $query . "classification, GPA) VALUES ";
$query = $query . "('John', 'Smith', 'Senior', 3.5)";
mysql_query($query);

back to top

Updating Data

Updating information will generally require a parameter to reference a unique item in the database.  The relational database model uses primary keys to uniquely identify records.  One might capture the parameter from an HTML for and use the parameter as a reference to the record of interest.  The example source code below updates a records GPA based on a studentID.

$GPA=$_POST['GPA'];
$studentID=$_POST['studentID'];
$query = "update table Student ";
$query = $query . " set GPA = $GPA ";
$query = $query . " where studentID = $studentID";
mysql_query($query);

back to top

Deleting Data

Deleting records from a database is similar to updating.  You will generally need a parameter that can be used to reference the item you wish to delete.  I suggest using the primary key as the parameter to delete records from your database, or some combination of fields that is a candidate key, a field or combination of fields that could be used to uniquely delete a record.  The example below deletes a record from the table using the primary key of the table - studentID.

$studentID=$_POST['studentID'];
$query = "delete from Student ";
$query = $query . " where studentID = $studentID";
mysql_query($query);

back to top

Your Eighth Program

Since many of you will not have a database to connect to, and since this chapter did not cover DDL, we will not have an assignment in this chapter.   If you are my student in CGS 3559, you will not be able to develop databases because the Osprey server does not provide access to a database connection.

Congratulations! You have completed the eighth chapter. You can now take the online assessment, complete the eighth activity, and move to the next chapter. 

>>Take the Online Quiz

back to top

Activity 8

Create a ficticious program to insert records into the student table.  This will include creating a form to collect the first name, last name, classification, and GPA of the student.  The classification field should be a drop down menu.  The PHP program should error check for all the fields.

back to top

Chapter References 

back to top

Previous section

 

Copyright Albert Dieter Ritzhaupt. All Rights Reserved.