In fact, there is no desire today.-MySQLi, desire...-MySQLi_PHP tutorial

In fact, today there is no desire...-MySQLi, desire...-MySQLi. In fact, there is no desire today .. -MySQLi, desire .. -MySQLihi had a pleasant swim at noon, but in the afternoon I saw a punch in Superman, so I had no desire to learn... Force yourself to have no desire today...-MySQLi, desire...-MySQLi

Hi

At noon, I had a good swim, but in the afternoon I saw a punch in Superman, so I couldn't go out of the desire to learn... Force yourself to update something and read the book later.

1. MySQLi

II. MySQLi OOP-based programming

2.1 Usage analysis

-- Basic

MySQLi is an extended class library, essentially a class(?).

The general process is the same as that of MySQL:Connection, database selection, character set setting, SQL statement execution, close connection.

-- Link library example

/*
*Connect and select a database
*/
$ Mysqli = new mysqli ('localhost', 'root ','');
Print_r ($ mysqli); echo"
";

Echo$ Mysqli-> select_db('Test'); echo"
";

$ Mysqli2 = new mysqli ();
Print_r ($ Mysqli2-> connect('Localhost', 'root', ''); echo"
";

Print_r ($ Mysqli3 = new mysqli ('localhost', 'root', '', 'test ')); Echo"
";

There are three different methods. the method here uses the class attribute of mysqli. of course, you can also use the mysqli command to link;

$ Con = mysqli_connect (HOST, USERNAME, PASSWORD)

The result contains some information.

Mysqli Object
	(
	[Affected_rows] => 0
	[Client_info] => maid-dev-20120503-$ Id: bf9ad53b11c9a57efdb1057292d73b928b8c5c77 $
	[Client_version] = & gt; 50011
	[Connect_errno] => 0
	[Connect_error] =>
	[Errno] => 0
	[Error] =>
	[Error_list] => Array
	(
	)
	[Field_count] => 0
	[Host_info] => localhost via TCP/IP
	[Info] =>
	[Insert_id] => 0
	[Server_info] => 5.6.17
	[Server_version] = & gt; 50617
	[Stat] => Uptime: 968 Threads: 1 Questions: 24 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per secondd avg: 0.024
	[Sqlstate] = & gt; 00000
	[Protocol_version] => 10
	[Thread_id] => 11
	[Warning_count] => 0
	)

These attributes can be obtained through object attributes, such

Echo $ mysqli-> client_info; echo"
";

You can also find these items through the corresponding method.

Header ('content-type: text/html; charset = utf-8 ');
// 1. establish a connection to MySQL data
// $ Mysqli = new mysqli ('localhost', 'root', 'root ');
/// Print_r ($ mysqli );
/// 2. open the specified database
// $ Mysqli-> select_db ('test ');
// $ Mysqli = new mysqli ();
// $ Mysqli-> connect ('127. 0.0.1 ', 'root', 'root ');
// Print_r ($ mysqli );

// Open a specified database while establishing a connection
$ Mysqli = @ new mysqli ('localhost', 'root', 'root', 'test ');
// Print_r ($ mysqli );
// $ Mysqli-> connect_errno: get the error code generated by the connection.
// $ Mysqli-> connect_error: get the error message generated by the connection.
If ($ mysqli-> connect_errno ){
Die ('connect Error: '. $ mysqli-> connect_error );
}
Print_r ($ mysqli );
Echo'

';
Echo 'Client information: '. $ mysqli-> client_info .'
';
Echo $ mysqli-> get_client_info ().'
';
Echo 'Client version: '. $ mysqli-> client_version .'
';
Echo' ';
Echo 'server information: '. $ mysqli-> server_info .'
';
Echo $ mysqli-> get_server_info ();
Echo' ';
Echo 'server version: '. $ mysqli-> server_version .'
';

Echo'

';

-- Character set example

// 1. establish a connection to MySQL
$ Mysqli = @ new mysqli ('localhost', 'root', 'root', 'test ');
If ($ mysqli-> connect_errno ){
Die ('connect Error: '. $ mysqli-> connect_error );
}
// 2. set the default client encoding method utf8
$ Mysqli-> set_charset ('utf8 ');

// 3. execute SQL query
$ SQL = < Create table if not exists mysqli (
Id tinyint unsigned AUTO_INCREMENT KEY,
Username VARCHAR (20) NOT NULL
);
EOF;
$ Res = $ mysqli-> query ($ SQL );
Var_dump ($ res );

/*
SELECT/DESC/DESCRIBE/SHOW/EXPLAIN: the mysqli_result object is returned when execution is successful. if execution fails, false is returned.
If other SQL statements are executed successfully, true is returned; otherwise, false is returned.
*/
// Close the connection
$ Mysqli-> close ();

Note that utf8 is used in the database instead of UTF-8;

2.2 insert record operation

Add.

-- Connect. php

Because a series of operations to connect to the database are commonly used, for this, our simple method isEncapsulated and called everywhere

Require_once 'connect. php ';

Connect. php

/*
*Connect and select a library (header) file
*/
$ Mysqli = new mysqli ('localhost', 'root', '', 'test ');
If ($ mysqli-> connect_errno ){
Die ('connect Error: '. $ mysqli-> connect_error );
} Else {
Echo 'Client information: '. $ mysqli-> client_info .'
';
}
$ Mysqli-> set_charset ('utf8 ');

-- Add

/*
* Insert data into the database
*/

Require_once 'connect. php ';

$ SQL = "insert mysqli (username) value ('Tom ')";
Echo $ mysqli-> query ($ SQL );

Here, a single SQL statement is executed.

Alternatively, add a judgment and output an error message.

If ($ res ){
Echo $ mysqli-> insert_id;
} Else {
Echo 'error'. $ mysqli-> ERROR;
}

Alternatively, insert multiple records.

$ SQL = "insert mysqli (username) value ('sdaf'), ('Andy ')";

2.3 update records

Update.

$ SQL = "update test set id = id + 10 ";
$ Mysqli-> query ($ SQL );

2.4 delete

Delete

$ SQL = "delete from mysqli where id> = 2 ";

--

In particular, affected_rows returns three types of data:

-1 SQL statement problems;

0 is not affected;

> = 0 number of affected items.

-- Summary

Header ('content-type: text/html; charset = utf-8 ');
$ Mysqli = new mysqli ('localhost', 'root', 'root', 'test ');
If ($ mysqli-> connect_errno ){
Die ('connect ERROR: '. $ mysqli-> connect_error );
}
$ Mysqli-> set_charset ('utf8 ');

// Execute SQL query
// Add record
// Execute a single SQL statement. Only one SQL statement can be executed.
// $ SQL = "INSERT user (username, password) VALUES ('king', 'king ');";
// $ SQL. = "DROP TABLE user ;";
$ SQL = "INSERT user (username, password) VALUES ('queen1', 'queen1'), ('queen2', 'queen2'), ('queen3 ', 'queen3'), ('queen4', 'queen4 ')";
$ Res = $ mysqli-> query ($ SQL );
If ($ res ){
// Obtain the AUTO_INCREMENT value generated by the previous insert operation.
Echo 'Congratulations, you have registered successfully. you are the user of the website '. $ mysqli-> insert_id .'.
';
// Obtain the number of affected records generated by the previous operation
Echo 'has'. $ mysqli-> affected_rows. 'The record is affected ';
} Else {
// Obtain the error code and error message generated in the previous step.
Echo 'error'. $ mysqli-> errno. ':'. $ mysqli-> ERROR;
}
Echo'

';

// Add the table age to 10
$ SQL = "UPDATE user SET age = age + 10 ";
$ Res = $ mysqli-> query ($ SQL );
If ($ res ){
Echo $ mysqli-> affected_rows. 'record updated ';
} Else {
Echo "ERROR". $ mysqli-> errno. ':'. $ mysqli-> error;
}
Echo'

';

// Delete the user with the table id <= 6
$ SQL = "DELETE FROM user WHERE id <= 6 ";
$ Res = $ mysqli-> query ($ SQL );
If ($ res ){
Echo $ mysqli-> affected_rows. 'records deleted ';
} Else {
Echo "ERROR". $ mysqli-> errno. ':'. $ mysqli-> error;
}
// Close the connection to MySQL
$ Mysqli-> close ();

2.5 query

Note that select is used, so the returned result set is print_r or var_dump that can be printed.

So here we will talk about the selection of the returned result set.

Header ('content-type: text/html; charset = utf-8 ');
$ Mysqli = new mysqli ('localhost', 'root', 'root', 'test ');
If ($ mysqli-> connect_errno ){
Die ('connect ERROR: '. $ mysqli-> connect_error );
}
$ Mysqli-> set_charset ('utf8 ');
$ SQL = "SELECT id, username, age FROM user ";
$ Mysqli_result = $ mysqli-> query ($ SQL );
// Var_dump ($ mysqli_result );
If ($ mysqli_result & $ mysqli_result-> num_rows> 0 ){
// Echo $ mysqli_result-> num_rows;
// $ Rows = $ mysqli_result-> fetch_all (); // Obtain all records in the result set. by default, two-dimensional
// Index + index form
// $ Rows = $ mysqli_result-> fetch_all (MYSQLI_NUM );
// $ Rows = $ mysqli_result-> fetch_all (MYSQLI_ASSOC );
// $ Rows = $ mysqli_result-> fetch_all (MYSQLI_BOTH );
// $ Row = $ mysqli_result-> fetch_row (); // returns a record in the result set as an index array
// Print_r ($ row );
// Echo'

';
// $ Row = $ mysqli_result-> fetch_assoc (); // Obtain a record in the result set and return it as an associated array
// Print_r ($ row );
// Echo' ';
// $ Row = $ mysqli_result-> fetch_array (); // both have
// Print_r ($ row );

// Echo' ';
// $ Row = $ mysqli_result-> fetch_array (MYSQLI_ASSOC );
// Print_r ($ row );

// Echo' ';
// $ Row = $ mysqli_result-> fetch_object ();
// Print_r ($ row );
// Echo' ';
//// Move the internal pointer of the result set
// $ Mysqli_result-> data_seek (0 );
// $ Row = $ mysqli_result-> fetch_assoc ();
// Print_r ($ row );

// Print_r ($ rows );

While ($ row = $ mysqli_result-> fetch_assoc ()){
// Print_r ($ row );
// Echo'

';
$ Rows [] = $ row;
}
Print_r ($ rows );

// Release the result set
$ Mysqli_result-> free ();


} Else {
Echo 'query error or no record in the result ';
}
$ Mysqli-> close ();

Yi hi had a good swim at noon, but in the afternoon, after reading a fist of Superman, I had a desire to learn... Force yourself...