Php and database combined with a simple web instance code analysis (php beginners)

However, apart from the installation part, there are more or less instructions for Windows, and the other part is the same for all platforms. By the way, for the installation part, please refer to the installation guide on this site. In this tutorial, we will build a small Website step by step, using the following features of PHP and MySQL:
1. view the database;
2. edit database records;
3. Modify database records;
4. delete database records.
We will learn about MySQL and PHP at the same time and feel them together. This article will be learned from here. If you do not install and configure Apache + PHP + Mysql, please refer to the relevant articles on Web tutorial network!
Run the web server (PHP extension added) first and MySQL.
Create and manipulate a MySQL database:
First, we need to create the database and table to be used. The database name is "example" and the table name is "tbl". It has the following fields: identification number, name, last name, and information. To create a database and define a table on the mysql terminal, double-click or run c: \ mysql \ bin \ mysql.exe.
If you want to see which tables have been defined in MySQL, you can use (note mysql> terminal prompt ):
Mysql> show databases; <press enter>
This command may display the following information:
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
+ ---------- +
2 rows in set (0.01 sec)
To define a new database (example), type:
Mysql> create database example; <press enter>
You will see an answer, for example:
Query OK, 1 row affected (0.17 sec) is very popular. Now we have a new database. Now we can create a new table in the database, but first we need to select the new database:
Mysql> use example; <press enter>
The answer should be:
Database changed
Now we can create a table with the following fields:
Index Number-integer
Username-string with a maximum length of 30
User surname-string with a maximum length of 50
Free Info-string with a maximum length of 100
At the MySQL prompt, type the following command to create a table:
MySQL> create table tbl (idx integer (3), UserName varchar (30), LastName varchar (50), FreeText varchar (100); <press enter>
The answer should be:
Query OK, 0 rows affected (0.01 sec)
Well, let's take a look at what the table looks like from the MySQL prompt. type the command:
MySQL> show columns from tbl; <press enter>
We will get the following results:
+ ---------- + -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + -------------- + ------ + ----- + --------- + ------- +
| Idx | int (3) | YES | NULL |
| UserName | varchar (30) | YES | NULL |
| LastName | varchar (50) | YES | NULL |
| FreeText | varchar (100) | YES | NULL |
+ ---------- + -------------- + ------ + ----- + --------- + ------- +

4 rows in set (0.00 sec)
Here, we can see the content of the newly created table "tbl.
Now let's take a look at what is in the table. Type the following command:
MySQL> select * from tbl; <press enter>
This command is used to display all data in the table "tbl. The output may be:
Empty set (0.07 sec) returns this result because we have not inserted any data into the table. Let's insert some data into the table and type:
MySQL> insert into tbl values (1, 'rafi ', 'ton', 'just a test'); <press enter>
Query OK, 1 row affected (0.04 sec)
As shown in the preceding figure, the values inserted into the table follow the sequence defined in the previous table, because the default sequence is used. We can set the data sequence. The syntax is as follows:
MySQL> insert into tbl (idx, UserName, LastName, FreeText) values (1, 'rafi ', 'ton', 'just a test'); <press enter>
Okay. Now let's take a look at the table content:
MySQL> select * from tbl; <press enter>
The result is:
+ ------ + ---------- + ------------- +
| Idx | UserName | LastName | FreeText |
+ ------ + ---------- + ------------- +
| 1 | Rafi | Ton | Just a test |
+ ------ + ---------- + ------------- +
1 row in set (0.00 sec)
Now we can see the structure of the table and the content of each cell.
Now we want to delete the data. To achieve this, enter:
MySQL> delete from tbl where idx = 1 limit 1; <press enter> Query OK, 1 row affected (0.00 sec)
Okay. Give some explanations. We are telling MySQL to delete records from the "tbl" table, delete those records whose idx field value is 1, and delete only one record. If we do not limit the number of deleted records to 1, all records with idx as 1 will be deleted (in this example, we only have one record, but even so, I just want to make it clearer ).
Unfortunately, we once again got an empty table, so let's try again:
MySQL> insert into tbl values (1, 'rafi ', 'ton', 'just a test'); <press enter>
Query OK, 1 row affected (0.04 sec)
Another thing that can be done is to modify the content of the specified field and use the "update" command:
MySQL> update tbl set UserName = 'berberber' where UserName = 'rafi '; <press enter>
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
This command will search all records whose UserName is "Rafi" and change it to "Berber ". Note that the set part and the where part are not necessarily the same. We can search for a field but change the other field. In addition, we can perform two or more search conditions.
MySQL> update tbl set UserName = 'rafi 'where UserName = 'berberber' and LastName = 'ton'; <press enter>
Query OK, 1 row affected (0.04 sec)
This query searches for two fields and changes the UserName value.

Combination of PHP and MySQL
In this section, we will create a single PHP-based web site to control the previously created MySQL table.
We will build the following site structure (assuming you already know some basic HTML knowledge ):
1. index. php3 is used to view the table at the front end. 2. add. php3 is used to insert data into the table.
3. Modify. php3 is used to Modify the records in the Table. 4. del. php3 is used to delete records in the table.
First, let's look at the database and see the scripts below the workshop:
--------------------------------------------------------------------------------
Index. php

<Html>
<Head> <title> Web Database Sample Index </title>
</Head>
<Body bgcolor = # ffffff>
<H2> Data from tbl <?
Mysql_connect () or die ("Problem connecting to DataBase ");
$ Query = "select * from tbl ";
$ Result = mysql_db_query ("example", $ query );
If ($ result ){
Echo "Found these entries in the database: <br> <p> </p> ";
Echo "<table width = 90% align = center border = 1> <tr>
<Td align = center bgcolor = #00 FFFF> User Name </td>
<Td align = center bgcolor = #00 FFFF> Last Name </td>
<Td align = center bgcolor = #00 FFFF> Domain Name </td>
<Td align = center bgcolor = #00 FFFF> Request Date </td>
</Tr> ";
While ($ r = mysql_fetch_array ($ result ))
{
$ Idx = $ r ["idx"];
$ User = $ r ["UserName"];
$ Last = $ r ["LastName"];
$ Text = $ r ["FreeText"];
Echo "<tr>
<Td> $ idx </td>
<Td> $ user </td>
<Td> $ last </td>
<Td> $ text </td>
</Tr> ";
}
Echo "</table> ";
}
Else
{
Echo "No data .";
}
Mysql_free_result ($ result );
Include ('links. x ');
?>
</Body>
</Html>
--------------------------------------------------------------------------------
Okay. Here are some instructions:
We first create thml documents with normal html tags. When we want to transfer from html to PHP, we use <? To open the PHP Section. This tells the web server to regard the subsequent text as PHP syntax rather than general html. Use?> To end the PHP section.
The mysql_connect () Command tells PHP to establish a connection with the MySQL server. If the connection is established successfully, the script continues. If the connection fails, then print out the die Command Information "Problem connecting to Database" (if you want to see more information about mysql_connect and other PHP functions, you can go to the document under the http://www.php.net to find ).
Now, if MySQL is installed as discussed above, it is enough. However, if you are using pre-installed MySQL (like ISP), you should use the following command:
Mysql_connect (localhost, username, password );
We can set $ query to the query we want to execute in MySQL, and then run it using the mysql_db_query command:
$ Result = mysql_db_query ("example", $ query );
In this case, "example" indicates the Database Name and $ query is the query to be performed.
We use the MySQL Command select (as described above) to retrieve all data from the table:
$ Query = "select * from tbl ";
Briefly explain the functions of $ result. If the execution is successful, the function returns a MySQL result identifier of the query result. If an error occurs, false is returned. The returned result is not a result identifier, which can be converted to the information we need later.
Now, we want to check whether there are records in the database, and if so, print the results according to the html table structure. To check whether data exists, we use the if Command and the following syntax:
If (argument ){
"Do something ;"
} Else {
"Do something different ;"
}
In this case, "do something" indicates the command to be executed when argument is set to true. "do something different" indicates the command to be executed when argument is set to false.
Note that we use the echo command to output some html tags to create an html table structure. Only the text output from the PHP Command is considered html content-the PHP Command itself is not considered html content. Another command we use is the while command. The format is as follows:
While (argument )){
"Something to do ";
}
The while LOOP repeats repeatedly when argument = true and runs the instruction set in.
Here we combine the while loop and PHP functions $ r = mysql_fetch_array ($ result ). This function retrieves a record based on the corresponding result identifier and places the result in an associated array $ r. It uses the field name as the key value of the array. In our script, we will get an array: $ r ['idx'], $ r ['username'], $ r ['lastname'] and
$ R ['freetext '].
We can also use the mysql_fetch_row function to place the results in an ordered array. We can use $ r [0], $ r [1], $ r [2] and $ r [3] to obtain the corresponding values. .
Now that we have all the information, we can print it out in an html table:
Reference content is as follows:
Echo "<tr>
<Td> $ idx </td>
<Td> $ user </td>
<Td> $ last </td>
<Td> $ text </td>
</Tr> ";
Now we can release MySQL connections and release some resources by using the mysql_free_result ($ result) function.
Another useful feature of PHP is to include text files in scripts. Let's assume that you have some reusable code (such as links to other pages). We can use the include function to save some code and time. Moreover, if you want to change the code, you only need to change the content of the contained file, which will take effect in all the files that contain it.
Here we create a text file named Links. x, which stores all the link menus we want to use on every page.
<P> </p>
<Ul>
<Li> <a href = "index. php3"> Home </a>
<Li> <a href = "add. php3"> Add a new entry to the DataBase </a>
<Li> <a href = "edit. php3"> Edit an entry </a>
<Li> <a href = "del. php3"> Delete an entry from the DataBase </a>
</Ul>
The include syntax is:
Include ('encoded _ text_file ');
Can we use it now?> To close the PHP section and use </body> Using the form to add data allows us to see the following code:
--------------------------------------------------------------------------------
<Html>
<Head> <title> Add an entry to the database </title>
</Head>
<Body bgcolor = # ffffff>
<H1> Add an entry <Form method = "post" action = "add2tbl. php3">
<Table width = 90% align = center>
<Tr> <td> Index: </td> <input type = text name = "idx" size = 3 maxlength = 3> </td> </tr> <td> UserName: </td> <input type = text name = "UserName" size = 40
Maxlength = 100> </td> </tr>
<Tr> <td> LastName: </td> <input type = text name = "LastName" size = 40
Maxlength = 100> </td> </tr> <td> FreeText: </td> <input type = text name = "FreeText" s = 40 maxlength = 100> </td> </tr> <td> </ td> <input type = submit value = add> </td> </tr>
</Form>
</Table>
<? Php include ('links. x');?>
</Body>
</Html>
--------------------------------------------------------------------------------
If you are familiar with forms, this is a simple script. We designed a form based on the html page. After the form is submitted, the add2tbl. php3 script is called. At present, the form and MySQL table should be composed of four fields: index number, FirstName, LastName, and FreeText. Note that the field names in this form are the same as those in the MySQL table, but this is only for convenience, not necessary.
We once again used the include command <? Include ('links. x');?> (As explained above) to add links.
Let's take a look at the add2tbl. php3 script:
--------------------------------------------------------------------------------
<Html>
<Body>
<?
If ($ UserName)
{
Mysql_connect () or die ("Problem connecting to DataBase ");
$ Query = "insert into tbl values ('$ idx',' $ username', '$ LastName', '$ FreeText ')";
$ Result = mysql_db_query ("example", $ query );
Echo "Data inserted. new table: <br> <p> </p> ";
$ Query = "SELECT * FROM tbl ";
$ Result = mysql_db_query ("example", $ query );
If ($ result)
{
Echo "<table width = 90% align = center border = 1> <tr>
<Td align = center bgcolor = #00 FFFF> idx </td>
<Td align = center bgcolor = #00 FFFF> User Name </td>
<Td align = center bgcolor = #00 FFFF> Last Name </td>
<Td align = center bgcolor = #00 FFFF> Free Text </td>
</Tr> ";
While ($ r = mysql_fetch_array ($ result ))
{
$ Idx = $ r ["idx"];
$ User
Note: The comments I made in the script. You can use "//" for a comment. The server will ignore the later part of this line.

Simple, isn't it? Edit a record from the database: Let's assume that we want to modify the existing record in the database. We can see that there is an SQL command called set to set the values of fields in the database. We will use this command to modify the entire record in the database. Consider the following script:
--------------------------------------------------------------------------------
Edit. php:Copy codeThe Code is as follows: <Head> <title> Editing an entry from the database </title>
</Head>
<Body bgcolor = # ffffff>
<H1> Edit an entry <?
Mysql_connect () or die ("Problem connecting to DataBase ");
$ Query = "select * from tbl ";
$ Result = mysql_db_query ("example", $ query );
If ($ result)
{
Echo "Found these entries in the database: <br> ";
Echo "<table width = 90% align = center border = 1> <tr>
<Td align = center bgcolor = #00 ffff> idx </td>
<Td align = center bgcolor = #00 FFFF> User Name </td>
<Td align = center bgcolor = #00 FFFF> Last Name </td>
<Td align = center bgcolor = #00 FFFF> Free Text </td>
</Tr> ";
While ($ r = mysql_fetch_array ($ result ))
{
$ Idx = $ r ["idx"];
$ User = $ r ["UserName"];
$ Last = $ r ["LastName"];
$ Text = $ r ["FreeText"];
Echo "<tr>
<Td align = center>
<A href = \ "editing. php3? Idx = $ idx & user = $ user & last = $ last & text = $ text \ "> $ idx </a> </td>
<Td> $ user </td>
<Td> $ last </td>
<Td> $ text </td>
</Tr> ";
}
Echo "</table> ";
}
Else
{
Echo "No data .";
}
Mysql_free_result ($ result );
Include ('links. x ');
?>
</Body>
</Html>

--------------------------------------------------------------------------------
As you can see, the code here is somewhat familiar. The first part only prints the table content in the database. Note that one line is not the same:
<A href = \ "editing. php3? Idx = $ idx & user = $ user & last = $ last & text = $ text \ "> $ idx </a>
This line creates a link to editing. php3 and passes some variables to the new script. Similar to the form, only links are used. We convert the information to: variable and value. Note: to print out the "symbol, we need to use \". Otherwise, the server will regard it as part of the PHP script and use it as printed information.
We want to convert all the records in the database, so that we can get the exact data in the table so that it is easier to modify it.
--------------------------------------------------------------------------------
Editing. phpCopy codeThe Code is as follows: <Head> <title> Editing an entry </title>
</Head>
<Body bgcolor = # ffffff>
<H1> Editing an entry <Form method = "post" action = "editdb. php3">
<Table width = 90% align = center>
<Tr> <td> idx: </td> <? Php echo "$ idx";?> </Td> </tr>
<Tr> <td> UserName: </td> <input type = text name = UserName size = 40 maxlength = 100
Value = "<? Php echo "$ user";?> "> </Td> </tr>
<Tr> <td> LastName: </td> <input type = text name = LastName size = 40 maxlength = 100
Value = "<? Php echo "$ last";?> "> </Td> </tr>
<Tr> <td> Free Text: </td> <input type = text name = FreeText size = 40 maxlength = 100
Value = "<? Php echo "$ text";?> "> </Td> </tr>
<Tr> <td> </td> <input type = submit value = "Edit it! "> </Td> </tr> </table>
<Input type = hidden name = idx value = "<? Php echo "$ idx";?> ">
</Form>
<? Php include ('links. x');?>
</Body>
</Html>

--------------------------------------------------------------------------------
Okay. This script is simple. When a form is printed, it records the data of the current record by using the value attribute in the <input type => command. The data is transmitted from the previous page.
Now, if we do not change the record information, it will return the current value, that is, the default value. If we change the field value, the field value will become a new value. Next we can pass the new value to another script, which will change the value in the MySQL table.
--------------------------------------------------------------------------------
Editdb. php:Copy codeThe Code is as follows: <? Php
Mysql_connect () or die ("Problem connecting to DataBase ");
$ Query = "update tbl set
Idx = '$ idx', UserName =' $ username', LastName = '$ lastname', FreeText =' $ FreeText 'where
Idx = '$ idx '";
$ Result = mysql_db_query ("example", $ query );
$ Query = "SELECT * FROM tbl ";
$ Result = mysql_db_query ("example", $ query );
If ($ result)
{
Echo "Found these entries in the database: <br> <p> </p> ";
Echo "<table width = 90% align = center border = 1> <tr>
<Td align = center bgcolor = #00 FFFF> idx </td>
<Td align = center bgcolor = #00 FFFF> User Name </td>
<Td align = center bgcolor = #00 FFFF> Last Name </td>
<Td align = center bgcolor = #00 FFFF> Free Text </td>
</Tr> ";
While ($ r = mysql_fetch_array ($ result ))
{
$ Idx = $ r ["idx"];
$ User = $ r ["UserName"];
$ Last = $ r ["LastName"];
$ Text = $ r ["FreeText"];
Echo "<tr>
<Td> $ idx </td>
<Td> $ user </td>
<Td> $ last </td>
<Td> $ text </td>
</Tr> ";
}
Echo "</table> ";
}
Else
{
Echo "No data .";
}
Mysql_free_result ($ result );
Include ('links. x ');
?>

--------------------------------------------------------------------------------
Basically, one thing to care about is the following line:
$ Query = "update tbl set idx = '$ idx', UserName =' $ username', LastName = '$ lastname ', freeText = '$ FreeText' where idx = '$ idx '";
Note that it is the same as the syntax we explained in the previous MySQL section. Another thing is that this script changes the idx = $ idx record. If there are multiple records with idx equal to $ idx In the table, these records will be changed. If we want to be stricter, we can change the where clause as follows:
$ Query = "update tbl set idx = '$ idx', UserName =' $ username', LastName = '$ lastname ', freeText = '$ FreeText' where idx = '$ idx' and UserName =' $ username' and LastName = '$ lastname' and FreeText =' $ FreeText '";
This syntax checks all fields, not just idx.
Delete a record from the database:
Yes, it is easy to delete. We still need two scripts: one for selecting the record to be deleted (basically the same as the record selected for editing) and the other for deleting and printing new tables.
--------------------------------------------------------------------------------
Del. phpCopy codeThe Code is as follows: <Head> <title> Deleting an entry from the database </title>
</Head>
<Body bgcolor = # ffffff>
<H1> Del an entry <?
Mysql_connect () or die ("Problem connecting to DataBase ");
$ Query = "select * from tbl ";
$ Result = mysql_db_query ("example", $ query );
If ($ result)
{
Echo "Found these entries in the database: <br> <p> </p> ";
Echo "<table width = 90% align = center border = 1> <tr>
<Td align = center bgcolor = #00 ffff> idx </td>
<Td align = center bgcolor = #00 FFFF> User Name </td>
<Td align = center bgcolor = #00 FFFF> Last Name </td>
<Td align = center bgcolor = #00 FFFF> Free Text </td>
</Tr> ";
While ($ r = mysql_fetch_array ($ result ))
{
$ Idx = $ r ["idx"];
$ User = $ r ["UserName"];
$ Last = $ r ["LastName"];
$ Text = $ r ["FreeText"];
Echo "<tr>
<Td align = center>
<A href = \ "dele. php3?
Idx = $ idx & UserName = $ user & LastName = $ last & FreeText = $ text \ "> $ idx </a> </td>
<Td> $ user </td>
<Td> $ last </td>
<Td> $ dtext </td>
</Tr> ";
}
Echo "</table> ";
}
Else
{
Echo "No data .";
}
Mysql_free_result ($ result );
Include ('links. x ');
?>
</Body>
</Html>