php mysql 驱动查询_php 连接mysql（查询 增删改）

1、连接数据库方法：

//方法1

$mysqli = new mysqli("localhost", "root", "root", "easyadmin");

if (!$mysqli) {

echo "database error";

} else {

echo "php env successful";

}

$mysqli->close();

//方法2

try {

$dbh = new PDO('mysql:host=localhost;dbname=easyadmin', 'root', 'root', array(PDO::ATTR_PERSISTENT => true)); //持久性连接

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh->exec('SET CHARACTER SET utf8');

echo "连接成功";

echo "
";

/* 1.断开连接*/

//$dbh = null;

$name = 'kevin2%';

/* 2.查询：sql语句中user与name不能是单引号‘’,是左上角的波浪线~,sql语句以外的可以是单引号，否则查询语句会报错*/

$sql = "select * from `user` where `name` like :name";

$stmt = $dbh->prepare($sql);

$stmt->execute(array(':name' => $name));

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

print_r($row);

echo("
");

}

// /*3.添加*/

// $sql = "INSERT INTO `user` (`name` ,`password`)VALUES (:name, :password)";

// $stmt = $dbh->prepare($sql);

// $stmt->execute(array(':name' => 'kevin2', ':password' => '123459'));

// echo $dbh->lastInsertId();

/*4.删除*/

$name = "zj";

$sql = "delete from `user` where `name` like :name";

$stmt = $dbh->prepare($sql);

$stmt->execute(array(':name' => $name));

echo $stmt->rowCount();

/*5.修改*/

$id = 3;

$sql = "update `user` set `password`=:password where `id`=:id ";

$stmt = $dbh->prepare($sql);

$stmt->execute(array(':id' => $id, ':password' => 'testabc'));

echo "Update：" . $stmt->rowCount();

} catch (PDOException $ex) {

print"Error!:" . $ex->getMessage() . "
";

die();

}

?>

2.参数绑定

举例：

$stmt = $dbh->prepare($sql);

$stmt->bindParam(':country', $country, PDO::PARAM_STR);

$stmt->execute();

3.事务

try {

$dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');

$dbh->query('set names utf8;');

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh->beginTransaction();

$dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('mick', 22);");

$dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('lily', 29);");

$dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('susan', 21);");

$dbh->commit();

} catch (Exception $e) {

$dbh->rollBack();

echo "Failed: " . $e->getMessage();

}

4.foreach语句

$dbh->query($sql); 当$sql 中变量可以用$dbh->quote($params); //转义字符串的数据

$sql = 'Select * from city where CountryCode ='.$dbh->quote($country);  //引用

foreach ($dbh->query($sql)as $row)   {

print $row['Name'] . "/t";

print $row['CountryCode'] . "/t";

print $row['Population'] . "/n";

}

?>