使用 MySQLi 进行数据库操作

数据库操作 飞快学 305浏览

MySQLi 是 PHP 推荐的版本。

【最简单的CRUD操作】

R: Retrieve 读取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
require_once 'db_config.php';
 
$db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($db->connect_errno > 0)
    die('Unable to connect to database [' . $db->connect_error . ']');
 
$sql = "select * from film";
if(!$result = $db->query($sql))
    die('There was an error running the query [' . $db->error . ']');
printf("Find %d rows<br/>\n",  $result->num_rows);
while($row = $result->fetch_assoc()) 
    printf("%s, %s<br/>\n", $row['title'], $row['rating']);
$result->free(); 
?>

C:Create/Insert 插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
require_once 'db_config.php';
 
$db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if($db->connect_errno > 0)
    die('Unable to connect to database [' . $db->connect_error . ']');
 
$sql = "INSERT INTO `sakila`.`film` (`film_id`, `title`, `description`, 
  `release_year`, `language_id`, `original_language_id`, `rental_duration`, 
  `rental_rate`, `length`, `replacement_cost`, `rating`, 
  `special_features`, `last_update`) 
 VALUES (NULL, 'Big Times', 'Good', '2015', '2', '2', '3', 
 '4.99', '60', '19.99', 'G', 'Commentaries', CURRENT_TIMESTAMP);";
$db->query($sql); // return true or false
printf("Insert: affected rows: %d <br/>\n", $db->affected_rows); 
printf("The new record's id is %d <br/>\n", $db->insert_id);   
?>

D: Delete 删除

1
2
3
4
5
6
7
8
9
10
11
<?php
require_once 'db_config.php';
 
$db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if($db->connect_errno > 0)
    die('Unable to connect to database [' . $db->connect_error . ']');
 
$sql = "DELETE FROM `film` WHERE `release_year` = 2015";
$db->query($sql);
printf("Delete: affected rows: %d <br/>\n", $db->affected_rows);
?>

U:Update 更新

1
2
3
4
5
6
7
8
9
10
11
<?php
require_once 'db_config.php';
 
$db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if($db->connect_errno > 0)
    die('Unable to connect to database [' . $db->connect_error . ']');
 
$sql = "UPDATE `film` SET `release_year` = '2011' WHERE `film_id` > 1000;";
$db->query($sql);
printf("Update: affected rows: %d <br/>\n", $db->affected_rows);      
?>

【 参数绑定 bind_Param 】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
require_once 'db_config.php';
 
$db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if($db->connect_errno > 0)
    die('Unable to connect to database [' . $db->connect_error . ']');
 
$statement = 
  $db->prepare('SELECT title, rating FROM film WHERE `length`=? AND `rating`=?');
$length = 46;
$rating = 'PG-13';
$statement->bind_param('is', $length, $rating); // i integer, s string
$statement->execute();
$statement->bind_result($title, $rating);
while($statement->fetch())
    printf("%s, %s<br/>\n", $title, $rating);
$statement->free_result();
?>

【权威参考】

http://php.net/manual/en/book.mysqli.php