Beginner's
Guide To MySQL and PHP
by W.J.
Gilmore
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:
<?
# 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:
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
# 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:
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
# 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:
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
# 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:
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 characterithe
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 characterithe
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 characterithe
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 databstics, 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 databstics, 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 databstics, 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;
$ase. 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;
$ase. 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;
$ase. 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:
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:
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.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:
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:
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!
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:
Define the
MAGIC_QUOTES variable within the configuration file.
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.