How to add and query MySQL data with a PHP script

This article describes how to add and query MySQL data using a PHP script, that is, to use the basic SELECTFROM and INSERTINTO statements in a PHP program. For more information, see MySQL Insert Into add data
INSERT
The insert into syntax is used to add data records to a data table.
Syntax:

INSERT INTO tb_name VALUES (value1, value2,...)

This syntax inserts data records into all fields in the table in sequence.
But in more cases, it is to add records to the specified column:

INSERT INTO tb_name (column1, column2,...) VALUES (value1, value2,...)

The following example adds a record to the user table:

<? Php $ conn = @ mysql_connect ("localhost", "root", "root123"); if (! $ Conn) {die ("failed to connect to the database :". mysql_error ();} mysql_select_db ("test", $ conn); mysql_query ("set names 'gbk '"); // code conversion to avoid Chinese garbled characters in the database // mysql_query ("set names 'utf8 '"); // when the PHP file is in UTF-8 format, use $ password = md5 ("123456"); // The original password 12345 is encrypted and the password $ regdate = time (); // Obtain the timestamp $ SQL = "INSERT INTO user (username, password, email, regdate) VALUES ('John ',' $ password', '2017 @ 163.com ', $ regdate) "; // exit ($ SQL); // exit the program and print the SQL statement Debug if (! Mysql_query ($ SQL, $ conn) {echo "failed to add data:". mysql_error ();} else {echo "data added successfully! ";}?>

For data submitted by forms, you can use $ _ POST or $ _ GET to receive form data on the data processing page and write the data to the data table.
Description
1. to prevent data records from being written into data tables or garbled characters due to encoding problems, encoding conversion is performed before mysql_query () is executed.
2. the storage password is actually encrypted by MD5, and MD5 encryption is irreversible. to verify the password, you only need to encrypt the password entered by the user by MD5 and then compare it with the database password.
3. in SQL statements, we use single quotes ''to indicate text character attributes.
4. in order to debug the exceptions in data writing to the data table, the function of exiting the program and printing SQL statements is added. when debugging is required, you can remove the comments before the statements to make them take effect and facilitate debugging.

MySQL Select from query data
Common Query
The select from syntax is used to query and read data FROM a data table.
Syntax:

SELECT column1,column1,... FROM tb_name

If you want to read all fields, you can use the * number to replace the field name:

SELECT * FROM tb_name

Example:

<? Php $ conn = @ mysql_connect ("localhost", "root", "root123"); if (! $ Conn) {die ("failed to connect to the database :". mysql_error ();} mysql_select_db ("test", $ conn); mysql_query ("set character set 'gbk '"); // avoid converting Chinese garbled characters to mysql_query ("set character set 'utf8'"); // use $ SQL = "SELECT * FROM user" when the PHP file is in UTF-8 format "; $ result = mysql_query ($ SQL); // Obtain the query result dataset // retrieve the data from the dataset cyclically while ($ row = mysql_fetch_array ($ result) {echo "username :". $ row ['username']."
"; Echo" email: ". $ row ['email ']."
"; Echo" registration date: ". date (" Y-m-d ", $ row [regdate])."

";}?>

Browser output:
Username: admin
Email: admin@5idev.com
Registration Date:

Username: James
Email: xiao@163.com
Registered on: 2010-07-02

User Name: Jack
Email: jack@gmail.com
Registered on: 2010-07-02

Username: Xiao Wang
Email: 12345@163.com
Registration Date:
Description
1. use mysql_query ("set character set 'gbk'") to prevent Chinese garbled characters from reading data.
2. mysql_query () obtains the dataset Resource, which must be obtained using the mysql_fetch_array () function.
3. use the while loop to get all data row by row