HP color scanner



PHP/MySQL Cheat Sheet

There is a lot to learn about the interaction between PHP and MySQL. But the basic tools can be covered relatively quickly. This handout is designed to be a quick-reference guide for the most important features of PHP and MySQL.

Identifying PHP code

PHP code intermixed with HTML is delimited by the tags . PHP statements end with a semi-colon.

Variables

Variables are simply names used in PHP to store values. They are indicated by a string preceded by a dollar sign. For instance, the following statement assigns the value “CSC111” to the variable $course_number:

$course_number = “CSC111”;

Variables in PHP do not have to be declared before they can be used.

Output

Information is written to the browser window in PHP using the echo or print command. The two commands are interchangeable. HTML tags mixed in with the output to be printed are interpreted appropriately. The period character (“.”) is used to concatenate parts of output strings together. For example:

echo “The sum of ” . $num1 . “ and ” . $num2 . “ is ” . $sum . “.”;

As long as you use double-quotes, you can simplify statements like this by including the variables within the quotes. Then the values of the variable will replace the variables themselves. For instance, the statement:

echo “The sum of $num1 and $num2 is $sum”;

will print

The sum of 3 and 5 is 8

assuming $num1=3, $num2=5 and $sum=8.

Getting Information from HTML Forms

Extracting information from HTML forms is very easy in PHP. If a PHP page is given as the action to be carried out when a form is submitted, that page can access the fields of the form from a special global array simply by using the field names of the form as indices. For instance, if your form has fields called FirstName and LastName, the PHP page that processes the form can access them using $_POST["FirstName"] and $_POST["LastName"], which will contain whatever the user entered into the corresponding fields in the form. (NOTE: This assumes that the POST method was used in the form. Use $_GET if the GET method was used instead. See below for more.)

Getting Information from the URL

Using the GET method rather than the POST method with your form will cause all of the form values to be appended to the end of the URL of the PHP page as a querystring when the form is submitted. For instance:



PHP handles querystrings just as it handles form input sent by the POST method – it simply places the values in a different global array. So, given the URL above, the page cs19.php would have variables $_GET["fName"] and $_GET["lName"] with values “Joe” and “Cool” respectively.

Connecting to a MySQL Database

PHP provides a rich collection of built-in functions for database access. Once again, you basically just have to remember a few simple lines of code to accomplish specific tasks. To connect to a MySQL database, use the functions mysql_connect and mysql_select_db, as in the following example:

$linkID = mysql_connect("localhost","userID","userpass");

mysql_select_db("databaseName", $linkID);

The first line is used essentially to log into your MySQL account on the server. Your connection information is stored in a PHP variable (called “linkID” in this example) The next line makes the connection to a specific database in your account. (Remember that you can have several.) After you’ve worked with your database, you must be sure to close this connection:

mysql_close($linkID);

It is a good idea to include code that will alert you if any of these tasks fail to complete. An easy way to do that is to use the build-in PHP die() function, like this:

$linkID = mysql_connect("localhost","userID","userpass")

or die ("Could not connect: " . mysql_error());

If the connection is successful, this command will “short circuit” and the die() function won’t run at all. If the connection fails, however, execution will stop and a (hopefully) helpful error message will be printed.

Getting Information Out of the Database

This is where you put your knowledge of SQL to use. Once you have an active connection to your database, you can basically do anything you like with it. The key is understanding SQL commands. Recall that searching the database for information to display involves the SELECT command. It also involves the PHP functions mysql_query and mysql_fetch_array. Here’s how it works:

$SQL = “SELECT * FROM tableName WHERE fieldname = ‘foo’”;

$allValues = mysql_query($SQL, $linkID);

The function mysql_query selects the requested records from the database and stores them in the variable $allValues in something called a resource, which is basically a two-dimensional array.

It is good PHP programming practice to check to make sure a query was successful. This can be done with the following code:

if (!$allValues) {

echo “Could not execute query ($SQL): “ . mysql_error();

exit;

}

The die() function could also be used here, as in the previous example.

If the query was unsuccessful, the variable $allValues will contain the value FALSE. So this will print an error message, the original query, and an explanation of the error in the event of a failed query. It will then terminate the application.

If the query is successful, there is a default pointer that at any given moment is pointing to one of the retrieved records in the resource (initially the first one). Consider the following line of code:

$thisValue = mysql_fetch_array($allValues)

The function mysql_fetch_array grabs a single row of data from the $allValues variable and stores it in $thisValue. Now you can access the data from the record just by using the original field names. For instance:

echo $thisValue[“field1”];

echo $thisValue[“field2”];

The nice thing about the function mysql_fetch_array is that it automatically moves the pointer to the next record. It also returns a TRUE/FALSE value that lets you know when it has reached the end of the data. This is useful for looping through data. For instance:

$allValues = mysql_query(“SELECT * FROM Grades”, $linkID);

while ($thisValue = mysql_fetch_array($allValues))

{

echo $thisValue[“field1”] . “ ” . $thisValue[“field2”];

echo “”;

}

There are other useful alternatives to mysql_fetch_array. I encourage you to look up the details of mysql_fetch_row and mysql_fetch_object, for instance.

An alternative that I frequently use is mysql_fetch_assoc. It works like this:

while ($thisValue = mysql_fetch_assoc($allValues))

{

extract($thisValue);

print "\n";

print “$field1$field2”;

print "\n";

}

The extract function pulls the values of each row out into variables named after the actual field names. Try this alternate version if you like.

Changing/Updating the Database

Once you’ve got SELECT down, this is easy. To add to, delete from, or update a record in the database, just use the appropriate SQL command as discussed in class, or covered in the tutorials (from the class web site). For example:

mysql_query(“DELETE FROM tableName WHERE fieldName = ‘foo’”, $linkID);

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download