PDO tutorial

Published
2022-09-29
浏览次数 :  36

<?php 
/**
 * PDO TUTORIAL example 
 * @var string
 */
$host = 'localhost';
$user = 'root';
$password = '';

$dbname = 'pdobase';

//set dsn

$dsn = 'mysql:host=' . $host . ';dbname='.$dbname;

//created pdo instance
$pdo = new PDO($dsn,$user,$password);

//设置默认的fetch的获取方式

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
//default query 

// $stmt = $pdo->query('SELECT * FROM posts');

// while ($row = $stmt->fetch()) {
// 	// code...
// 	echo '<h1>' . $row->title . '</h2><br>';
// }
// 
// while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// 	// code...
// 	echo '<h1>' . $row['title'] . '</h2><br>';
// }

//prepare statement  prepare && execute 

//user input
$author = 'wayne';
$is_published = true;
$limit = 2;
$id = 2;

//positional param
//这里limit要起作用的话要设置取消ATTR_EMULATE_PREPARES 属性
$sql = 'SELECT * FROM posts WHERE author = ? && is_published = ? LIMIT ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$author,$is_published,$limit]);

//named params 
//
// $sql = 'SELECT * FROM posts WHERE author = :author';
// $stmt = $pdo->prepare($sql);
// $stmt->execute(['author' => $author]);

$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($posts as $post) {
	echo $post['title'] . '<br>';
}


//fetch 是获取1个,fetch all 是获取全部
// $sql = 'SELECT * FROM posts WHERE id = :id';

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

// $stmt->execute(['id' => $id]);

// $post = $stmt->fetch();

// echo $post->body;

//get row count rowCount() 函数
// $sql = 'SELECT * FROM POSTS WHERE author = :author';

// $stmt = $pdo->prepare($sql);
// $stmt->execute(['author' => $author]);

// $count = $stmt->rowCount();


// echo $count;

//INSERT DATA
// $title = 'POST ERight';
// $body = 'THIS IS POST ERight';
// $author = 'albert';

// $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';


//UPDATE 
// $id = 1;
// $body = 'This is the updated post';

// $sql = 'UPDATE posts SET body=:body WHERE id = :id';

// $stmt = $pdo->prepare($sql);
// $stmt->execute(['body' => $body,'id' => $id]);

// echo 'post updated';

//DELETE 
// $id = 3;

// $sql = 'DELETE FROM posts WHERE id = :id';

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

// $stmt->execute(['id' => $id]);

// echo 'post deleted';

//search data 搜索的方法
//search the word post 
// $search = "%h%";

// $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>';
// }

Top