PHP & MYSQL

This handout is based on the book “How to do Everything with PHP & MySQL” by Vikram Wasvani, from McGraw Hill/Osbourne publications and the online PHP&MySQL tutorial by Kevin Yank which is available at:
http://dev.mysql.com/tech-resources/articles/ddws/.
You can buy the book from Amazon or Barnes & Noble.

In the previous labs, we have seen how PHP and MySQL work separately. Now, it is time to use them together to build database driven web applications. In this lab, we will learn how PHP can be used to access MySQL server and perform queries.

First, we need to connect to the MySQL server and select a database. We use the mysql_connect() function for this purpose (If you use @mysql_connect() instead, PHP will not display error messages produced by the MySQL server). To select the database we want to work with, we use the function mysql_select_db():


$db_host = "localhost";
$db_username = "ie215";
$db_passwd = "password";
$db= @mysql_connect($db_host, $db_username, $db_passwd) or die ("Could not connect!\n");


echo "Connection established.\n";
$db_name = "ie215";


@mysql_select_db("$db_name") or die ("Could not select the database $dbname!\n");

echo "Database $db_name is selected.\n";


Save this file as connect.php and view the web page at: https://inst.decf.berkeley.edu/~ie215/connect.php

If the connection is established and the database is selected successfully you will see the message below in your web browser:

Connection established. Database ie215 is selected.

If one of the functions fails to execute PHP returns an error message, in this case “Could not connect!” or “Could not select the database ie215!”, and stops executing the rest of the code.

Note that you should use your own username instead of ie215. For instance, if your DECF username is ie215-23, then your MySQL username is ie215_23 (with an underscore not a hyphen) and the name of your database is ie215_23. Your DECF account password and MySQL password work independently, so even if you have changed your DECF password, your MySQL password stays the same.

So far, we established a connection with the MySQL server and chose a database to work with. Next, we can execute SQL commands to create tables, insert records and run queries. We use the mysql_query() function for this purpose. Let’s start with creating tables:

$sql = "CREATE TABLE Jokes ( " .
"ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " .
"JokeText TEXT, " .
"JokeDate DATE NOT NULL " .
")";
if ( mysql_query($sql) ) {
echo("

Jokes table successfully created!

");
} else {
echo("

Error creating Jokes table: " .
mysql_error() . "

");

First, we store the CREATE TABLE command in the variable $sql and then execute it using the mysql_query() function. This function also returns a Boolean result which can be used inside a conditional statement to display appropriate messages.

Before executing the code above, don’t forget that PHP needs to connect to the MySQL server and select a database. You should see the following message on your screen after executing the code above:

Connection established. Database ie215 selected.
Jokes table successfully created!

The use of DELETE, INSERT and UPDATE commands are similar:

$sql = "UPDATE Jokes SET JokeDate='1990-04-01' " .
"WHERE JokeText LIKE '%chicken%'";
if ( mysql_query($sql) ) {
echo("

Update affected " .
mysql_affected_rows() . " rows.

");
} else {
echo("

Error performing update: " .
mysql_error() . "

");
}

For DELETE, INSERT and UPDATE commands, MySQL also keeps track of the number of rows that are affected by the operation. The function mysql_affected_rows() is used to retrieve the number of rows affected.

The SELECT queries differ from the operations above since the SELECT queries usually return more complex results. As in most of the functions in PHP, if the processing of a SELECT query fails for some reason, the function mysql_query returns a ‘false’ value. This output can be used to display error messages:

$result = mysql_query("SELECT JokeText FROM Jokes");
if (!$result) {
echo("

Error performing query: " .
mysql_error() . "

");
exit();
}

If the query fails, $result will be equal to ‘false’, thus !$result will be true and the error message will be displayed. Notice that we used the function mysql_error() to retrieve the error message given by the MySQL server.

If the query is executed successfully, we can use the mysql_fetch_array() function to handle the result.

while ( $row = mysql_fetch_array($result) ) {
// process the row...
…………………………………………………………
…………………………………………………………
}

The function mysql_fetch_array() returns the rows in the result one at a time until the last row. Then it returns a ‘false’ value, so that the ‘while’ loop is terminated. Using this loop we can print the rows in the result inside a table:

Echo ‘’;
Echo ‘’;

while ( $row = mysql_fetch_array($result) ) {
echo ‘’;
echo ‘’;
}
Echo ‘

ID	Joke Text	Date
’ .$row[0]. ‘	’; echo ‘	’ .$row[1]. ‘	’; echo ‘	’ .$row[2]. ‘	’; echo ‘

’;

Here, $row[i] stands for the i-th column in the result.

We can also let the visitors insert records into the tables in the database. We can use the following form to get visitor input:

Type your joke here:

This form stores the visitor input in the variable $joketext and sends it to insert.php which looks like:


$db_host = "localhost";
$db_username = "ie215";
$db_passwd = "password";
$db= @mysql_connect($db_host, $db_username, $db_passwd) or die ("Could not connect!\n");


echo "Connection established.\n";
$db_name = "ie215";


@mysql_select_db("$db_name") or die ("Could not select the database.\n $dbname");

echo "Database $db_name selected.\n";


$sql = "INSERT INTO Jokes SET " .
"JokeText='$joketext', " .
"JokeDate=CURDATE()";
if (mysql_query($sql)) {
echo("

Your joke has been added.

");
} else {
echo("

Error adding submitted joke: " .
mysql_error() . "

"); }

?>

The html file containing the form will display the following web page:


Once the visitor submits a joke, it will be stored in the variable $joketext and insert.php will add this record to the table JOKES and display:

Connection established. Database ie215 selected.
Your joke has been added.



Useful Links:

• PHP’s MySQL support:
http://www.php.net/manual/en/ref.mysql.php
• More examples:
http://www.melonfire.com/community/columns/trog/article.php?id=18
• Kevin Yank’s online PHP & MySQL tutorial:
http://dev.mysql.com/tech-resources/articles/ddws

No Quiz!
• PHP and MySQL Project Demo: Select one or two of the relations from your Project and design a web-based interactive query using it. In MySQL, design the table(s) and insert 10 tuples of sample data. Design a web page with embedded PHP that will allow online visitors to enter data and execute a query on this data. For example, consider a 'Product' table with prices. Your page might allow a visitor to enter a maximum limit price in a form, and your solution would return all products with price under than that maximum. Write a short summary of your design, including the MySQL table description and the embedded PHP sections, and include the URL of the web page. Submit this in hardcopy on the due date.