PDO

PHP PDO 数据操作详解

最新推荐文章于 2025-03-02 19:03:00 发布

howlowl

最新推荐文章于 2025-03-02 19:03:00 发布

阅读量192

点赞数

分类专栏： PHP

PHP 专栏收录该内容

4 篇文章

订阅专栏

mysql:

<?php
	//1. Create a database connection
	$connection = mysql_connect("localhost", "root", "****");
	if(!$connection){
		die("Database connection failed: " . mysql_error());
	}
	
	//2. Select a database to use
	$db_select = mysql_select_db("widget_corp", $connection);
	if(!$db_select){
		die("Database selection failed: " . mysql_error());
	}
?>
<html>
	<head>
		<title>Basic</title>
	</head>
	<body>
	<?php
		//3. perform database query
		$result = mysql_query("select * from subjects", $connection);
		if(!$result){
			die("Database query failed: " . mysql_error());
		}
		
		//4.use returned database
		while($row = mysql_fetch_array($result){
			echo $row["menu_name"] . " " . $row["position"] . "<br />";
		}
	?>
	</body>
</html>
<?php
	//5. close connection
	mysql_close($connection);
?>

PDO:

prepared statement precompiles the statement and treats input passed in just as data, there is no way that data is accidently executed;

Main PDO classes:

1.PDO represents a connection between DB and PHP

2.PDOStatement represents a prepared statement and after execution a associated result

3.PDOError represents a error raised by PDO

<?php
    print_r(getAvailableDrivers());
?>

<?php
	$host = 'localhost';
	$dbname = 'pdoposts';
	$charset = 'utf8mb4';
	$user = 'root';
	$pass = '***';
	
	$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
	$opt = [
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_EMULATE_PREPARES => false,
	]
	try{
		$pdo = new PDO($dsn, $user, $pass, $opt);
	}catch(PDOException $e){
		echo $e->getMessage() . "<br />";
		die();
	}
	
	$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
	
	
	##PRDO QUERY
	$stmt = $pdo->query('SELECT * FROM posts');
	#
	while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		echo "$row['title']<br />";
	}
	#
	while($row = $stmt->fetch()){
		echo $row->title . "<br />";
	}
	##
	
	
	##PREPARE STATEMENTS(prepare & execute)
	//UNSAFE
	//$sql = "SELECT * FROM posts where author = '$author' ";
	//FETCH MULTIPLE POSTS
	//User Input
	$author = 'Brad';
	$is_published = true;
	$id = 1;
	$limit = 1;
	
	//positional params
	$sql = 'SELECT * FROM posts WHERE author = ? && is_published = ? LIMIT ?';
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$author, $is_published, $limit);
	$posts = $stmt->fetchAll();
	
	//named params
	$sql = 'SELECT * FROM posts WHERE author = :author && is_published = :is_published';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(['author'=> $author, 'is_published' => $is_published]);
	$posts = $stmt->fetchAll();
	
	//var_dump($posts);
	foreach($posts as $post){
		echo $post->title . "<br />";
	}
	
	//FETCH SINGLE POST
	$sql = 'SELECT * FROM posts where id = :id ';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(['id' => $id]);
	$post = $stmt->fetch();
	echo $post->body . "<br />";
	
	//GET ROW COUNT
	$stmt = $pdo->prepare('SELECT * FROM posts WHERE author = ?');
	$stmt->execute([$author]);
	$postCount = $stmt->rowCount();
	echo $postCount . "<br />";
	
	//INSERT DATA
	$title = 'POST FIVE';
	$body = 'This is post five';
	$author = 'Kevin';
	$sql = 'INSERT INTO posts(title, body, author) VALUES(:title, :body, :author)';
	$stmt = $pdo->prepare($sql);
	$stmt->execute('title' => $title, 'body' => $body, 'author' => $author);
	echo 'Post added<br />';
	
	//UPDATE DATA
	$id = 1;
	$body = 'This is the updated post';
	$sql = 'UPDATE posts SET body = :body WHERE id = :id';
	$stmt = $sql->prepare($sql);
	$stmt->execute(['body' => $body, 'id' => $id]);
	echo 'Post updated<br />';
	
	//DELETE DATA
	$id = 3;
	$sql = 'DELETE FROM posts WHERE id = :id';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(['id' => $id]);
	echo 'Post deleted<br />';
	
	//SEARCH DATA
	$search = '%f%';
	$sql = 'SELECT * FROM posts WHERE title LIKE ?';
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$search]);
	$posts = $stmt->fetchAll();
	
	foreach($posts as $post){
		echo $post->title . "<br />";
	}	
	
?>

<?php
	$db = new PDO('mysql:host=localhost;dbname=test', 'root', '***');
	$sql = 'INSERT INTO people(username, gender country) VALUES (:username, :gender, :country)';
	$stmt = $pdo->prepare($sql);
	$stmt->bindParam(':username', $username);
	$stmt->bindParam(':gender', $gender);
	$stmt->bindParam(':country', $country);
	
	$username = 'Beth';
	$gender = 'f';
	$country = 'Canada;
	$stmt->execute();
	
	$username = 'Sandeep';
	$gender = 'm';
	$country = 'India';
	$stmt->execute();
	
	$db = null
?>

(三）PDO closing connection

With MySQLi, to close the connection you could do:

$this->connection->close();

However with PDO it states you open the connection using:

$this->connection = new PDO();

but to close the connection you set it to null.

$this->connection = null;

Is this correct and will this actually free the PDO connection? (I know it does as it is set to null.) I mean with MySQLi you have to call a function (close) to close the connection. Is PDO as easy as = null to disconnect? Or is there a function to close the connection?

up vote down vote accepted

According to documentation you're correct (http://php.net/manual/en/pdo.connections.php):

The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

（四）Getting data out of statement. fetch()

We have seen this function already, but let's take a closer look. It fetches a single row from database, and moves the internal pointer in the result set, so consequent calls to this function will return all the resulting rows one by one. Which makes this method a rough analogue to mysql_fetch_array() but it works in a slightly different way: instead of many separate functions (mysql_fetch_assoc(), mysql_fetch_row(), etc), there is only one, but its behavior can be changed by a parameter. There are many fetch modes in PDO, and we will discuss them later, but here are few for starter:

PDO::FETCH_NUM returns enumerated array
PDO::FETCH_ASSOC returns associative array
PDO::FETCH_BOTH - both of the above
PDO::FETCH_OBJ returns object
PDO::FETCH_LAZY allows all three (numeric associative and object) methods without memory overhead.

From the above you can tell that this function have to be used in two cases:

When only one row is expected - to get that only row. For example,
```
$row = $stmt->fetch(PDO::FETCH_ASSOC);
```
Will give you single row from the statement, in the form of associative array.
When we need to process the returned data somehow before use. In this case it have to be run through usual while loop, like one shown above.

Another useful mode is PDO::FETCH_CLASS, which can create an object of particular class

$news = $pdo->query('SELECT * FROM news')->fetchAll(PDO::FETCH_CLASS, 'News');

will produce an array filled with objects of News class, setting class properties from returned values. Note that in this mode

properties are set before constructor call
for all undefined properties __set magic method will be called
if there is no __set method in the class, then new property will be created
private properties will be filled as well, which is a bit unexpected but quite handy

Note that default mode is PDO::FETCH_BOTH, but you can change it using PDO::ATTR_DEFAULT_FETCH_MODE configuration option as shown in the connection example. Thus, once set, it can be omitted most of the time.

Manual: http://php.net/manual/zh/pdostatement.fetch.php

Ref:

https://phpdelusions.net/pdo