We've assembled a series of thoughts and examples based upon my own experience using Php 3.0 and MySql. Our goal is that it will aid any beginners in jumping over the common obstacles that hinder progress at the earliest stages of Php/MySql implementation. Connecting to the database. Connecting to the database involves the calling of MySql commands via Php, which are embedded in an "HTML" page. However, instead of having the *.html extension (ex. Mywebpage.html), the php pages would end in the extension *.php3 for Php3.0, or in the extension *.phtml, if you are using Php/FI 2.0. (Note: For the remaining part of this document, the standards for Php 3.0 will be used.)

Before we begin to call commands, however, it is assumed that MySQL and Php have been installed and configured on the server. (If this has not been done, it would be wise to first install the latest versions, or ask your provider if MySQL and Php are available on the server.)

An simple example of the connection process would be:

That's it! You are now connected to the MySql database. Remember that the MySQL database password is usually not the same as the account password. It is also possible that your MySQL database doesn't even have a password. It is strongly suggested that password protection is placed on the database. Without it, it could be fairly (very) easy for someone to enter it by simply executing basic Php commands.

Now What? You will probably want to execute commands on the database once the connection has been made. Let's look at a couple of examples:

Let's use this as the example MySQL table:

Create table rocketships ( model VARCHAR (25), year DATE, seating INT, color VARCHAR (10) );

Insertion of Data Before inserting data into the rocketships table, the table must first be created within the MySQL database. If you are unfamiliar with the necessary steps, consult Section 7.6 of the MySQL documentation.

Let's add a new rocket to the table: (commands would be inserted where one sees the above "#add commands here")

MYSQL($dbName,"INSERT INTO rocketships VALUES('X-Wing', '1998-09-14', '6', 'blue')");

Important: When inserting data into the table, the number of variables inserted into the table must match exactly with the actual number of columns within the table. If it is not exact, the data will not be inserted.

Data Mining Assuming the insertion went okay, let's find that data within the database, inserting the following commands within the script:


$num = mysql_numrows($result); # the actual number of rows held in the array $result.

$model = mysql_result($result,$i,"model"); $color = mysql_result($result,$i,"color"); $seating = mysql_result($result,$i,"seating");

print "The model is called: $model.

"; print "The color is: $color.

"; print "The rocket seats: $seating.

";

?>

Output:

The model is called X-Wing.

The color is blue.

The rocket seats 6.

Note: The above script assumes that there is only one rocket within the table colored blue and seating 6. If there is more than one rocket having these characteristics, the script will display the first rocket found within the table.

Also, note within the select statement that color and seating are NOT variables. Rather, these are the actual names of the columns. Therefore, do not place $ before the names!

Assuming there are a number of rockets having these characteristics, let's find them all and print them all out.


$num = mysql_numrows($result); # the actual number of rows held in the array $result.

$ i = 0; while ($i < $num) :

$model = mysql_result($result,$i,"model"); $color = mysql_result($result,$i,"color"); $seating = mysql_result($result,$i,"seating");

print "Rocket number $num found:

"; print "The model is called: $model.

"; print "The color is: $color.

" print "The rocket seats: $seating.

";

$i++;

endwhile; ?>

Up until now, we have been looking at somewhat static uses of the MySQL database. But what if you want to allow the user to input data? This is the subject of the next section, Forms and MySQL.

Variables: from a form to MySQL Many times, we do not want to statically insert data into a MySql table. This could be easier done using telnet. We might want to use an HTML form to allow a user to insert for example, their name and email address into a MySql table.

Let's use the following table:

Create table addresses (

name VARCHAR (25), email VARCHAR (25), date_inserted DATETIME

);

A user would fill out the above form, entering their name and email address. The name would be placed within the variable $name, and the email would be placed within the variable $email. Upon clicking the submit button, the information would be passed to the Php3 script called thanks.php3.

# thanks.php3


# declare some variables

$site = io.incluso.com; $username = mannymoejack; $password = doodad; $dbName = incluso; mysql_connect($site, $username, $password);

MYSQL($dbName,"INSERT INTO userinfo VALUES('$name', '$address', NULL)");

print "Your data has been added to the database. Please click here to return to ionline.";

mysql_close(); # this closes the database connection

?>

Important Notes:

* Why was the word NULL used, instead of a variable? Because the DATETIME datatype , when used in conjunction with NULL, automatically inserts the current date and time into the database. * Notice the backslashes before the " marks. The " marks are special characters for Php, and must be noted by the backslash, or an error will occur.

Two Quick Tips: 1. Watch the extensions! Sometimes, Internet providers have installed both versions 2.0 and 3.0 of Php on the server, for reasons of convenience to users who have implemented Php 2.0 in the past, and have not yet upgraded to the recent version. Therefore, if you are using commands which were found in the 3.0 manual, make sure the extensions of the pages end in *.php3. Those ending in *.phtml will only use the 2.0 parser, of which many 3.0 commands are not found!

2. The addslashes() and stripslashes() function. Certain characters are considered special within Php, and special consideration must be taken when attempting to pass them as variables within a Php script. For example, if I attempt to insert the following data into a MySQL database: Ciao m'a'r'e' It will seem as if the data has been inserted, as there will be no error. However, that data has not been passed! There are two choices to remedy the situation: 1. Define the MAGIC_QUOTES variable within the configuration file. 2. Use the addslashes() function. Ex. Let's assume the following data has been passed via a form: $property = Alessia's house $where = Italy

Before the Insert statement is called, $property must undergo the addslashes() function. $property = addslashes($property);

effectively rendering $property = Alessia\'s house.

Furthermore, when later calling that data from the database, the stripslashes() function must be called. $property = stripslashes($property); effectively rendering $property to again be Alessia's house.

That's it for now. Next issue, I will continue to look at more complicated aspects of the insert and select commands. Hope to see you there.

F.Y.I: The MySql guide can be found at: http://www.mysql.com

The Php 3.0 guide can be found at: http://www.php.net