PHP (14) Use mysqli to operate MySQL

1. Connect to MySQL Database

<?php$mysqli = new Mysqli ("localhost", "root", "123456", "MyDB"), if ($mysqli->connect_errno) {echo "Failed to Co Nnect to MySQL: ". $mysqli->connect_error;} else{echo "Success connected!";}

2. Executing DDL statements

SQL statements can be executed by using the query method of the $mysqli object created above, and the SQL statement is passed as a string to the query () method

Query () supports caching execution results from statement execution to the client, which is more common than the real_query () and Multi_query () methods.

$mysqli->query ("drop table if exists test"), $mysqli->query ("CREATE TABLE test (ID int primary key auto_increment)") ; $mysqli->query ("INSERT into test values (1)");

If you need to execute multiple SQL statements consecutively, you must use Multi_query (), with multiple SQL statements separated by semicolons.

$sql = "INSERT into test values (+); select * from test;"; $mysqli->multi_query ($sql); $res = $mysqli->store_result (); Var_dump ($res->fetch_all (MYSQLI_ASSOC)); $res- >close ();

3. Execute the query statement

The $mysqli object executes a query statement by querying a result object $res, which caches the results of the query, and can return one row of data at a time through the $res object's Fetch_assoc () method, which is stored in an indexed array. Data field names can be used as indexes to obtain the corresponding data

$res = $mysqli->query ("SELECT * from Test"), if ($res! = null) {while ($row = $res->fetch_assoc ()) {echo $row ["id"]; echo GetType ($row ["id"]);//string type}}

Note that the Mysqli query () method executes a statement that is not precompiled, the result returned by query () is the string type (string), and if you want the result to return the correct PHP numeric type, you can add a property MYSQLI_OPT_INT_AND_FLOAT_NATIVE to the Mysqli

$mysqli->options (mysqli_opt_int_and_float_native, 1);

4. Execute precompiled statements

You can specify parameters dynamically for an SQL statement by executing a precompiled statement.

$stmt = $mysqli->prepare ("INSERT into test values (?)"); /precompiled SQL Statement $id = 9, $stmt->bind_param ("i", $id);//bind parameter $stmt->execute ();//Execute SQL Statement $stmt->close ();//Close Precompiled statement, Release resources, remember to do not forget to do

The values returned by the query results of statements executed by Precompilation are automatically converted to the correct type (not all string types) compared to SQL statements that are not precompiled.

$stmt = $mysqli->prepare ("SELECT * from Test"), $stmt->execute (), $res = $stmt->get_result ();//Get query results while ($    row = $res->fetch_assoc ()) {echo $row ["id"]; Echo GetType ($row ["id"]);//integer instead of string}

The use of precompiled SQL statements can effectively prevent SQL injection and increase the security of database operations.

5. Execute the stored procedure

$mysqli->query ("CREATE procedure P (in Id_val int) begin INSERT INTO Test (ID) values (id_val); END; "); $mysqli->query ("Call P (10)");//execute stored procedure $res = $mysqli->query ("SELECT * from Test"); Var_dump ($res->fetch_all () );

6. Execution of transactions

Whether MySQL supports transactions depends on its storage engine, starting with MySQL5.5, the default storage engine changes to INNODB,INNODB fully supports the acid characteristics of the transaction.

$mysqli->autocommit (FALSE);//Set autocommit transaction to False$mysqli->query ("INSERT into Test values"), $mysqli Rollback ();//ROLLBACK TRANSACTION, 100 no Insert $mysqli->query ("INSERT into test values (101)"), $mysqli->commit (),//COMMIT TRANSACTION, insert only 101

7. Meta-Data metadata

Metadata is used to describe the columns in the result set, and metadata information can be obtained through the Mysqli_result interface.

$res = $mysqli->query ("SELECT * from Test"); Var_dump ($res->fetch_fields ());

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/88/15/wKiom1fpCsTjmO-3AABoRAG9HQE832.jpg-wh_500x0-wm_3 -wmp_4-s_1000397637.jpg "title=" 1.jpg "alt=" Wkiom1fpcstjmo-3aaborag9hqe832.jpg-wh_50 "/>

There are a lot of ways that are not involved, and here are just a few of the methods that are commonly used, to learn more ways to find PHP mannual yourself.

This article is from the "thick Product Thin Hair" blog, please make sure to keep this source http://joedlut.blog.51cto.com/6570198/1856749

PHP (14) Use mysqli to operate MySQL