PDO =》 PHP DATABASE OBJECT
1、Select
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "select * from table_name where NAME = :name AND PWD = :pwd";
$sth = $dbh->prepare($sql);
$sth ->bindValue(':name', 'user');
$sth ->bindValue(':pwd', 'password');
$sth-> execute();
foreach($sth as $row) {
echo var_dump($row);
}
$dbh = null;
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "select * from table_name where NAME = ?AND PWD = ?";
$sth = $dbh->prepare($sql);
$sth ->bindValue(1, 'user');
$sth ->bindValue(2, 'password');
$sth-> execute();
foreach($sth as $row) {
echo var_dump($row);
} $dbh = null;
2、UPDATE
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "update table_name set name = :name where id = :id";
$sth = $dbh->prepare($sql);
$sth ->bindValue(':name', 'user');
$sth ->bindValue(':id', '1');
$flag = $sth-> execute(); // true or false
$dbh = null;
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "update table_name set name = ? where id = ?";
$sth = $dbh->prepare($sql);
$sth ->bindValue(1, 'user');
$sth ->bindValue(2, '1');
$flag = $sth-> execute(); // true or false
$dbh = null;
3、Insert
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "insert into table_name (name) values (:name)";
$sth = $dbh->prepare($sql);
$sth ->bindValue(':name', 'user');
$flag = $sth-> execute(); // true or false
$dbh = null;
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "insert into table_name (name) values (?)";
$sth = $dbh->prepare($sql);
$sth ->bindValue(1, 'user');
$flag = $sth-> execute(); // true or false
$dbh = null;
4、Delete
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "delete from table_name where id = :id";
$sth = $dbh->prepare($sql);
$sth ->bindValue(':id', '1');
$flag = $sth-> execute(); // true or false
$dbh = null;
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=dbname";
$dbh = new PDO($dsn, 'root', 'password');
$sql = "delete from table_name where id = ?";
$sth = $dbh->prepare($sql);
$sth ->bindValue(1, '1');
$flag = $sth-> execute(); // true or false
$dbh = null;
每一部分的第二段代码都是用?和数字索引的方式来绑定参数,有的人可能不是很理解这些后绑定跟直接生成sql语句之后去执行有什么差别,其实不难理解。
假设你输入:
select * from table_name where id = ?
问号的部分如果直接动态生成就可能变成这样
select * from table_name where id = 1 or 1=1
而如果动态绑定的话,问号部分就被限制只能输入一个跟id字段类型相符合的变量,如果有sql注入就会编译不过