PHP3 Introduction - MySQL database interfacing
( Page 4 of 4 )

In order to perform these functions, MySQL must be installed and configured on the server.
Basically, PHP acts as an intermediate between the database server and the web browser, communicating commands from one to another. This communication greatly increases the possibility of interactivity, be it in the form of polls, user-personalized sites, online submission systems, or any other activity requiring user input.

This interaction starts by making a connection to the MySQL database. This connection is accomplished using the following command:
Syntax: int mysql_connect(string hostname, string username, string password);

• hostname - the hostname of the database. (i.e. Devshed's would most likely be: www.devshed.com
• username - the username set to connect to the MySQL database.
• Password - the password set to connect to the MySQL database.
• The int returned is positive if the connection is successful, negative if unsuccessful.

All commands must, as always, be placed within the "<?" and "?>" brackets.

Returning to the user information project, let's suppose the following table has been created within the MySQL database:

mysql> CREATE TABLE information (
    -> name VARCHAR (25),
    -> email VARCHAR (25),
    -> choice VARCHAR (8) );

Now, assume we want to insert the user's information into the MySQL database. This can be accomplished by modifying the email.php3 script to be the following:

<?
/* this script will handle the variables passed from the moreinfo.html file */

/* declare some relevant variables */

$hostname = "devshed";
$username = "myusername";
$password = "mypassword";
$dbName = "mydbname";

/* MySQL table created to store the data */
$userstable = "information";

/* the site administrator's email address */
$adminaddress = "administration@buycorn.com";

/* make connection to database */
MYSQL_CONNECT($hostname,$username,$password) 
           OR DIE("Unable to connect to database");

@mysql_select_db("$dbName") or 
            die("Unable to select database"); 

PRINT "<CENTER>";
PRINT "Hello, $name.";
PRINT "<BR><BR>";
PRINT "Thank you for your interest.<BR><BR>";
PRINT "We will send information to $email,
                and have noted that you like $preference";
PRINT "</CENTER><BR><BR>";

/* Send relevant emails */
mail("$email", "Your request for information",
"$name\nThank you for your interest!\n
We sell fresh corn daily over the Internet!
Place your order  at http://www.buycorn.com,
and receive a free package of $preference!");

mail("$adminaddress",
"Visitor request for info.",
"$name requested for information.\n

The email address is $email. \n The visitor prefers $preference.");

/* Insert information into table */
$query = "INSERT INTO $userstable VALUES('$name','$email', '$preference')";

$result = MYSQL_QUERY($query);

PRINT "Your information has also been inserted into our database.";

/* Close the database connection */
MYSQL_CLOSE();
?>

Some notes:

1. The variables declared at the beginning of the script are for the MYSQL_CONNECT() function. We also could have plugged the values directly into the function. However, as projects begin to grow in size, these values will probably be placed in a separate file and included (using the #include statement) within the file. Thus making for easy modification should any information need to be edited.
2. The @mysql_select_db() function selects a database. Doing this saves some time later in the script, allowing one to execute query statements without having to nominate a database name.
   Syntax: int mysql_select_db(string database_name, int link_identifier);
   * database_name must be one of the database names appearing on the server.
   * link_identifier (optional) refers to the link to the database, for reason of making a connection. If one is not designated, the last opened link is used.
   * returns a true/false value, based on success.
3. The MYSQL_QUERY() function is then used to send queries to the MySQL database.
   Syntax: int mysql_query(string query, int link_identifier);
   * query - the query that you would like to send to the database.
   * link_identifier - the database name. (optional. If not included, the query will use the most recently opened database) Or, if one chooses no to use the @mysql_select_db() function, one must nominate a database name.
   * Returns an integer. Positive if successful, negative if unsuccessful.
4. The MYSQL_CLOSE statement closes the connection to the MySQL database. In the following syntax, the link_identifier refers to a database name. If it is not specified, it will close the last opened database.
   Syntax: int mysql_close(int link_identifier);
   * link_identifier - same as above.
   * Returns an integer. Positive if successful, negative if unsuccessful.

View the script.


If you have actually set this up on your server and executed it, you will now see that the information has in fact been added to the information table. In the next section, we will learn how to extract this data from the MySQL database, and display it onto the screen.
{mospagebreak title=Outputting data from MySQL} Ok, we have had great success with our request for user information, and a considerable amount of information is now stored within the database. Yet, how does one go about searching through this data, and returning useful results from it? This is accomplished through the execution of three steps:

1. Request for data fitting the required restraints.
2. Tally of all results fitting restraints.
3. Output of data fitting restraints.

We are interested in outputting to the browser all users that prefer Apples to Oranges.

<?
/* script to output to screen all users preferring Apples to Oranges */

/* declare some relevant variables */
$hostname = "devshed";
$username = "myusername";
$password = "mypassword";
$userstable = "information";
$dbName = "mydbname";

/* make connection to database */
MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");

@mysql_select_db( "$dbName") or die( "Unable to select database"); 

/* Select all users with the preference Apples */
$query = "SELECT * FROM $userstable WHERE choice = 'Apples'";

$result = MYSQL_QUERY($query);

/* How many of these users are there? */
$number = MYSQL_NUMROWS($result);

/* Print these results to the screen in a nice format */
$i = 0;

IF ($number == 0) :
	PRINT "<CENTER><P>Nobody in the database prefers Apples!</CENTER>";
ELSEIF ($number > 0) :
	PRINT "<CENTER><P>Users preferring Apples: $number<BR><BR>";
	WHILE ($i < $number):
		$name = mysql_result($result,$i,"name");
		$email = mysql_result($result,$i,"email");
		PRINT "Visitor $name likes Apples.<BR>";
		PRINT "Email address: $email.";
		PRINT "<BR><BR>";
		$i++;
	ENDWHILE;
		PRINT "</CENTER>";
ENDIF;
?>

Save this as apples.php3.

Explanation of new functions:

$number = MYSQL_NUMROWS($result);

Syntax: int mysql_num_rows(string result);
* result - the array of records returned from the MYSQL_QUERY function.
* returns the numerical value of the number of rows that have been stored within $result.

Finally, we output the results of this search. If there is nobody within the database that likes Apples, output will ensue stating so; If there are users within the database preferring Apples, each user name and email will be duly outputted. This output takes place through a WHILE statement.

$name = MYSQL_RESULT($result,$i,"name");

Syntax: int mysql_result(int result, int i, column);
The mysql_result() function breaks apart each record, placing each piece into a variable. In short,
* $result refers to the array in question
* $i refers to the particular count that we have arrived.
* column is the actual name of the column within the MySQL table. Can also be the offset value, or the field table dot field name (i.e. fieldname.tablename).

Thus, using a simple WHILE loop, we are capable to easily output data to the browser.

View the script.

Other functions:

Using the MYSQL_QUERY() function, one is able to execute a number of SQL functions. Among these include the DELETE and UPDATE functions.

Delete
Let's assume we want to delete all records containing the name "Bunny":

$query = "DELETE FROM $userstable WHERE name = "Bunny";
MYSQL_QUERY($query);

Update
Or perhaps we want to modify all records containing the name "Bunny":

$query = "UPDATE $userstable SET name = "Bugs Bunny" WHERE name = "Bunny";
MYSQL_QUERY($query);

In this article, we have gotten quite a start learning how to incorporate PHP3.0 into web site development. We have learned how to create dynamic pages, as well as how to use PHP3.0 to tie a MySQL database to the web. However, this is but a small piece of what PHP3.0 is capable of.

Perhaps the most wise advice I can give in reference to PHP3.0 is to read; Rather memorize, the documentation. Written by PHP's authors, there is no other resource available that can sufficiently demonstrate the capabilities of PHP as well.

The documentation, as well as the latest news and other resources about PHP3.0 is readily available at http://www.php.net

MySQL documentation, resources, and news are available at http://www.mysql.com

In addition, become an active member of the PHP mailing list. Simply by reading some of the questions/answers posted each day, you will quickly become very familiar with the intricacies of the language.

>>> More PHP Articles          >>> More By W.J. Gilmore