Database link
10. The biggest feature of PHP is its particularly powerful ability to operate databases, and PHP provides support for a variety of databases.
With PHP you can easily connect to the database, request data and display it in your web site, or even modify the data in the database. In this section, we mainly take the MySQL database that is most used with PHP on the Internet as an example, and introduce the relevant MySQL database operation functions and basic database operations.
In MySQL database, there are two functions we use to connect to the database, and they are:
integer mysql_connect(string host,string user,string password);
integer mysql_pconnect(string host,string user,string password);
Both the mysql_connect function and the mysql_pconnect function are connections to the MySQL database on the specified host. If the database is located on a different port, you can add a colon and a port number after the host name. The parameters of the function can also be not filled by default. If the parameters are not filled in, the default host name is "localhost", the user name is database administrator, the default value is "root", and the password is empty. After a successful connection to the database, both functions can return a connection number, and if the connection fails, a false value will be returned. Let's take a look at the following sentences:
<?
$db=mysql_connect("localhost","user","password");
mysql_select_db("mydb",$db);
?>
Notes:
$db=mysql_connect("localhost","user","password"); We use the link parameters of mysql, including the host name, user name and password, as the parameters of mysql_connect(), and at the same time get the return value of $db. In this way, in the following statement, we can use the variable $db as a connection number connecting to the mysql database.
mysql_select_db("mydb",$db); Link the PHP program to the mydb database, so that the link between the program and the database is completed.
10.1 A simple database message book
After completing the link to the database, we can perform a series of operations on the database. Here is a simple database guestbook program (guestbook.php3):
I assume that the MySQL database on your machine and the tool Phpmyadmin_2.0.5, which manages the MYSQL database, have been installed and will work properly.
The first thing we need to do is create a message database, assume the name is: mydb.
1. Start the browser and open the management WEB interface of Phpmyadmin_2. 0.5.
2. Enter the database name mydb in the "Create new database" text box, and then press the create key.
Next, we want to create a data table under the message database, assuming the name is: guestbook.
The command to create this data table is as follows:
CREATE TABLE guestbook (ID INT NOT NULL AUTO_INCREMENT, name CHAR(250), email CHAR(250), job CHAR(250), comments BLOB, PRIMARY KEY(ID));
Finally, put the following guestbook program into the writable directory of your machine and save it into a guestbook.php3 file. It's that simple, you already have your own guestbook.
10.2 Guestbook program (guestbook.php3):
<?php
/* $host : your MySQL-host, usually 'localhost' */
/* $user : your MYSQL-username */
/* $password : your MySQL-password */
/* $database : your MySQL-database */
/* $table : your MySQL-table */
/* $page_title : the title of your guestbook-pages */
/* $admin_mail : email-address of the administrator to send the new entries to */
/* $admin_name : the name of the administrator */
/* $html_mail : say yes if your mail-agent can handle HTML-mail, else say no */
$host = "localhost";
$user = "";
$password = "";
$database = "mydb";
$table = "guestbook";
$page_title = "pert guestbook";
$admin_mail = "pert@";
$admin_name = "Webmaster";
$html_mail = "no";
?>
<HTML>
<HEAD>
<TITLE><?php echo $page_title; ?></TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" LINK="#000000">
<FONT FACE="Verdana" SIZE="-2">
<?
/* connect to the database */
mysql_pconnect("$host","$user","$password") or die("Can't connect to the SQL-server");
mysql_select_db("$database");
/* action=view : retrieve data from the database and show it to the user */
if($action == "view") {
/* function for showing the data */
function search_it($name) {
/* some vars */
global $offset,$total,$lpp,$dir;
global $table,$html_mail,$admin_name,$admin_mail;
/* select the data to get out of the database */
$query = "SELECT name, email, job, comments FROM $table";
$result = mysql_query($query);
$total= mysql_numrows($result);
print "<CENTER><FONT FACE="Verdana" SIZE="-2"><A HREF="guestbook.php3?action=add" onMouseOver="='Add your name';return true" onMouseOut="='';return true" TITLE="Add your name">Add your name">Add your name</A></FONT></CENTER><br><br>";
if ($total== 0) {
print "<CENTER>No one left a message at this moment</CENTER><br><br>"; }
elseif ($total> 0) {
/* default */
$counter=0;
if ($dir=="") $dir="Next";
$lpp=5;
if ($offset==0) $offset=0;
if ($dir=="Next") {
if ($total > $lpp) {
$counter=$offset;
$offset+=$lpp;
$num=$offset;
if ($num > $total) {
$num=$total; } }
else {
$num=$total; } }
elseif ($dir=="Previous") {
if ($total > $lpp) {
$offset-=$lpp;
if ($offset < 0) {
$offset=0; }
$counter=$offset-$lpp;
if ($counter < 0)
$counter=0;
$num=$counter+$lpp; }
else {
$num=$total; } }
while ($counter < $num) {
$j=0;
$j=$counter + 1;
/* now really grab the data */
$i1=mysql_result($result,$counter,"name");
$i2=mysql_result($result,$counter,"email");
$i3=mysql_result($result,$counter,"job");
$i4=mysql_result($result,$counter,"comments");
$i4 = stripslashes ("$i4");
/* print it in a nice layout */
print "<CENTER>n";
print "<TABLE WIDTH=400 BORDER=0 ALIGN=CENTER VALIGN=TOP><TR><TD><FONT FACE="Verdana" SIZE="-2">n";
print "<HR>n";
print "<BR><B>Name:</B> $i1n";
print "<BR><B>email:</B><A HREF="mailto:$i2" onMouseOver="='Email $i2';return true" onMouseOut="='';return true" TITLE="Email $i2">$i2</A>n";
print "<BR><B>Job:</B> $i3n";
print "<BR><B>Comment:</B>n";
print "<BR>$i4n";
print "</FONT></TD></TR></TABLE>n";
print "</CENTER>n";
$counter++;
}
}
mysql_close();
}
/* execute the function */
search_it($name);
/* See if we need to put on the NEXT or PREVIOUS buttons */
if ($total > $lpp) {
echo("<form action="$PHP_SCRIPT" method="POST">n");
/* See if we need a PREVIOUS button */
if ($offset > $lpp) {
echo("<input type="submit" value="Previous" name=dir>n"); }
/* See if we need a NEXT button */
if ($offset < $total) {
echo("<input type="submit" value="Next" name=dir>n"); }
echo("<input type=hidden name="offset" value="$offset">n");
echo("<input type=hidden name="name" value="$name">n");
echo("</form>");
}
}
/* action=add : show a form where the user can enter data to add to the database */
elseif($action == "add") { ?>
<TABLE WIDTH="460" ALIGN="CENTER" VALIGN="TOP">
<TH COLSPAN="2"><P>Please fill in a message</TH>
<FORM NAME="guestbook" ACTION="guestbook.php3?action=send" METHOD="POST">
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your name:</TD>
<TD><INPUT TYPE=text NAME=name></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your E-mail:</TD>
<TD>
<INPUT TYPE=text NAME=email></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your job:</TD>
<TD>
<INPUT TYPE=text NAME=job></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your message:</TD>
<TD>
<TEXTAREA NAME=comments COLS=40 ROWS=6></TEXTAREA>
<P>
<INPUT TYPE=submit VALUE=Submit> <INPUT TYPE=Reset VALUE=Reset>
<A ALIGN="RIGHT" HREF="guestbook.php3?action=view" onMouseOver="='Read all comments first';return true" onMouseOut="='';return true" TITLE="Read all comments first"><FONT SIZE="-2">View all comments first</FONT></A>
</TD>
</TR>
</FORM>
</TABLE>
</CENTER>
<?
}
/* action=send : add the data from the user into the database */
elseif($action == "send") {
/* check if a HTML-mail should be send or a plain/text mail */
if($html_mail == "yes") {
mail("$admin_name <$admin_mail>","PHP3 Guestbook Addition","<HTML><BODY><FONT FACE="Century Gothic"><TABLE BORDER="0" WIDTH="100%" CELLSPACING="4"><TR>$name ($email) schreef het volgende bericht in het gastenboek :</TR><TR><TD ALIGN="LEFT"> </TD><TD ALIGN="LEFT" NOWRAP> </TD></TR><TR><TD ALIGN="LEFT">$comments</TD><TD ALIGN="LEFT" NOWRAP> </TD></TR><TR><TD ALIGN="LEFT"> </TD></TR><TR><TD ALIGN="LEFT" NOWRAP> </TD></TR><TR><TD ALIGN="LEFT">Your message:</TD><TD ALIGN="LEFT" NOWRAP>$name</TD></TR><TR><TD ALIGN="LEFT">Your name:</TD><TD ALIGN="LEFT" NOWRAP>$email</TD></TR><TR><TD ALIGN="LEFT">Your email:</TD><TD ALIGN="LEFT" NOWRAP>$job</TD></TR><TR><TD ALIGN="LEFT">Your job:</TD></TR></TABLE></BODY></FONT></HTML>", "From: $name <$email>nReply-To: $name <$email>nContent-type: text/htmlnX-Mailer: PHP/" . phpversion());
}
/* MySQL really hates it when you try to put things with ' or " characters into a database, so strip these...*/
$comments = addslashes ("$comments");
$query = "INSERT INTO guestbook VALUES('','$name', '$email', '$job', '$comments')";
$result = MYSQL_QUERY($query);
?>
<BR><P ALIGN = CENTER>Thanks, <?php echo $name; ?>, your message.
<BR><P ALIGN = CENTER><A HREF="guestbook.php3?action=view" onMouseOver="='View your comment now';return true" onMouseOut="='';return true" TITLE="View your comment now">Watch the message</A><BR><BR>
<?
}
/* if there's no action given, then we must show the main page */
else {
/* get the number of entries written into the guestbook*/
$query = "SELECT name from guestbook";
$result = MYSQL_QUERY($query);
$number = MYSQL_NUMROWS($result);
if ($number == "") {
$entry = "No one left a comment yet"; }
elseif ($number == "1") {
$entry = "1 current number of comments"; }
else {
$entry = "Current number of comments $number"; }
echo "<CENTER><BR>";
echo "<P>$entry<BR>";
echo "<H4><FONT FACE="Verdana" SIZE="3"><A HREF="guestbook.php3?action=add" onMouseOver="='Please leave a message';return true" onMouseOut="=';return true" TITLE="Add your name to our guestbook">Please leave a message</A></FONT></H4>";
if ($number > "") {
echo "<H4><FONT FACE="Verdana" SIZE="3"><A HREF="guestbook.php3?action=view" onMouseOver="='Watch Message';return true" onMouseOut="='';return true" TITLE="View the names in our guestbook">Watch Message</A></FONT></H4>"; }
echo "</P></CENTER>";
}
?>
<BR><SMALL><CENTER>Copyright: <A HREF="http://personal./haitang/" onMouseOver="='pert';return true" onMouseOut="='';return true" TITLE="pert">Boundless Sky</A></CENTER></SMALL>
</FONT>
</BODY>
</HTML>
10. The biggest feature of PHP is its particularly powerful ability to operate databases, and PHP provides support for a variety of databases.
With PHP you can easily connect to the database, request data and display it in your web site, or even modify the data in the database. In this section, we mainly take the MySQL database that is most used with PHP on the Internet as an example, and introduce the relevant MySQL database operation functions and basic database operations.
In MySQL database, there are two functions we use to connect to the database, and they are:
integer mysql_connect(string host,string user,string password);
integer mysql_pconnect(string host,string user,string password);
Both the mysql_connect function and the mysql_pconnect function are connections to the MySQL database on the specified host. If the database is located on a different port, you can add a colon and a port number after the host name. The parameters of the function can also be not filled by default. If the parameters are not filled in, the default host name is "localhost", the user name is database administrator, the default value is "root", and the password is empty. After a successful connection to the database, both functions can return a connection number, and if the connection fails, a false value will be returned. Let's take a look at the following sentences:
<?
$db=mysql_connect("localhost","user","password");
mysql_select_db("mydb",$db);
?>
Notes:
$db=mysql_connect("localhost","user","password"); We use the link parameters of mysql, including the host name, user name and password, as the parameters of mysql_connect(), and at the same time get the return value of $db. In this way, in the following statement, we can use the variable $db as a connection number connecting to the mysql database.
mysql_select_db("mydb",$db); Link the PHP program to the mydb database, so that the link between the program and the database is completed.
10.1 A simple database message book
After completing the link to the database, we can perform a series of operations on the database. Here is a simple database guestbook program (guestbook.php3):
I assume that the MySQL database on your machine and the tool Phpmyadmin_2.0.5, which manages the MYSQL database, have been installed and will work properly.
The first thing we need to do is create a message database, assume the name is: mydb.
1. Start the browser and open the management WEB interface of Phpmyadmin_2. 0.5.
2. Enter the database name mydb in the "Create new database" text box, and then press the create key.
Next, we want to create a data table under the message database, assuming the name is: guestbook.
The command to create this data table is as follows:
CREATE TABLE guestbook (ID INT NOT NULL AUTO_INCREMENT, name CHAR(250), email CHAR(250), job CHAR(250), comments BLOB, PRIMARY KEY(ID));
Finally, put the following guestbook program into the writable directory of your machine and save it into a guestbook.php3 file. It's that simple, you already have your own guestbook.
10.2 Guestbook program (guestbook.php3):
<?php
/* $host : your MySQL-host, usually 'localhost' */
/* $user : your MYSQL-username */
/* $password : your MySQL-password */
/* $database : your MySQL-database */
/* $table : your MySQL-table */
/* $page_title : the title of your guestbook-pages */
/* $admin_mail : email-address of the administrator to send the new entries to */
/* $admin_name : the name of the administrator */
/* $html_mail : say yes if your mail-agent can handle HTML-mail, else say no */
$host = "localhost";
$user = "";
$password = "";
$database = "mydb";
$table = "guestbook";
$page_title = "pert guestbook";
$admin_mail = "pert@";
$admin_name = "Webmaster";
$html_mail = "no";
?>
<HTML>
<HEAD>
<TITLE><?php echo $page_title; ?></TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" LINK="#000000">
<FONT FACE="Verdana" SIZE="-2">
<?
/* connect to the database */
mysql_pconnect("$host","$user","$password") or die("Can't connect to the SQL-server");
mysql_select_db("$database");
/* action=view : retrieve data from the database and show it to the user */
if($action == "view") {
/* function for showing the data */
function search_it($name) {
/* some vars */
global $offset,$total,$lpp,$dir;
global $table,$html_mail,$admin_name,$admin_mail;
/* select the data to get out of the database */
$query = "SELECT name, email, job, comments FROM $table";
$result = mysql_query($query);
$total= mysql_numrows($result);
print "<CENTER><FONT FACE="Verdana" SIZE="-2"><A HREF="guestbook.php3?action=add" onMouseOver="='Add your name';return true" onMouseOut="='';return true" TITLE="Add your name">Add your name">Add your name</A></FONT></CENTER><br><br>";
if ($total== 0) {
print "<CENTER>No one left a message at this moment</CENTER><br><br>"; }
elseif ($total> 0) {
/* default */
$counter=0;
if ($dir=="") $dir="Next";
$lpp=5;
if ($offset==0) $offset=0;
if ($dir=="Next") {
if ($total > $lpp) {
$counter=$offset;
$offset+=$lpp;
$num=$offset;
if ($num > $total) {
$num=$total; } }
else {
$num=$total; } }
elseif ($dir=="Previous") {
if ($total > $lpp) {
$offset-=$lpp;
if ($offset < 0) {
$offset=0; }
$counter=$offset-$lpp;
if ($counter < 0)
$counter=0;
$num=$counter+$lpp; }
else {
$num=$total; } }
while ($counter < $num) {
$j=0;
$j=$counter + 1;
/* now really grab the data */
$i1=mysql_result($result,$counter,"name");
$i2=mysql_result($result,$counter,"email");
$i3=mysql_result($result,$counter,"job");
$i4=mysql_result($result,$counter,"comments");
$i4 = stripslashes ("$i4");
/* print it in a nice layout */
print "<CENTER>n";
print "<TABLE WIDTH=400 BORDER=0 ALIGN=CENTER VALIGN=TOP><TR><TD><FONT FACE="Verdana" SIZE="-2">n";
print "<HR>n";
print "<BR><B>Name:</B> $i1n";
print "<BR><B>email:</B><A HREF="mailto:$i2" onMouseOver="='Email $i2';return true" onMouseOut="='';return true" TITLE="Email $i2">$i2</A>n";
print "<BR><B>Job:</B> $i3n";
print "<BR><B>Comment:</B>n";
print "<BR>$i4n";
print "</FONT></TD></TR></TABLE>n";
print "</CENTER>n";
$counter++;
}
}
mysql_close();
}
/* execute the function */
search_it($name);
/* See if we need to put on the NEXT or PREVIOUS buttons */
if ($total > $lpp) {
echo("<form action="$PHP_SCRIPT" method="POST">n");
/* See if we need a PREVIOUS button */
if ($offset > $lpp) {
echo("<input type="submit" value="Previous" name=dir>n"); }
/* See if we need a NEXT button */
if ($offset < $total) {
echo("<input type="submit" value="Next" name=dir>n"); }
echo("<input type=hidden name="offset" value="$offset">n");
echo("<input type=hidden name="name" value="$name">n");
echo("</form>");
}
}
/* action=add : show a form where the user can enter data to add to the database */
elseif($action == "add") { ?>
<TABLE WIDTH="460" ALIGN="CENTER" VALIGN="TOP">
<TH COLSPAN="2"><P>Please fill in a message</TH>
<FORM NAME="guestbook" ACTION="guestbook.php3?action=send" METHOD="POST">
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your name:</TD>
<TD><INPUT TYPE=text NAME=name></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your E-mail:</TD>
<TD>
<INPUT TYPE=text NAME=email></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your job:</TD>
<TD>
<INPUT TYPE=text NAME=job></TD>
</TR>
<TR>
<TD ALIGN="RIGHT" VALIGN="TOP">
Your message:</TD>
<TD>
<TEXTAREA NAME=comments COLS=40 ROWS=6></TEXTAREA>
<P>
<INPUT TYPE=submit VALUE=Submit> <INPUT TYPE=Reset VALUE=Reset>
<A ALIGN="RIGHT" HREF="guestbook.php3?action=view" onMouseOver="='Read all comments first';return true" onMouseOut="='';return true" TITLE="Read all comments first"><FONT SIZE="-2">View all comments first</FONT></A>
</TD>
</TR>
</FORM>
</TABLE>
</CENTER>
<?
}
/* action=send : add the data from the user into the database */
elseif($action == "send") {
/* check if a HTML-mail should be send or a plain/text mail */
if($html_mail == "yes") {
mail("$admin_name <$admin_mail>","PHP3 Guestbook Addition","<HTML><BODY><FONT FACE="Century Gothic"><TABLE BORDER="0" WIDTH="100%" CELLSPACING="4"><TR>$name ($email) schreef het volgende bericht in het gastenboek :</TR><TR><TD ALIGN="LEFT"> </TD><TD ALIGN="LEFT" NOWRAP> </TD></TR><TR><TD ALIGN="LEFT">$comments</TD><TD ALIGN="LEFT" NOWRAP> </TD></TR><TR><TD ALIGN="LEFT"> </TD></TR><TR><TD ALIGN="LEFT" NOWRAP> </TD></TR><TR><TD ALIGN="LEFT">Your message:</TD><TD ALIGN="LEFT" NOWRAP>$name</TD></TR><TR><TD ALIGN="LEFT">Your name:</TD><TD ALIGN="LEFT" NOWRAP>$email</TD></TR><TR><TD ALIGN="LEFT">Your email:</TD><TD ALIGN="LEFT" NOWRAP>$job</TD></TR><TR><TD ALIGN="LEFT">Your job:</TD></TR></TABLE></BODY></FONT></HTML>", "From: $name <$email>nReply-To: $name <$email>nContent-type: text/htmlnX-Mailer: PHP/" . phpversion());
}
/* MySQL really hates it when you try to put things with ' or " characters into a database, so strip these...*/
$comments = addslashes ("$comments");
$query = "INSERT INTO guestbook VALUES('','$name', '$email', '$job', '$comments')";
$result = MYSQL_QUERY($query);
?>
<BR><P ALIGN = CENTER>Thanks, <?php echo $name; ?>, your message.
<BR><P ALIGN = CENTER><A HREF="guestbook.php3?action=view" onMouseOver="='View your comment now';return true" onMouseOut="='';return true" TITLE="View your comment now">Watch the message</A><BR><BR>
<?
}
/* if there's no action given, then we must show the main page */
else {
/* get the number of entries written into the guestbook*/
$query = "SELECT name from guestbook";
$result = MYSQL_QUERY($query);
$number = MYSQL_NUMROWS($result);
if ($number == "") {
$entry = "No one left a comment yet"; }
elseif ($number == "1") {
$entry = "1 current number of comments"; }
else {
$entry = "Current number of comments $number"; }
echo "<CENTER><BR>";
echo "<P>$entry<BR>";
echo "<H4><FONT FACE="Verdana" SIZE="3"><A HREF="guestbook.php3?action=add" onMouseOver="='Please leave a message';return true" onMouseOut="=';return true" TITLE="Add your name to our guestbook">Please leave a message</A></FONT></H4>";
if ($number > "") {
echo "<H4><FONT FACE="Verdana" SIZE="3"><A HREF="guestbook.php3?action=view" onMouseOver="='Watch Message';return true" onMouseOut="='';return true" TITLE="View the names in our guestbook">Watch Message</A></FONT></H4>"; }
echo "</P></CENTER>";
}
?>
<BR><SMALL><CENTER>Copyright: <A HREF="http://personal./haitang/" onMouseOver="='pert';return true" onMouseOut="='';return true" TITLE="pert">Boundless Sky</A></CENTER></SMALL>
</FONT>
</BODY>
</HTML>