PHP and MySQL (15)

PHP Configuration

• Referencing mysqli extensions

Extension = Php_mysqli.dll

• No absolute path required

If not enabled, then the reference mysqli extension must use an absolute path reference

Extension_dir = "ext"

Creating and disconnecting Links

$mysqli new mysqli (); // instantiate the Mysqli class $mysqli , connect ("localhost", "root", "123"); // Link Database $mysqli , select_db ("text"); // Select text Database $mysqli -close (); // Close Link

Get error message

• Get the error code

$mysqli New mysqli ("localhost", "root", "123", "Test"); Echo $mysqli , errno; // no error returned 0

• Get error message

$mysqli New mysqli ("localhost", "root", "123"); // instantiate the Mysqli class $mysqli , select_db ("text"); // Select text Database if ($mysqli - errno) {echo$mysqli -error; // Unknown database ' text ' has no text databases }$mysqli Close (); // Close Link

• Store link information in a separate file

// mysql.connect.php file <? PHP $mysqli New mysqli ("localhost", "root", "123", "Test"); >

• Include this file if necessary

<? PHP include "Mysql.connect.php"; // Call the mysql.connect.php file ?>

Interacting with the database

• Send a query to the database

• Get Data

Query () method

Mysqli_store_result higher memory and processing requirements, querying the entire result set (default)

Mysqli_use_result lower memory requirements, query several rows of result sets

$mysqli=NewMysqli ("localhost", "root", "123", "Test");//link the database server and select the test database$query= "SELECT ID, Name, age from Xiu";//Create a query statement$result=$mysqli, Query ($query,Mysqli_store_result);//the $query () method is responsible for sending query to the database while(List($id,$name,$age) =$resultFetch_row ()) {//The Fetch_row () method generates an array of the obtained values    printf("%d*%d*%d",$id,$name,$age);}$mysqliClose ();//Close Database Link

• Inserting, deleting, or updating data

$mysqli New mysqli ("localhost", "root", "123", "Test"); // link the database server and select the test database $query = "ALTER TABLE Xiu Add column birdate date"; // Create a query statement $mysqli , query ($query); // the $query () method is responsible for sending query to the database Echo $mysqli , affected_rows; // How many lines are affected by the hint $mysqli -close (); // Close Database Link

• Free up query memory

Sometimes it is possible to get a particularly large result set that consumes a lot of memory and frees up memory using the free () method

$mysqli=NewMysqli ("localhost", "root", "123", "Test");//link the database server and select the test database$query= "SELECT ID, Name, age from Xiu";//Create a query statement$result=$mysqli, Query ($query,Mysqli_store_result);//the $query () method is responsible for sending query to the database while(List($id,$name,$age) =$resultFetch_row ()) {//The Fetch_row () method generates an array of the obtained values    printf("%d*%d*%d",$id,$name,$age);}$mysqli-Free ();//Freeing Memory

• Parsing query Results

• Put the results in an object

The Fetch_object () method places the result set into the object

$mysqli=NewMysqli ("localhost", "root", "123", "Test");//link the database server and select the test database$query= "SELECT ID, Name, age from Xiu";//Create a query statement$result=$mysqli, Query ($query);//the $query () method is responsible for sending query to the database while($xiu=$resultFetch_object ()) {//The Fetch_object () method places the result set into the object    $id=$xiu-ID; $name=$xiu-name; $age=$xiu-Age ; printf("id:%d,name:%s,age:%d",$id,$name,$age);}$mysqliClose ();//Close Database Link

• Using indexed arrays and associative arrays to get results

Fetch_array () Putting the result set into an array

MYSQLI_ASSOC returns the row as an associative array, the key is represented by the field, and the value is represented by the field contents

Mysqli_num returns an array of rows as a numeric index, and the attributes of the elements are determined in the order in which they are queried

Mysqli_both returns rows as associative arrays and array index arrays

$mysqli=NewMysqli ("localhost", "root", "123", "Test");//link the database server and select the test database$query= "SELECT ID, Name, age from Xiu";//Create a query statement$result=$mysqli, Query ($query);//the $query () method is responsible for sending query to the database while($xiu=$resultFetch_array (MYSQLI_ASSOC)) {//the Fetch_array () method puts the result set into an array    $id=$xiu["id"]; $name=$xiu["Name"]; $age=$xiu["Age"]; printf("id:%d,name:%s,age:%d",$id,$name,$age);}$mysqliClose ();//Close Database Link

• Determine the selected row and the affected row

• Determine the number of rows returned

The Num_rows property returns how many rows of data are queried

$mysqli New mysqli ("localhost", "root", "123", "Test"); // link the database server and select the test database $query = "SELECT ID, Name, age from Xiu"; // Create a query statement $result $mysqli , query ($query); // the $query () method is responsible for sending query to the database Echo $result , num_rows; // Num_rows Property Query returns how many rows of data $mysqli -close (); // Close Database Link

• Determine the number of rows affected

 affected_rows property returns the number of rows affected by an INSERT, update, or delet query

$mysqli New mysqli ("localhost", "root", "123", "Test"); // link the database server and select the test database $query = "INSERT into Xiu values (4, ' user ', 20)"; // Create a query statement $mysqli , query ($query); // the $query () method is responsible for sending query to the database Echo $mysqli , affected_rows; // Affected_rows property returns the number of rows affected $mysqli -close (); // Close Database Link

• Processing prepared statements

Bundle parameters

$mysqli=NewMysqli ("localhost", "root", "123", "Test");//link the database server and select the test database$query= "INSERT into Xiu values (?,?,?)";//Create a query that corresponds to a placeholder (?)$stmt=$mysqliStmt_init ();//To Create a statement object$stmt-Prepare ($query);//preparing statements for execution$stmt-Bind_param ("DSD",$id,$name,$age);//Binding Parameters$id= 5;$name= "4";$age= 3;$stmtExecute ();//EXECUTE Statement$stmtClose ();//Recover Statement Resources$mysqliClose ();//Close Database Link

• Bundle variables

$mysqli=NewMysqli ("localhost", "root", "123", "Test");//link the database server and select the test database$query= "Select Id,name,age from Xiu";//Create a query that corresponds to a placeholder (?)$stmt=$mysqliStmt_init ();//To Create a statement object$stmt-Prepare ($query);//preparing statements for execution$stmtExecute ();//EXECUTE Statement$stmt-Bind_result ($id,$name,$age);//Binding result Parameters while($stmtFetch ()) {//fetch () gets each row that prepares the statement result    printf("id:%d,name:%s,age:%d",$id,$name,$age);}$stmtClose ();//Recover Statement Resources$mysqliClose ();//Close Database Link

PHP and MySQL