2. PHP basics-connect to the msql database to add, delete, modify and check operations & actual combat: news management project

Mysql extension
PHP provides extensions for MySQL database operations: allowing PHP to operate as a MySQL client connecting to the server.
Basic operation of
database connection Connect to database server
1) Resource mysql_connect (server address, user name, password)

Insert picture description here


Insert picture description here

The connection resource is also super-global by default, and the resource can be used for other operations of the database anywhere

Supplement: mysql_connect connects to a server only once by default (if you run it again, the same resource is returned), but if you need to connect to the same resource twice, you can use the fourth parameter: TRUE

Set the connection code to
keep the communication between the client and the server smooth: the same "language" (character set)
1) Form 1: mysql_query("set names XXX")

Insert picture description here


Insert picture description here

2) Form 2: mysql_set_charset("XXX")

Insert picture description here


Insert picture description here

3) How to determine which code to use
? What character set is the client's current script execution interface, and set it to which character set to
select the database to be used
1) Form 1: mysql_query("use XXX")
2) Form 2 : Mysql_select_db("XXX")

Insert picture description here


Insert picture description here

Close the connection
Actively release the connection: the connection resources of the MySQL server are limited, and need to be released if you don’t need it (the system will automatically release when the script is executed)
1) mysql_close()

Insert picture description here


Insert picture description here

The execution
result of adding, deleting and modifying operation Mysql_query function returns true or false, true represents successful execution, false represents execution failure: there are two reasons for failure: one is the syntax error of the SQL command itself; the second is the execution failure;

Insert picture description here

Make a script specifically responsible for database initialization: afterwards, you need to operate the database and include the script

Insert picture description here


Insert picture description here

1) mysql_query("insert …"): insert the corresponding data into the specified table through the insert instruction

Insert picture description here


Insert picture description here

2) mysql_query("delete …"): Operate the database (table) according to the delete instruction

Insert picture description here


Insert picture description here

3) mysql_query("update …"): Operate the database according to the update instruction

Insert picture description here


Insert picture description here

Judge the execution result
1) The execution is successful, the result is true, it only means that the SQL command is executed successfully
2) The execution fails, the result is false, it means that the SQL command is wrong or the operation object does not exist

Insert picture description here

Execute query operation
Execute query statement
1) mysql_query("select …")
2) Other similar query statements, such as show statement, desc statement
. Summary of the above two situations: Anyone who performs an operation and hopes to get the data returned by the database for display ( Result return: data result)
3) Execution result processing: success is the result set, if failure is false, the
success return result: the SQL command has no error, but if the query result itself is empty, the return is true (the result set is a resource: converted to bool is always TRUE)

Insert picture description here


Insert picture description here

Failed to be false: There is an error in the SQL command

Insert picture description here

Get the number of rows in the result set
1) mysql_num_rows(): Get the number of rows in the result set

Insert picture description here


Insert picture description here

Parsing the result set
converts a result set resource (PHP cannot be used directly) into a data format that PHP can parse: by extracting the corresponding one from the result set (result set pointer: similar to an array pointer) according to the location of the result set pointer Record (one line), return an array, and move the pointer down at the same time...until the pointer moves out of the result set.
1) mysql_fetch_assoc(): Get an associative array, the form name of the table is used as the array subscript, and the element value is used as the value of the array element

Insert picture description here


Insert picture description here

2) mysql_fetch_row(): Get the index array, only get the value of the data, and then the subscript of the array is automatically indexed from 0

Insert picture description here


Insert picture description here

3) mysql_fetch_array(): Get an associative or indexed array, but the default is to exist at the same time: a record is fetched twice, forming a group of associative arrays, and a group of indexed arrays; but the second parameter can be used to determine the method of obtaining: MYSQL_ASSOC only obtains associative array; MYSQL_NUM only obtains index array; MYSQL_BOTH obtains both.

Insert picture description here


Insert picture description here

Usually the operation of the result set: generally get all (all records) in it

Other related functions
Related field information
1) mysql_num_fields(): Get the number of all fields in a specified result set

Insert picture description here


Insert picture description here

2) mysql_field_name(): Get the name of a field at a specified location in a specified result set

Insert picture description here


Insert picture description here

Error information
1) mysql_error(): Get the prompt information
corresponding to the error 2) mysql_errno(): Get the error prompt code corresponding to the error

Wrong judgment: Based on the result of the execution of the Mysql_query function: If the result returns false, it means an execution error.

Insert picture description here


Insert picture description here

Other functions
1) mysql_insert_id(): Get the self-increasing ID generated by the last insert operation, if there is no self-increasing ID, return 0

Insert picture description here


Insert picture description here

Mysql extension application case
Add news form
1) The user gets an HTML file that can add news form

Insert picture description here

Modify form items: method submission object

Insert picture description here

2) The user submits the data to the specified PHP script in the background (POST)

Insert picture description here


Insert picture description here

3) The background script processes the data and inserts it into the database: Processing means that PHP does a lot of verification work before getting the data into the database to ensure that the data is valid.
3.1 Receive data

Insert picture description here


Insert picture description here

3,2 Data verification: legality verification. Title cannot be empty, content cannot be empty

Insert picture description here

3.3 Prompt the user and ask the user to start again (return to the new form page)

Insert picture description here


Insert picture description here

3.4 Data storage

Insert picture description here


Insert picture description here

4) Prompt the user of the operation result and jump to the list page (jump to the details page)

Insert picture description here


Insert picture description here

Mysql extension application case
Display news list
Get all the news information in the database, and then ask for the display template provided by HTML to output and display all the data according to the specified location and format.

1. Get all news data from the database: PHP script

Insert picture description here

Take out all the records from the result set: take one at a time, one array at a time; then put all the records in an array: form a typical two-dimensional array

Insert picture description here


Insert picture description here


Insert picture description here

2. There is a ready-made HTML template that can display data: the front end

Insert picture description here

3. PHP finds a way to display the data in HTML (combination of the two): PHP contains HTML

Insert picture description here

Pay attention to the details:
1. The display of data should be output by looping traversal: It is necessary to clarify how PHP saves the data.
2. The format of the data needs to be displayed according to requirements: list, time, whether to top
Mysql extension application case
Delete news
delete news is selected by the user Click to delete a certain news to remove it from the database.

1. Let the user see all the news: completed (news display list: news.phpnews.html)
2. Provide the user with a corresponding click button: already exists
3. Realize the click for the specified news

Insert picture description here

4. There is a special PHP script responsible for responding to user clicks: click which one to delete which
4.1 receive the news ID to be deleted

Insert picture description here

4.2 Call database operation to delete data

Insert picture description here

4.3 Prompt that the deletion is successful, and refresh the current interface

Insert picture description here

Mysql extension application case
Modifying news
Modifying news refers to the news that the user wants to modify through a certain fetch, and then click to modify the corresponding news information, and finally click save to realize the modification and update to the database.

1. You need to see the news you want to edit: Completed: News list news.phpnews.html
2. You can click to view it: Add a click link for each news in the list page

Insert picture description here

3. A PHP script is needed to handle this.
3.1 Create a PHP file and accept the ID to be edited

Insert picture description here

3.2 Get the corresponding news information through ID

Insert picture description here

4. An HTML template is needed to display the current data to be edited: PHP contains HTML
4.1 contains template HTML

Insert picture description here

4.2 Display the corresponding data in the template form

Insert picture description here

5. The update form provided by the front end needs to be modified: the form submission object (unlimited)
5.1 The form submission object needs to be modified

Insert picture description here

5.2 The update operation is that the background selects a certain news to update according to the data submitted by the form: the form must contain a unique news that can be recorded: ID, and the ID does not need to be shown to the user, and a hidden form needs to be added

Insert picture description here

6. A PHP script is required to accept the modified data and save it to the database
6.1 Add a PHP script to update the data

Insert picture description here

6.2 Receive data submitted by users and perform data verification at the same time: neither the title nor the content can be empty

Insert picture description here

6.3 Organizing SQL updates to the database

Insert picture description here

6.4 Prompt results

Insert picture description here