PHP Operation MySQL Database

PHP sends data to MySQL, PHP operation MySQL database is the focus of emphasis.

To give you some examples:

1. To register a user, send the form's data post to PHP to write to the database
2. To buy a product is to write the product information and user information to the database via PHP
3. Online payment is to write the user's recharge information to the database via PHP
4. Modify Avatar upload avatar address to get the value of the Avatar field in the database via PHP

Attention:

MySQL extensions are no longer supported by default from PHP7, that is, mysql_* series functions are no longer supported. Please use mysqli to connect to the database.

MYSQLI support PHP5 also supports PHP7.

First, the database connection steps

First step: Connect to the database server

type	Description
Function	Mysqli_connect
Function	Connecting to a MySQL database server
Parameter 1	Host
Parameter 2	Database Server login Name
Parameter 3	Password
Parameter 4	Name of the database
Parameter 5	Database server port does not fill default 3306

If parameter 4, the database name is filled and selected in this step, no third step is required.

Step Two: Judging errors

type	Description
Function	Mysqli_errno
Function	Return connection error number, no error returned 0
Parameter 1	Resources returned by incoming Mysqli_connect

type	Description
Function	Mysqli_error
Function	Returns the connection error string
Parameter 1	Resources returned by incoming Mysqli_connect

Step three: Select a database

type	Description
Function	mysqli_select_db
Function	Select a database in this connection
Parameter 1	Resources returned by incoming Mysqli_connect
Parameter 2	The name of the database that needs to be connected

If you have completed the first step of the database, you do not need to change to another database, you do not need to perform a third step.

Fourth step: Setting the character set

type	Description
Function	Mysqli_set_charset
Function	Set the connection with MySQL power, result, check character set
Parameter 1	Resources returned by incoming Mysqli_connect
Parameter 2	Character set type

Fifth step: Preparing SQL statements

is actually a SQL statement character set.

Sixth step: Send SQL statements

type	Description
Function	Mysqli_query
Function	Send SQL statement
Parameter 1	Resources returned by incoming Mysqli_connect
Parameter 2	Incoming sent SQL statement

The SQL statement is ready to be completed and the SQL statement needs to be sent to the MySQL server via Mysqli_query.

Seventh step: Determine whether to perform normal or traverse data

In the 6th step, the statements for the Select category are sent, and the result output is usually displayed. You need to use a function that iterates through the display data.

type	Description
Function	Mysqli_fetch_array
Function	Get data in result set, return array for convenience
Parameter 1	Result variables passed in the query
Parameter 2	Incoming Mysqli_num returns an indexed array, MYSQLI_ASSOC returns an associative array, Mysqli_both returns an index and an association

type	Description
Function	Mysqli_fetch_assoc
Function	Get data in result set, return associative array for convenience
Parameter 1	Result variables passed in the query

type	Description
Function	Mysqli_fetch_row
Function	Get data in result set, return index array for convenience
Parameter 1	Result variables passed in the query

type	Description
Function	Mysqli_fetch_object
Function	Get data in result set, return object for traversal
Parameter 1	Result variables passed in the query

type	Description
Function	Mysqli_num_rows
Function	Returns the total number of results from the query
Parameter 1	Result variables passed in the query

type	Description
Function	Mysqli_num_rows
Function	Returns the total number of results from the query
Parameter 1	Result variables passed in the query
Note	Use very little in the actual work, understand

Write

In the 6th step, if you send an INSERT statement, you usually need to get the success of the execution, or get the self-increment ID at the same time.

type	Description
Function	Mysqli_fetch_field
Function	Traversing data rows
Parameter 1	Result variables passed in the query

Eighth step: Close the database

Type description function Mysqli_close function Close database connection parameter 1 incoming Mysqli_connect resource returned

A database connection is a resource type. We told you about the resource types in the previous chapters. Any type of resource that involves a number of resources is opened and closed. This ensures that PHP can process and recycle resources more efficiently.

Therefore, when the database connection succeeds, it does not need to be used. We can close this connection.

Attention:
Mysqli only learn the process of the method can be. In the object-oriented phase, the object usage of mysqli is completely discarded, but the way that the PDO objects connect to the database is used.

PHP database operation of the user to do a paging

In real projects, we write the hosts, usernames, passwords, and libraries in the configuration file.

If you write dead in the code, if the information about the database server changes, it is obviously not in the programmer's mind to modify all the code once.

In addition, in each page that needs to connect to the database. We all need to write the connection, judge the error, set the character too much trouble. And it is not conducive to reusing the code.

We can reach the goal with the Include series function we talked about earlier:

Therefore, we can do a configuration file config.php. Set all the configurations you need to use as constants, with the following code:

<?PHP//Database ServerDefine'Db_host','localhost');//Database user nameDefine'Db_user','Root');//Database PasswordDefine'db_pwd','Secret');//Library nameDefine'db_name',' Book');//Character SetDefine'Db_charset','UTF8');?>

We will extract the connection.php page, in the future need to connect to the database only need to include connection.php files. The code is as follows:

<?  'config.php'= mysqli_connect (db_host, Db_user, Db_pwd, db_name); if (Mysqli_errno ($conn)) {  
    Mysqli_error ($conn);
    Exit
}
Mysqli_set_charset ($conn, db_charset);? >

We can implement the database connection in the future by including the connection.php file directly in each file use:

Include ' connection.php ';

Finish the preparatory work above and complete the pagination next. The paging effect is as follows:

The page to implement contains the following basic elements in the paging:

Elements	Description	Notes
Home	Start at the first page of the page	The default is 1 when you go in with get arguments.
Previous page	Current page minus 1	If the page number is the first page minus 1, the first page should be
Next page	Current page plus 1	If the last
Last	Last page	Total number of bars divided by the number of pages displayed per page
Current page	The page number that is currently located	is the current page number
Total pages	How many pages are there?	Total number of bars divided by the number of displays per page

When we control page numbers, it is the page number control that is achieved by passing in the page number value through the URL address bar. After page.php the page number, we can figure out more useful information. The effect of URL control paging is as follows:

In the code implementation, it is the paging that is actually implemented by the offset (offset) and the amount (num) after the limit.

Page Number	get value in URL	limit offset, quantity
1th page	1	0,5
2nd page	2	5,5
3rd page	3	10,5
Page n	N	(n-1) *5,5

We use code to achieve business:

(1) Calculate the required parameters for paging

(2) SQL statements

We said before that the core of the excessive page is to control the number of displays per page by using offset and NUM in the SQL statement.

We also listed the specific formula, we convert the company into code as follows:

$num = 5; $offset = ($page-1) * $NUM;

We apply $num and $offset to the SQL statement:

$sql = "Select Id,username,createtime,createip from the user order by id desc limit $offset, $num";

Control the paging value in the URI

Echo'<tr>&LT;TD colspan="5"> <a href="page.php?page=1"> Home </a> <a href="page.php?page= '. ($page-1). '"> Prev </a> <a href="page.php?page= '. ($page + 1). '"> Next </a> <a href="page.php?page= '. $total. '"> Last </a>Current is the first'. $page.'Page Total'. $total.'page</td> </tr>';

We end up with the overall business in tandem to achieve the final result, the code is as follows:

Include'connection.php'; $count _sql='select COUNT (ID) as C from user'; $result=mysqli_query ($conn, $count _sql); $data=Mysqli_fetch_assoc ($result);//get total number of users$count = $data ['C']; $page= Isset ($_get['page']) ? (int) $_get['page'] :1;/*if (isset ($_get[' page '))) {$page = (int) $_get[' page ');} else {$page = 1;}*///number of displays per page$num=5;//Get Total Pages$total = Ceil ($count/$num);if($page <=1) {$page=1;}if($page >=$total) {$page=$total;} $offset= ($page-1) *$num; $sql="Select Id,username,createtime,createip from the user order by id desc limit $offset, $num"; $result=mysqli_query ($conn, $sql);if($result &&mysqli_num_rows ($result)) {    //data is present and the data is displayed in a loop.Echo'<table width= "border=" 1 ">';  while($row =Mysqli_fetch_assoc ($result)) {echo'<tr>'; Echo'<td>'. $row ['username'] .'</td>'; Echo'<td>'. Date'y-m-d h:i:s', $row ['Createtime']) .'</td>'; Echo'<td>'. Long2ip ($row ['Createip']) .'</td>'; Echo'<td><a href= "edit.php?id='. $row ['ID'] .'"> Edit Users </a></td>'; Echo'<td><a href= "delete.php?id='. $row ['ID'] .'"> Delete users </a></td>'; Echo'</tr>'; } Echo'<tr><td colspan= "5" ><a href= "page.php?page=1" > Home </a> <a href= "page.php?page='. ($page-1) .'"> Prev </a> <a href=" page.php?page='. ($page +1) .'"> Next </a> <a href=" page.php?page='. $total.'"> Last </a> current is the first'. $page.'Page Total'. $total.'page </td></tr>'; Echo'</table>';} Else{echo'No Data';} Mysqli_close ($conn);

PHP database operation data Display garbled the ultimate solution

PHP connection MySQL garbled is the development process, this is the development of novice frequently encountered problems. According to the actual problems encountered, will be garbled problem, summed up into 9 points to completely solve the problem of garbled after the connection.

The core idea of solving the garbled problem is to make sure that the code must be unified in many different file systems.

These 9 points are:

1.html encoding consistent with MySQL encoding

2.PHP encoding consistent with MySQL encoding

3. If there is a header to send the character set, just like the database

4.<meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 "/> To be consistent with the text encoding of the page

5. The character set of database repository should be unified

6. The character set of the table should be unified

7. The character set of the column must be uniform (table set, column default Write table)

8. Connection, check the character set to unify

9. The character set of the result set should be unified

PHP Operation MySQL Database