mysql

databases test; use test; create table student (id int auto_increment , name varchar(20), num int, primary key(id) ) ; insert into student (name, num) values (‘one‘, 111), (‘two‘, 222); select name, num from student order by name asc;

connect

<?php
$link = new mysqli(‘localhost‘, ‘root‘, ‘YOUR_PASSWD‘, ‘shop‘);
if ($link->connect_errno) {
    echo ‘<font color="#c00"> fail:‘.$link->connect_error.‘</font>‘;
    exit();
} else {
    echo ‘<font color="#000"> succeed </font>‘;
}
?>

<?php
$link = new mysqli(‘localhost‘, ‘root‘, ‘YOUR_PASSWD‘);
if ($link->connect_errno) {
    echo ‘<font color="#c00"> fail:‘.$link->connect_error.‘</font>‘;
    exit();
} else {
    echo ‘<font color="#000"> succeed </font>‘;
}
$link->select_db(‘shop‘);
?>

query

query(..., MYSQLI_STORE_RESULT);
结果作为一个缓存集返回, 可以立即对整个结果集导航 (默认设置), 会增加内存, 可以很快知道返回了多少行

query(..., MYSQLI_USE_RESULT);
非缓存集, 根据需要从服务器获取结果集, 对于较大的结果集, 可以提高性能, 响应时间快

mysql_query() 不能传二进制 BLOB 字段，因为二进制信息中的\0 会被误判为语句结束，mysql_real_query() 则可以

<?php
$query = ‘show databases;‘;
$link->query($query);
if ($link->errno == 0) {
    echo ‘show databases succeed‘, ‘<br>‘;
} else {
    echo ‘show databases fail‘, $link->error, ‘<br>‘;
}
?>

<?php
$link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
if ($link->connect_errno) {
    echo ‘fail: ‘, $link->connect_error;
    exit();
}

$query = ‘select name, num from student order by name asc‘;
$result = $link->query($query);
if ($link->errno) {
    echo ‘query fail: ‘, $link->error, ‘<br>‘;
}

while (list($name, $num) = $result->fetch_row()) {
    echo ‘name = ‘, $name, ‘ num = ‘, $num, ‘<br>‘;
}

while ($row = $result->fetch_row()) {
    echo ‘name = ‘, $row[0], ‘ num = ‘, $row[1], ‘<br>‘;
}

while ($row = $result->fetch_object()) {
    echo ‘name = ‘, $row->name, ‘ num = ‘, $row->num, ‘<br>‘;
}

echo ‘there are ‘, $link->affected_rows, ‘ affected rows <br>‘;

$result->free();

$link->close();
?>

prepare

传统的 query() 使用循环机制, (重复解析)

<?php
$link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
if ($link->connect_errno) {
    echo ‘fail: ‘, $link->connect_error;
    exit();
}

$query = ‘select name, num from student order by name asc‘;

$stmt = $link->stmt_init();

$stmt->prepare($query);

....

$stmt->close();
$link->close();
?>

1. 绑定参数
   

   insert
   

   i   所有整数类型
   d   double 和 float 类型
   s   其他类型  如字符串
   

   <?php
   $link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
   if ($link->connect_errno) {
       echo ‘fail: ‘, $link->connect_error;
       exit();
   }
   
   $query = ‘insert into student (name, num) values (?, ?)‘;
   
   $stmt = $link->stmt_init();
   
   $stmt->prepare($query);
   
   $stmt->bind_param(‘si‘, $name, $num);
   
   $name_array = array(‘test1‘, ‘test2‘);
   $num_array = array(001, 002);
   
   $x = 0;
   
   while ($x < sizeof($name_array)) {
       $name = $name_array[$x];
       $num  = $num_array[$x];
   
       $stmt->execute();
       $x++;
   }
   
   $stmt->close();
   $link->close();
   ?>
   

2. 绑定结果
   

   select
   

   <?php
   $link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
   if ($link->connect_errno) {
       echo ‘fail: ‘, $link->connect_error;
       exit();
   }
   
   $query = ‘select name, num from student‘;
   
   $stmt = $link->stmt_init();
   
   $stmt->prepare($query);
   
   $stmt->execute();
   
   $stmt->bind_result($name, $num);
   
   echo ‘<table border=1> <th>name</th><th>num</th>‘;
   while ($stmt->fetch()) {
       echo ‘<tr><td>‘, $name, ‘</td>‘, ‘<td>‘, $num, ‘</td></tr>‘;
   }
   echo ‘</table>‘;
   
   $stmt->close();
   $link->close();
   ?>
   

• 2015-05-31 22:40:31: 加入 insert query prepare
  

mysql

标签：