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?
|
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 arrayPDO::FETCH_ASSOC
returns associative arrayPDO::FETCH_BOTH
- both of the abovePDO::FETCH_OBJ
returns objectPDO::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