PHP database connection mysql and mysqli comparative analysis, mysqlmysqli_PHP tutorial

Comparative analysis of PHP database connection mysql and mysqli, mysqlmysqli

1. The concepts of mysql and mysqli are related

1. Mysql and mysqli are both PHP function sets and have little connection with the mysql database.

2. Before the php5 version, the mysql function of php was generally used to drive the mysql database. For example, the function of mysql_query() is process-oriented. 3. After the php5 version, the function function of mysqli was added, which has a certain meaning. Technically speaking, it is an enhanced version of the mysql system function, which is more stable, efficient and secure. Corresponding to mysql_query() is mysqli_query(), which is object-oriented and uses objects to operate and drive the mysql database

2. The difference between mysql and mysqli

1. Mysql is a non-persistent connection function. Mysql will open a connection process every time it is connected.

2. Mysqli is a permanent connection function. Running mysqli multiple times will use the same connection process, thus reducing server overhead. mysqli encapsulates some advanced operations such as transactions, and also encapsulates many available methods in the DB operation process.

3. Usage of mysql and mysqli

1. mysql (process mode):

$conn = mysql_connect('localhost', 'user', 'password');　//连接mysql数据库　　 
 
mysql_select_db('data_base'); //选择数据库　 
 
$result = mysql_query('select * from data_base');//第二个可选参数，指定打开的连接　　 
 
$row = mysql_fetch_row( $result ) ) //只取一行数据　　 
 
echo $row[0]; //输出第一个字段的值　

PS: mysqli operates in a procedural manner. Some functions must specify resources, such as mysqli_query (resource identifier, SQL statement), and the parameter of the resource identifier is placed in front, while mysql_query (SQL statement, 'resource identifier' ) is optional, and the default value is the last opened connection or resource.

2. mysqli (object mode): 　

<pre name="code" class="php">$conn = new mysqli('localhost', 'user', 'password','data_base');　　 
//要使用new操作符，最后一个参数是直接指定数据库　　 
//假如构造时候不指定，那下一句需要$conn -> select_db('data_base')实现　　 
 
$result = $conn -> query( 'select * from data_base' );　　 
 
$row = $result -> fetch_row(); //取一行数据　　 
 
echo row[0]; //输出第一个字段的值 

Using new mysqli('localhost', usenamer', 'password', 'databasename'); will report an error with the following prompt:

Fatal error: Class 'mysqli' not found in ...

Generally, mysqli is not enabled because the mysqli class is not enabled by default. You need to change php.ini under win and remove the ";" in front of php_mysqli.dll. Mysqli must be compiled into it under Linux.

4. Examples of mysql and mysqli

1. mysql

<span style="font-size:14px;">$con=mysql_connect($dbhostip,$username,$userpassword); 
$db = mysql_select_db($dbdatabasename,$con); 
//执行语句 
$qres=mysql_query("SELECT id,GoodsName FROM user"); 
//提取一条数据 
$row=mysql_fetch_row($result); 
//mysql_fetch_row只能提取出查询结果的第一条记录 
//提取多条记录 
$reslist = array(); 
$i=0; 
while($row = mysql_fetch_row($res)){ 
  $reslist[$i] = $row; 
  $i++; 
 } 
mysql_close($con); 
</span> 

//mysql_fetch_row  提取的结果是没有查询中的字段名了（也就是没有键id,GoodsName，只有值）



//mysql_fetch_assoc 提取的结果有键值


//mysql_fetch_array提取的结果有键值，是前面两种的综合

Use @ (error control operator) before mysql_connect(), mysql_select_db() and other functions to ignore the error message generated by the system, and then we use die() to customize the error message;
For the return value of the mysql_query() function, if the executed statement has a return value (such as SELECT, SHOW, DESCRIBE, etc.), the corresponding data (on success) or FALSE (on failure) will be returned; if the executed statement has no return value (such as DELETE, DROP, INSERT, UPDATE, etc.), it returns TRUE (on success) or FALSE (on failure).

2. mysqli

$db=new mysqli($dbhostip,$username,$userpassword,$dbdatabasename); 
  
if(mysqli_connect_error()){  
  
　　echo 'Could not connect to database.';  
  
　　exit; 
  
} 
  
$result=$db->query("SELECT id,GoodsName FROM user"); 
  
$row=$result->fetch_row(); 

5. Examples of mysqli usage in php

<&#63;php 
  $variable = $_POST['variable']; //Post从表单中提取变量 
  if(!$variable) //如果变量为空，输出错误信息并退出 
  { 
    echo 'You hava not entered search details.Please go back and try again.'; 
    exit; 
  } 
  if(!get_magic_quotes_gpc()) //该函数用于判断get_magic_quotes_gpc是否开启，get_magic_quotes_gpc参数是用于确定是否将从post，get，cookie过来的数据增加转义字符和从数据库出来的数据去掉转义字符 
  { 
    $variable = addlashes($variable);//在特殊文本字符前增加转义字符 
    //stripslashes()用于在去掉转义字符 
  } 
  $localhost = 'hostname';//主机名 
  $user = 'username';//用户名 
  $pwd = 'password';//密码 
  $db = 'databasename';// 
  @$link = new mysqli($localhost,$user,$pwd,$db);//连接数据库 
  if(mysqli_connect_errno())//如果数据库连接失败，输出错误信息并退出 
  { 
    echo 'Error: Coulid not connect to database. Please try again later.'; 
    exit; 
  } 
  $query = "SELECT row from table where some situation";//查询语句 
  $result = $link -> query($query);//查询并返回结果 
  $num_results = $result -> num_rows; //结果行数 
  echo "<p>Number of row found: ". $num_results ."</p>";//输出行数 
   
  for($i = 0;$i < $num_results;$i++)//循环输出每组元素 
  { 
    $row = $result -> fetch_assoc();//提取元素，一次一行，fetch_assoc()提取出的元素，有属性以及值 
    echo stripslashes($row['attributename']);//按属性(键)提取值 
    echo "<br/>"; 
  } 
  $result -> free();//释放内存 
  $link -> close();//断开数据库连接 
&#63;> 

The above is about the difference and usage of mysql and mysqli in PHP database connection. I hope it will be helpful to everyone's learning.

Articles you may be interested in:

• Instructions on adding the extension=php_mysqli.dll directive in php.ini
• Some knowledge about the difference between mysqli and mysql in php Point analysis
• PHP Warning: PHP Startup: Unable to load dynamic library D:/php5/ext/php_mysqli.dll
• PHP5 mysqli prepare statement usage instructions
• php How to connect to the database after enabling the mysqli extension
• How to solve the problem of missing mysqli extension in phpmyadmin
• Sharing instructions for using PHP mysql and mysqli transactions
• PHP operation mysqli (sample code)
• PHP database operation based on Mysqli database operation library