PHP Tutorial Getting Started Tutorial-database tutorial Operations
In this tutorial, we will build a small web site step after day, using the following features of PHP and MySQL:
1. View the database;
2. Edit the records of the database;
3. Modify the records of the database;
4. Delete the records for the database.
We will learn MySQL and PHP at the same time, to feel them together. This article directly from here to learn, if you do not install the configuration apache+php+mysql please check the Web teaching network related articles!
Run the Web server first (the PHP extension has been added), and run MySQL.
Create and manipulate a MySQL database:
First we need to create the database and table to use. The database is named "Example" with the table named "TBL" with the following fields: Identification number, first name, last name, and information. To complete the work of building and defining tables through the MySQL tutorial terminal, simply double-click or run C:mysqlbinmysql.exe.
If you want to see which tables have been defined in MySQL, you can use (note that mysql> is a terminal prompt):
mysql> show databases; <回车>
This command may display the following information:
+----------+
| Database |
+----------+
| MySQL |
| Test |
+----------+
2 rows in Set (0.01 sec)
In order to define a new database (example), type:
mysql> CREATE DATABASE example; <回车>
You will see an answer such as:
Query OK, 1 row affected (0.17 sec) Very fat, we now have a new database. Now we can create a new table in the library, but first we need to select the new database:
Mysql> use example; <回车> "Carriage return"
The answer should be:
Database changed
Now we can build a table with the following fields:
Index number-integer
User name-string with a maximum length of 30
Family name-string with a maximum length of 50
Free information-a string with a maximum length of 100
at the MySQL prompt, type the following command to create the table:
mysql> The CREATE TABLE tbl (IDX intege R (3), UserName varchar (+), LastName varchar (+), FreeText varchar (100)); "Carriage return"
The answer should be:
Query OK, 0 rows affected (0.01 sec)
OK, let's see what the table looks like from the MySQL prompt, type the command:
MYSQ L> show columns from TBL; "Carriage return"
We will get the following result:
+----------+--------------+------+-----+---------+-------+
| 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 contents of the table "TBL" just created.
Now let's take a look at what's in the table. Type the following command:
Mysql> select * from TBL; <回车>
This command is used to display all the data in the table "TBL". The output may be:
The Empty set (0.07 sec) gets this result because we haven't inserted any data into the table yet. Let's insert some data into the table and type:
mysql> INSERT into TBL values (1, ' Rafi ', ' Ton ', ' Just a test '); <回车>
Query OK, 1 row affected (0.04 sec)
As I see above, the values we insert into the table are in the order of the tables we defined earlier, because the default order is used. We can set the order of the data, the syntax is as follows:
mysql> INSERT INTO TBL (Idx,username,lastname,freetext) VALUES (1, ' Rafi ', ' Ton ', ' Just a test '); <回车>
OK, now we can look at the contents of the table again:
Mysql> select * from TBL; <回车>
This time 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 contents of each cell.
Now we want to delete the data. In order to implement we should type:
Mysql> Delete from tbl where idx=1 limit 1; <回车> Enter > Query OK, 1 row affected (0.00 sec)
OK, give me some explanations. We're telling MySQL to delete records from the TBL table, delete records with a value of 1 for the IDX field, and restrict the deletion of only one record. If we do not limit the number of deleted records to 1, then all records with IDX 1 will be deleted (in this case we only have one record, but even so, I just want to make this clearer).
Unfortunately, once again we got an empty table, so let's lose it:
mysql> insert INTO TBL values (1, ' Rafi ', ' Ton ', ' Just a test '); "Carriage return"
Query OK, 1 row affected (0.04 sec)
Another thing you can do is modify the contents of the specified field, using the "Update" command:
mysql>update tbl S Et username= ' Berber ' where username= ' Rafi '; "Carriage return"
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0
This command will search all use Rname the record for "Rafi" and change it to "Berber". Note that the set section and the where section do not have to be the same. We can search for a field but change another field. Furthermore, we can perform searches for two or more conditions.
Mysql>update tbl Set username= ' Rafi ' where username= ' Berber ' and lastname= ' Ton '; "Carriage return"
Query OK, 1 row affected (0.04 sec)
This query searched for two fields, changed the value of username
Combining PHP with MySQL
In this section, we will build a PHP-based Web site that controls the MySQL tables that were built.
We will build the following site structure (assuming you already know some basic HTML knowledge):
1. Index.php3 for Front end view table 2. ADD.PHP3 for inserting data into a table
3. MODIFY.PHP3 is used to modify record 4 in the table. Del.php3 used to delete records in a table
First, we want to look at the database and see the following script:
--------------------------------------------------------------------------------
index.php
Web Database Sample Index
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:
";
echo "
| User Name |
Last Name |
Domain Name |
Request Date |
"; while ($r = Mysql_fetch_array ($result)) {$idx = $r ["idx"]; $user = $r ["UserName"]; $last = $r ["LastName"]; $text = $r ["FreeText"]; echo "
| $idx |
$user |
$last |
$text |
"; } echo "
";
}
Else
{
echo "No data";
}
Mysql_free_result ($result);
Include (' links.x ');
?>
--------------------------------------------------------------------------------
OK, here are some instructions:
We'll start by creating a THML document with normal HTML tags. When we want to get out of the HTML into PHP, we use To end the PHP section.
The mysql_connect () command tells PHP to establish a connection to the MySQL server. If the connection is established successfully, the script will continue and if unsuccessful, print out the information "problem connecting to Database" of the Die Command (if you want to see more information about mysql_connect and other PHP functions, you can go to HTTP.// Found in the document under Www.php.net).
Now, if MySQL is installed as we discussed above, it will suffice. But if you are using pre-installed MySQL (like an ISP), you should use the following command:
mysql_connect (localhost, username, password);
We can set $query as the query we want to execute in MySQL, and then use the Mysql_db_query command to execute it:
$result = Mysql_db_query ("Example", $query);
At this point, "example" represents the name of the database and $query is the query to be made.
We use the MySQL command select (as described above) to get all the data from the table:
$query = "SELECT * from TBL";
Simply explain the role of $result, if successful, the function returns a MySQL result identifier for the result of the query, and returns False if an error occurs. The return is not the result but an identifier, which can be converted to the information we need later.
Now, we want to check if there is a record in the database, and if so, print the result in the HTML table structure. To check if there is data, we use the IF command and the following syntax:
if (argument) {
"Do something;"
} else {
"Do something different;"
}
At this time "do something" when argument=true the command you want to execute, "do something Different" is the command to be executed when argument =false.
Note that we use the echo command to output some HTML tags to create the HTML table structure. Only text that is output from a PHP command is considered HTML content-the PHP command itself is not considered HTML content. Another command we use is the while instruction, which uses the following format:
while (argument)) {
"Something to Do";
}
The while loop repeats repeatedly at Argument=true, executing the instruction set in {}.
Here we combine the while loop and PHP function $r=mysql_fetch_array ($result). This function retrieves a record based on the corresponding result identifier, and puts the result in a related array (associative array) $r, which uses the name of the field 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, which will put the result in an ordered array, we can use $r[0], $r [1], $r [2] and $r[3] to get the corresponding value:
Now that we have all the information, we can print it out in an HTML table:
The following is the referenced content:
echo "
$idx
$user
$last
$text
";
Now we can release the MySQL connection and release some resources by using the Mysql_free_result ($result) function.
Another useful feature of PHP is the ability to include text files in the script. Let's say that you have some reusable code (such as a link to another page), and we can use the Include function to save some code and time. And, if you want to change the code, we just need to change the contents of the included file, which will take effect in all the files that include it.
Here we create a text file called Links.x, which will store all the link menus that we want to use on each page.
- Home
- ADD a new entry to the DataBase
- Edit an entry
- Delete an entry from the DataBase
The syntax for include is:
Include (' Included_text_file ');
Now we can use?> to close the PHP section and useTo end the HTML page.
Using forms to add data let's look at the following code:
--------------------------------------------------------------------------------
<title>Add an entry to the database</title>
Add an entry
--------------------------------------------------------------------------------
Let's say you're familiar with the form, which is a fairly simple script. We designed a form based on an HTML page that invokes the Add2tbl.php3 script after committing. Now, the form and the MySQL table should consist of 4 fields: Index Number,firstname,lastname and FreeText. Note that the field names in this form are the same as the field names in the MySQL table, but this is only for convenience and not necessary.
Once again we used the include command (as explained above) to increase the link.
Let's take a look at the add2tbl.php3 script:
--------------------------------------------------------------------------------
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:
";
$query = "SELECT * from TBL";
$result = Mysql_db_query ("Example", $query);
if ($result)
{
echo "
Simple, isn't it? To edit a record from the database: Let's assume that we want to modify the records that exist in the database. In front, we see a SQL command called set to set the value of the field that exists in the database. We will use this command to modify the entire record in the database. Consider the following script:
--------------------------------------------------------------------------------
edit.php:
Editing an entry from the database
Edit an entry
| Idx |
User Name |
Last Name |
Free Text |
"; while ($r = Mysql_fetch_array ($result)) {$idx = $r ["idx"]; $user Note the comments that I made in the script. With a comment you can use "//" and the server ignores the later part of this line. 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:"; echo "
| Idx |
User Name |
Last Name |
Free Text |
"; while ($r = Mysql_fetch_array ($result)) {$idx = $r ["idx"]; $user = $r ["UserName"]; $last = $r ["LastName"]; $text = $r ["FreeText"]; echo "
$idx |
$user |
$last |
$text |
"; } echo "
";
}
Else
{
echo "No data";
}
Mysql_free_result ($result);
Include (' links.x ');
?>
--------------------------------------------------------------------------------
As you can see, the code here is somewhat familiar. The first part simply prints out the contents of the table in the database. Note that there is a different line:
$idx
This line establishes a link to editing.php3 and passes some variables to the new script. It's like a form, but it's just a link. We convert the information into: variables and values. Note that in order to print out "symbols we need to use" otherwise the server will treat it as part of the PHP script and as the printed information.
We want to convert all the records in the database to the past so that we can get the exact data in the table so that it is easier for us to modify it.
--------------------------------------------------------------------------------
editing.php
<title>Editing an entry</title>
Editing an entry
--------------------------------------------------------------------------------
OK, this script is simple. Our concern is that when the form is printed out, it records the data of the current record by The Value property in the command. This data was passed from a previous page.
Now, if we don't change the recorded information, it will return the current value, which is the default value. If we change the value of the field, the value of the field becomes the new value. Then we can pass the new value to another script, which will change the values in the MySQL table.
--------------------------------------------------------------------------------
editdb.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:
";
echo "
| Idx |
User Name |
Last Name |
Free Text |
"; while ($r = Mysql_fetch_array ($result)) {$idx = $r ["idx"]; $user = $r ["UserName"]; $last = $r ["LastName"]; $text = $r ["FreeText"]; echo "
| $idx |
$user |
$last |
$text |
"; } echo "
";
}
Else
{
echo "No data";
}
Mysql_free_result ($result);
Include (' links.x ');
?>
--------------------------------------------------------------------------------
One thing that basically concerns you 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 syntax that we explained in the previous MySQL section. Another thing, note that this script changes the records of idx= $idx, and if there are more than one IDX equal to $IDX records in the table, these records will be changed. If we want to be more strict, 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 examines all fields, not just the IDX.
To delete a record from the database:
OK, delete is easy. We still need two scripts: one to select the records to delete (basically the same as the record you chose to edit), one to really delete and print the new table.
--------------------------------------------------------------------------------
del.php
<title>Deleting an entry from the database</title>
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:
";
echo "
| Idx |
User Name |
Last Name |
Free Text |
"; while ($r = Mysql_fetch_array ($result)) {$idx = $r ["idx"]; $user = $r ["UserName"]; $last = $r ["LastName"]; $text = $r ["FreeText"]; echo "
idx= $idx &username= $user &lastname= $last &freetext= $text "> $idx |
$user |
$last |
$dtext |
"; } echo "
";
}
Else
{
echo "No data";
}
Mysql_free_result ($result);
Include (' links.x ');
?>
http://www.bkjia.com/PHPjc/629705.html www.bkjia.com true http://www.bkjia.com/PHPjc/629705.html techarticle PHP Tutorial Getting Started Tutorial-database tutorial operations in this tutorial, we will build a small web site step by step, using the following features of PHP and MySQL: 1. View the database; 2.
