Database Link
10. The biggest characteristic of PHP is its powerful ability to operate databases. PHP provides support for multiple databases.
With PHP, you can easily connect to the database, request data and display it on your web site, or even modify data in the database. In this section, we will take the MySQL database that is most used with PHP on the Internet as an example, this section describes the related MySQL database operation functions and basic database operations.
In the MySQL database, there are two functions used to connect to the database:
Integer mysql_connect (string host, string user, string password );
Integer mysql_pconnect (string host, string user, string password );
Both the mysql_connect function and mysql_pconnect function connect to the MySQL database on the specified host. If the database is on a different port, you can add a colon and port number to the host name. Function parameters can also be left blank by default. If no parameter is set, the default host name is "localhost", the user name is the database administrator, the default value is "root", and the password is blank. After the connection to the database is successful, both functions can return a connection number. If the connection fails, a false value is returned. Let's take a look at the following statements:
<?
$ Db = mysql_connect ("localhost", "user", "password ");
Mysql_select_db ("mydb", $ db );
?>
Note:
$ Db = mysql_connect ("localhost", "user", "password"); the connection parameters of mysql, including the host name, user name, and password, are used as the parameters of mysql_connect, at the same time, the returned value is $ db. In this way, in the following statement, we can use the variable $ db as the connection number to connect 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 complete.
10.1 A simple database message book
After connecting the database, we can perform a series of operations on the database. The following is a simple database message Book Program (guestbook. php3 ):
I assume that the MySQL database on your machine and the Phpmyadmin_2. 0.5 tool for managing the MYSQL database have been installed and can work properly.
The first thing we need to do is create a message database, which is assumed to be named 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 press the create button.
Next, we will create a data table in the left-message database, assuming the name is guestbook.
The command for creating a 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, copy the following message book program to the writeable directory of your machine and save it as the guestbook. php3 file. That's 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 */
/* $ 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@21cn.com ";
$ 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 =" window. status = 'add your name'; return true "onMouseOut =" window. status = ''; return true" TITLE = "Add your name"> Add message </A> </FONT> </CENTER> <br> ";
If ($ total = 0 ){
Print "<CENTER> no message left at the moment </CENTER> <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 =" window. status = 'email $ i2 '; return true "onMouseOut =" window. status = ''; 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> enter 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 Email: </TD>
<TD>
<Input type = text NAME = email> </TD>
</TR>
<TR>
<Td align = "RIGHT" VALIGN = "TOP">
Your work: </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 =" window. status = 'read all comments first '; return true "onMouseOut =" window. status = ''; return true" TITLE = "Read all comments first"> <font size = "-2"> View all messages 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 shocould 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> <td align = "LEFT"> </TD> <td align = "LEFT" NOWRAP> </TD> </TR> <td align = "LEFT"> $ comments </TD> <td align = "LEFT" NOWRAP> </TD> </TR> <td align = "LEFT"> </TD> <td align = "LEFT" NOWRAP> </TD> </TR> <td align = "LEFT"> your message: </TD> <td align = "LEFT" NOWRAP> $ name </TD> </TR> <td align = "LEFT"> your name: </TD> <td align = "LEFT" NOWRAP> $ email </TD> </TR> <td align = "LEFT"> your email: </TD> <td align = "LEFT" NOWRAP> $ job </TD> </TR> <td align = "LEFT"> your work: </TD> </TR> </TABLE> </BODY> </FONT> </HTML> "," From: $ name <$ email> nReply-: $ 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> Thank you, <? Php echo $ name;?>, Your message.
<BR> <p align = CENTER> <a href = "guestbook. php3? Action = view "onMouseOver =" window. status = 'view your comment now '; return true "onMouseOut =" window. status = ''; return true" TITLE = "View your comment now"> View comments </A> <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 has left a message ";}
Elseif ($ number = "1 "){
$ Entry = "currently one member ";}
Else {
$ Entry = "current number of people leaving a message $ number ";}
Echo "<CENTER> <BR> ";
Echo "<P> $ entry <BR> ";
Echo "<H4> <font face =" Verdana "SIZE =" 3 "> <a href =" guestbook. php3? Action = add "onMouseOver =" window. status = 'Leave a message '; return true "onMouseOut =" window. status = ''; 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 =" window. status = 'view comments '; return true "onMouseOut =" window. status = ''; return true" TITLE = "View the names in our guestbook"> View Messages </A> </FONT> </H4> ";}
Echo "</P> </CENTER> ";
}
?>
<BR> <SMALL> <CENTER> copyright: <a href = "http://personal.668.cc/haitang/index.htm" onMouseOver = "window. status = 'pert '; return true "onMouseOut =" window. status = ''; return true" TITLE = "pert"> boundless sky </A> </CENTER> </SMALL>
</FONT>
</BODY>
</HTML>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
