0,数据库 test_db 的表:
-- ----------------------------
-- Table structure for articles
-- ----------------------------
DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
`id` int(25) NOT NULL,
`user_id` int(25) NULL DEFAULT NULL,
`title` varchar(110) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`memo` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`create_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`update_at` datetime(0) NOT NULL COMMENT '发布时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(25) NOT NULL COMMENT '主键id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
`create_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
1,pdo 连接数据库
<?php
$pdo = new PDO("mysql:host=localhost;dbname:test_db", "root", "");
// 1) 使用query
$sql = $pdo->query('SELECT * FROM users LIMIT 2'); //返回一个PDOStatement对象
//$row = $stmt->fetch(); // 结果集中获取下一行,用于while循环
$rows = $sql->fetchAll(); //获取所有
$row_count = $sql->rowCount(); //记录数,2
// 2) 使用prepare 推荐!
$sql = $pdo->prepare("select * from user where name = ? and age = ? ");
$sql->bindValue(1,'allen');
$sql->bindValue(2,20);
$sql->execute(); //执行一条预处理语句 .成功时返回 TRUE, 失败时返回 FALSE
$rows = $sql->fetchAll();
3) 预处理--执行的同时,指定绑定的参数
<?php
// 3) 预处理--执行的同时,指定绑定的参数
$pdo = new PDO("mysql:host=localhost;dbname:test_db", "root", "");
$sql = "INSERT INTO chat(nickname,msg) values(:nickname,:msg)";
$sql = $pdo->prepare($sql);
$sql->execute(array(
':nickname' => $nickname,
':msg' => $msg,
));
header("Content-type:application/json");
if ($pdo->lastInsertId() > 0) {
return json_encode(array('code' => 0, "msg" => "ok"));
} else {
return json_encode(array('code' => -1, "msg" => "failed"));
}
2, mysqli 连接数据库
2-1, mysqli 不预处理
<?php
// 1,创建数据库链接
$con = mysqli_connect('localhost', 'root', '', 'test_db', '3306');
// 2,链接是否出现问题
if (!$con) {
// mysqli_connect_error() 返回上一个 MySQL 连接产生的文本错误信息
die('Could not connect:' . mysqli_connect_error());
}
// 3,选择数据库
mysqli_select_db($con, "dm");
// 4,设置字符集
mysqli_set_charset($con, 'utf8');
// 5,编写mysql:
//$sql = "SELECT * FROM dm.users WHERE id>0 LIMIT 0,10";
$sql = "SELECT * FROM users";
$query = mysqli_query($con, $sql);
// $mysqli->mysqli_insert_id 返回最后一条插入语句产生的自增 ID
printf("New Record has id %d.\n", mysqli_insert_id($con));
/************** 结果集展示 ***************/
/**
* * todo 为常用的mysql结果集函数
* */
// 1,从结果集中取得 [ 一行 ] 作为关联数组,或数字数组,或二者兼有
$rows = mysqli_fetch_array($query);
// 2,从结果集中取得所有行作为关联数组、枚举数组、或二者兼有
$rows = mysqli_fetch_all($query);
// todo 3,select语句有效,取得结果集中行的数目
$rows = mysqli_num_rows($query); // 10
// todo 4, 从结果集中取得一行作为关联数组
$rows = mysqli_fetch_assoc($query);
while ($rows) {
// 取出所有的数据
var_dump($rows);
}
// 5, 取得前一次 MySQL 操作所影响的记录行数
$rows = mysqli_affected_rows($con);
// 释放结果集
mysqli_free_result($query);
// 关闭数据库
mysqli_close($con);
2-2, mysqli 预处理
<?php
$mysqli = new mysqli("localhost", "root", "", "test_db");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$stmt = $mysqli->prepare("INSERT INTO users (`id`, `name`) VALUES(?, ?)");
/**
* 参数有以下四种类型:
* i - integer(整型)
* d - double(双精度浮点型)
* s - string(字符串)
* b - BLOB(binary large object:二进制大对象)
*/
$stmt->bind_param("is", $id, $name);
// 设置参数
$id = "3";
$name = "gfh";
$stmt->execute();
//关闭预编译
$stmt->close();
//关闭数据库连接
$mysqli->close();