The implementation method of PHP manipulating Mysqli database _php example

Source: Internet
Author: User
Tags sql injection stmt

Add MySQL (i) support from php5.0, and new features are added in the form of objects

I express the meaning of the improvement of more functions, high efficiency, stability

Compile-time parameters:

./configure--with-mysql=/usr/bin/mysql_config \ #使用 mysql clientlibrary (libmysql) build
--with-mysqli=mysqlnd \ #使用 MySQL Native dirver that mysqlnd
--with-pdo-mysql=mysqlnd #使用 mysql Native dirver that mysqlnd

Since the copyright issue began with php5.3 PHP began to replace Libmysql.dll with MYSQLND
MYSQLND is a MySQL database driver developed by Zend Company, which has been improved in comparison with all aspects.

#使用mysqlnd编译

./configure--with-mysql=mysqlnd--with-mysqli=mysqlnd--with-pdo-mysql=mysqlnd plus your parameters

Mysqli procedures, object methods are supported

The MYSQLI offers three classes:

1, mysqli and connection-related
2, Mysqli_result processing result set
3, mysqli_stmt pretreatment class

#设置字符集
Set_charset

#获取字符集
Character_set_name

Getting database objects

Create Mysqli Object Mode 1
//Mask connection error generated
$mysqli = new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');

Only functions can be used to determine if the connection succeeds
if (Mysqli_connect_errno ())
{
  echo mysqli_connect_error ();
}

Create Mysqli Object Mode 2 You can set some parameters
$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ' , ' Test '); 

Query: Failed to return false,select successful return of the result set object, others return true not false, meaning that SQL execution succeeded

No result set sample

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');

$sql = "INSERT into Limove (' name ', ' order ') VALUES (' AA ', one)";
$rst = $mysqli->query ($sql);

$sql = "Delete from Limove where id = 221";
$rst = $mysqli->query ($sql);

if ($rst = = False)
{
  ee ($mysqli->errno);
  EE ($mysqli->error);
}

#影响条数
ee ($mysqli->affected_rows);
#插入的id
ee ($mysqli->insert_id);

EE ($mysqli);

Have result set

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');

$sql = "SELECT * from Limove as limove_as";

$result = $mysqli->query ($sql);
if ($result = = False)
{
  ee ($mysqli->errno);
  EE ($mysqli->error);
}

#行数
ee ($result->num_rows);

#列数
ee ($result->field_count);

#字段个数
ee ($result->field_count);

#获取所有字段的信息
$field _arr = $result->fetch_fields ();

#移动字段的指针
//$result->field_seek (1);

#依次获取字段的信息 while
($field = $result->fetch_field ())
{
  ee ($field);
}

#移动记录指针
$result->data_seek (1);

#一次获取所有数据
$data = $result->fetch_all (MYSQLI_ASSOC);

#关联数组方式获取结果集
$data = Array ();

$result->data_seek (0); #重置指针到起始 while
($row = $result->fetch_assoc ())
{
  $data [] = $row;
}

EE ($data);


$result->free ();
$mysqli->close ();

Execute multiple statements at a time multiquery (not recommended)

No result set, at which point the affected_rows can only get the number of bars that last effect

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');


$sql _arr = Array (
  ' INSERT into Limove (ID, ' name ', ' order ') VALUES (NULL, 1, 2) ',    
  ' INSERT into Limove (ID, ' name ', ' or Der ') VALUES (NULL, 1, 222) ',    
  ' delete from Limove where ' order ' = 2 ',    
);

$sql = implode ('; ', $sql _arr);

$result = $mysqli->multi_query ($sql);
if ($result = = False)
{
  ee ($mysqli->errno);
  EE ($mysqli->error);
}

$mysqli->close ();

Have result set

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');

$sql _arr = Array (
  ' show Tables ',    
  ' desc select * To Limove ',    
  ' show create Table Limove '    
);

$sql = implode ('; ', $sql _arr);

$rst = $mysqli->multi_query ($sql);

if ($rst = = False)
{
  ee ($mysqli->errno);
  EE ($mysqli->error);
}

do{
  $result = $mysqli->store_result (); #获取当前光标所在的结果集
  
  $data = $result->fetch_all ();
  
  EE ($data);
  
} while ($mysqli->next_result ()); #光标移动到下一个结果集

$mysqli->close ();

Transaction processing:

 $mysqli =new mysqli ("localhost", "root", "123456", "xsphpdb");

  Transaction processing $mysqli->autocommit (0);

  $error =true;

  $price = 50;
  
  $sql = "Update en set ye=ye-{$price} where name= ' Zhangsan '";

  $result = $mysqli->query ($sql);
    if (! $result) {$error =false;
  echo "from Zhang San to fail";
      }else{if ($mysqli->affected_rows==0) {$error =false;  
    echo "John's money has not changed";
    }else{echo "from the John account to relay success!";

  } $sql = "update en set ye=ye+{$price} where name= ' Lisi1 '";

  $result = $mysqli->query ($sql);
    if (! $result) {$error =false;
  echo "Transferred from Dick to failure";
      }else{if ($mysqli->affected_rows==0) {$error =false;  
    echo "Dick's money has not changed";
    }else{echo "to Lee four account into success!";
    } if ($error) {echo "Transfer successful!";
  $mysqli->commit ();
    }else{echo "Transfer failed!";
  $mysqli->rollback ();
  } $mysqli->autocommit (1);
$mysqli->close (); 

Mysqli_stmt:mysqli preprocessing Class (recommended): a prepared statement that the server side compiles only once SQL

The same functionality can be achieved with mysqli and Mysqli_result

Advantages: high efficiency, suitable for statements with the same data only a different situation, can prevent the generation of SQL injection

mysqli_stmt Example: Non-SELECT statement

Require ' fns.php ';

Create a Mysqli object way 
$mysqli = @new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');

Only functions can be used to determine if the connection succeeds if
(Mysqli_connect_errno ())
{
  echo mysqli_connect_error ();
  Die;
}

$mysqli->set_charset (' UTF8 ');

$sql = "INSERT into limove values (?,?,?)"; The statement has the same value



//mysqli there are direct methods available
$stmt = $mysqli->prepare ($sql);

Binding parameters
$stmt->bind_param (' ISS ', $id, $name, $order);

For ($i =0 $i <5; $i + +) {
  $id = 0;
  $name = ' name ';
  $order = Mt_rand (1, 1000);
  $stmt->execute ();

}

Last ID
ee ($stmt->insert_id);

Number of rows affected: The last performed
ee ($stmt->affected_rows);

Error number
EE ($stmt->errno);

Error message
ee ($stmt->error);

More information
ee ($stmt) can be seen in the stmt object;

EEE ($MYSQLI);

MYSQLI_STMT Example: SELECT statement 1

Require ' fns.php ';

Create a Mysqli object way 
$mysqli = @new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');

Only functions can be used to determine if the connection succeeds if
(Mysqli_connect_errno ())
{
  echo mysqli_connect_error ();
  Die;
}

$mysqli->set_charset (' UTF8 ');

$sql = "SELECT * from Limove where ID

The above PHP manipulation Mysqli Database Implementation method is small series to share all the content, hope to give you a reference, but also hope that we support cloud habitat community.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.