php tutorial introductory tutorial-database tutorial operation
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 the records in the database.
We will learn MySQL and PHP at the same time and feel them together. This article can be learned directly from here. If you don’t know how to install and configure Apache+PHP+Mysql, please check out the related articles on the web teaching network!
First run the web server (PHP extension has been added); run MySQL.
Create and manipulate a MySQL database:
First we need to create the database and tables to be used. The database is named "example", the table is named "tbl", and has the following fields: identification number, first name, last name and information. To complete the database creation and table definition work through the mysql tutorial terminal, just 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 the terminal prompt):
Mysql> show databases;
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;
You will see an answer such as:
Query OK, 1 row affected (0.17 sec) Great, 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;
The answer should be:
Database changed
Now we can create a table with the following fields:
Index number - integer
Username - a string with a maximum length of 30
User last name - a string with a maximum length of 50
Free message - string with maximum length 100
Type the following command at the MySQL prompt to create the table:
MySQL> create table tbl (idx integer(3), UserName varchar(30), LastName varchar(50), FreeText varchar(100));
The answer should be:
Query OK, 0 rows affected (0.01 sec)
Okay, let’s see what it looks like to view the table from the MySQL prompt. Type the command:
MySQL> show columns from tbl;
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 contents of the table "tbl" we 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 data in table "tbl". The output might be:
The reason why Empty set (0.07 sec) gets this result is because we have not inserted any data into the table. Let's insert some data into the table by typing:
MySQL> insert into tbl values (1,’Rafi’,’Ton’,’Just a test’);
Query OK, 1 row affected (0.04 sec)
As you can see above, the values we insert into the table are in the order in which we defined the table earlier, because the default order is used. We can set the order of data, the syntax is as follows:
MySQL> insert into tbl (idx,UserName,LastName,FreeText) values (1,’Rafi’,’Ton’,’Just a test’);
Okay, now we can take a look at the contents of the table again:
MySQL> select * from tbl;
The result this time 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. To achieve this we should type:
MySQL> delete from tbl where idx=1 limit 1;
Okay, give me some explanations. We are telling MySQL to delete records from the "tbl" table, delete those records with an idx field value of 1, and limit deletion to only one record. If we don't limit the number of deleted records to 1, then all records with idx 1 will be deleted (in this example we only have one record, but nonetheless, I just wanted to make this more clear).
Unfortunately, we get an empty table again, so let’s type it in again:
MySQL> insert into tbl values (1,’Rafi’,’Ton’,’Just a test’);
Query OK, 1 row affected (0.04 sec)
Another thing you can do is to modify the content of the specified field using the "update" command:
MySQL>update tbl set UserName=’Berber’ where UserName=’Rafi’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
This command will search for all records with UserName "Rafi" and change it to "Berber". Note that the set part and where part do not have to be the same. We can search one field but change another field. Moreover, we can perform searches with two or more criteria.
MySQL>update tbl set UserName=’Rafi’ where UserName=’Berber’ and LastName=’Ton’;
Query OK, 1 row affected (0.04 sec)
This query searches two fields and changes the value of UserName
Combining PHP and MySQL
In this part, we will create a simple PHP-based web site to control the MySQL table created earlier.
We will create the following site structure (assuming you already know some basic HTML knowledge):
1. index.php3 is used to view the table on 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 the records in the table
First, we want to check the database and take a look at the following script:
-------------------------------------------------- -------------------------------
Index.php
| User Name | Last Name | Domain Name | Request Date |
| $idx | $user | $last | $text |
