Beginner's Guide To MySQL and PHP |
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:
<? 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:<? $result = mysql($dbName, "SELECT * from rocketships where (color = 'blue' && seating = '6')"); # array holding all rows found within the table $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.<BR><BR>"; print "The color is: $color. <BR><BR>"; print "The rocket seats: $seating. <BR><BR>"; ?> 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. <? $result = mysql($dbName, "SELECT * from rocketships where (color = 'blue' && seating = '6')"); # array holding all rows found within the table $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:<BR><BR>"; print "The model is called: $model. <BR><BR>"; print "The color is: $color. <BR><BR>" print "The rocket seats: $seating. <BR><BR>"; $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 ); <FORM ACTION="thanks.php3" METHOD="POST"< <INPUT TYPE="text" NAME="name" SIZE="25" MAXLENGTH="30" VALUE=""> <INPUT TYPE="text" NAME="email" SIZE="25" MAXLENGTH="30" VALUE=""> <INPUT TYPE="submit" VALUE="default value"> </FORM> 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 <A HREF = \"http://www.314interactive.com/io/\">here</A> to return to ionline."; mysql_close(); # this closes the database connection ?> Important Notes:
|
|
#thelibrarY |