Beginnerz Guide To MySQL
Beginnerz Guide To MySQL
Beginnerz Guide To MySQL
htm
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.)
<?
# declare some variables
$site = io.incluso.com;
$username = mannymoejack;
$password = doodad;
$dbName = incluso;
# end variable declaration
mysql_connect($site, $username, $password);
?>
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:
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")
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:
1 of 4 1/22/2010 9:50 PM
Beginnerz Guide To MySQL and PHP file:///D:/Zfiles/Beginners_Guide_To_MySQL_and_PHP.htm
<?
$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");
?>
Output:
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");
$i++;
2 of 4 1/22/2010 9:50 PM
Beginnerz Guide To MySQL and PHP file:///D:/Zfiles/Beginners_Guide_To_MySQL_and_PHP.htm
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.
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.
);
</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
<?
$site = io.incluso.com;
$username = mannymoejack;
$password = doodad;
$dbName = incluso;
mysql_connect($site, $username, $password);
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:
3 of 4 1/22/2010 9:50 PM
Beginnerz Guide To MySQL and PHP file:///D:/Zfiles/Beginners_Guide_To_MySQL_and_PHP.htm
Before the Insert statement is called, $property must undergo the addslashes() function.
$property = addslashes($property);
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:
4 of 4 1/22/2010 9:50 PM