PDO::

출처:youtu.be/rcNYXc-hG_I

youtu.be/T41SMNgyRrc

php를 통해 DB Mysql 로 접근하기위해서는 Mysqli 또는 PDO 로 접근해야한다.

 

PDO 기본 연결

private $host="dbaddress";
private $user="username";
private $pwd="password";
private $dbName="dbname"

protected function connect(){
$dsn = 'mysql:host=' . $this->host. ';dbname='.$this->dbName;
$pdo = new PDO($dsn,$this->user,$this->pwd);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
return $pdo;
}

class Test extends Dbh{

public function getUsers(){
$sql ="SELECT * FROM tablename";
$stmt = $this->connect()->query($sql);
while($row=$stmt->fetch()){
echo $row['tableindex']. '</br>';
}

}



public function getUsersStmt($columnname1,$columnname2){

$sql ="SELECT * FROM tablename WHERE columnname1 =? AND columnname2 = ?";

$stmt = $this->connect()->prepare($sql);

$stmt->execute([$columnname1,$columnname2]);

$infos =$stmt->fetchAll();

foreach($infos as $info){
echo $info['columnname']. '</br>';
}
}

public function setUsersStmt($columnname1,$columnname2){

$sql ="INSERT INTO tablename(columnname1,columnname2) VALUES (?,?)";

$stmt = $this->connect()->prepare($sql);

$stmt->execute([$columnname1,$columnname2]);


}


}

 

<?php

$testObj=new Test();

$testObj->getUsers();

$testObj->getUsersStmt("abc","asda");

?>

 

 

출처:

youtu.be/kEW6f7Pilc4

 

1.PDO 기본 DSN 구축 및 PDO 세팅

<?php
$host='localhost';
$user='root';
$password='';
$dbname='pdoposts';

//Set DSN
$dsn='mysql:host='.$host.';dbname='.$dbname;

$pdo=new PDO($dsn,$user,$password);
?>

2.Attribute 설정 

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

 

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

또는

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

-LIMIT 값을 유저에게 받아 올 때 

 

3.쿼리 

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

위 while 은 ASSOC

밑 while 은 OBJ

 

x.해서는 안될 불안정한 방법.x

$sql="SELECT * FROM posts WHERE author = '$author'";

4.유저 인풋 

$author='Brad';
$is_published=true;
$id=1;

 

5. positional parameter

$sql='SELECT * FROM posts WHERE author=?';
$stmt=$pdo->prepare($sql);
$stmt->execute([$author]);
$posts=$stmt->fetchAll();

 

6.named parameter

$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();

7.fetch single

$sql='SELECT * FROM posts WHERE id=:id';
$stmt=$pdo->prepare($sql);
$stmt->execute(['id'=>$id]);
$posts=$stmt->fetch();

echo $posts['body'];

 

8.get row count 

$stmt=$pdo->prepare('SELECT * FROM posts WHERE author=?');
$stmt->execute([$author]);
$postCount=$stmt->rowCount();

echo $postCount;

 

9.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';

10.update data

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

 

11.delete data

$id=1;

$sql='DELETE FROM posts WHERE id=:id';
$stmt=$pdo->prepare($sql);
$stmt->execute(['id'=>$id]);
echo 'Post Deleted';

 

12.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>';
}