PHP访问MySQL数据库的常用方法

PHP访问MySQL数据库可以使用三种接口:

一.pdo_mysql(推荐)

连接:

$dsn = "mysql:host=localhost;dbname=test";
$username = 'root';
$password = '123456';
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);

$pdo = new PDO($dsn, $username, $password, $options);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

增:
方法1: 绑定关联数组

$str=$pdo->prepare("INSERT INTO `user` (`username`, `password`) VALUES (:username,:password)");
$str->execute(array(":username"=>"test", ":password"=>"passwd"));

方法2: 绑定索引数组

$str=$pdo->prepare("INSERT INTO `user` (`username`, `password`) VALUES (?,?)");
$str->execute(array("test", "passwd"));

删:

$str=$pdo->prepare("delete from user where id > 3");
$str->execute();

改:

$str=$pdo->prepare("UPDATE `user` SET username=:username,password=:password where id=:id");
$str->execute(array(":username"=>"test", ":password"=>"passwd", ":id"=>"3"));

查:
方法1: 单个取出,循环遍历,返回到数组

$str=$pdo->prepare("select * from user where id > :id  order by id");
$str->execute(array(":id"=>2));
$str->setFetchMode(PDO::FETCH_ASSOC);
//共三种:1.PDO::FETCH_BOTH(默认) 2.FETCH_ASSOC 3.FTECH_NUM

while($data=$str->fetch()){
print_r($data);
echo '
';
}

方法2: 全部取出,返回到二维数组

$str=$pdo->prepare("select * from user order by fid");
$str->execute();
$data=$str->fetchAll(PDO::FETCH_NUM);
print_r($data);

方法3:单个取出,循环遍历,绑定字段名到变量

$str=$pdo->prepare("select fid,username,password from user order by id");
$str->execute();

$str->bindColumn("id",$id);
$str->bindColumn("username",$username);
$str->bindColumn(3,$password);

while($str->fetch()){
echo "$id | $username | $password
";
}
echo "总记录数:".$str->rowCount()."
";
echo "总字段数:".$str->columnCount()."
";

二.mysqli

	$mysqli=new mysqli("localhost", "root", "123456", "test");
$sql="select * from user order by id";
$result=$mysqli->query($sql);
echo '';
while($row=$result->fetch_assoc()){
echo '';
foreach($row as $col){
echo '';
}
echo '';
}
echo '
'.$col.'
';
$mysqli->close();

三.mysql

$conn=mysql_connect("localhost", "root", "123456") or die("数据库连接失败");
mysql_select_db("test") or die("选择数据库失败");;
$sql="select * from user";
$data=mysql_query($sql);
echo ''
while($row=mysql_fetch_assoc($result)){
echo '';
foreach($row as $col){
echo '';
}
echo '';
};
echo '
'.$col.'
';
mysql_close();

发表评论

电子邮件地址不会被公开。 必填项已用*标注