forked from bramus/ws1-sws-course-materials
-
Notifications
You must be signed in to change notification settings - Fork 0
/
07.databases.summary.html
241 lines (173 loc) · 9.83 KB
/
07.databases.summary.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
<!doctype html>
<html>
<head>
<title>PHP: PDO and MySQL</title>
<style>
.main div { padding-left: 1em; margin-left: 1em; border-left: 0.125em solid #666; }
.main pre { border: 0.125em dotted #333; padding: 1em; width: 80%; overflow: scroll; }
a {color: #0066FF;}
.main a, a.higher { text-decoration: none; font-size: 70%; vertical-align: top; padding: 1em; margin: -1em; }
.info, .warning { font-style: italic; }
.info { opacity: 0.75; }
.warning-sign { font-size: 2em; font-style: normal;}
</style>
</head>
<body>
<h1>PHP: PDO<a href="http://www.php.net/manual/en/class.pdo.php" class="higher">⚑</a> and MySQL</h1>
<section class="info">
<p>This page forms the code summary of <a href="07.databases.html">07.databases.html</a>, part of the <strong>Webscripting1 — Serverside Webscripting</strong> course, part of the <a href="http://www.ikdoeict.be/">Professional Bachelor ICT</a> study programme, taught at Odisee, Ghent, Belgium. The materials and this summary were developed by Bram(us) Van Damme, lecturer ICT at Odisee, who blogs over at <a href="http://www.bram.us/">bram.us</a> and Twitters as <a href="http://www.twitter.com/bramus">@bramus</a>. The materials and this summary may be used freely, as long as credit to Bramus is present and a clear an upfront link to <a href="http://www.ikdoeict.be/">ikdoeict.be</a> remains in place. Suggestions and additions may be mailed to Bramus, or sent via <a href="https://github.com/bramus/ws1-sws-course-materials">a pull request on GitHub</a>.</p>
</section>
<section class="main">
<h2>Establishing a Database Connection<a href="http://www.php.net/manual/en/pdo.construct.php">⚑</a></h2>
<div><pre class="source"><code><?php
// Include config + functions used throughout the project
require_once __DIR__ . '/includes/config.php';
require_once __DIR__ . '/includes/functions.php';
// Make Connection
try {
$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME . ';charset=utf8', DB_USER, DB_PASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (Exception $e) {
showDbError('connect', $e->getMessage());
}</code></pre></div>
<p>Put this in <code>config.php</code></p>
<div><pre class="source"><code>// Database config
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', 'Azerty123');
define('DB_NAME', 'fotofactory');</code></pre></div>
<p>Put this in <code>functions.php</code></p>
<div><pre class="source"><code>// Error Logging and User Redirects
function showDbError($type, $msg) {
file_put_contents(__DIR__ . '/error_log_mysql', PHP_EOL . (new DateTime())->format('Y-m-d H:i:s') . ' : ' . $msg, FILE_APPEND);
header('location: error.php?type=db&detail=' . $type);
exit();
}</code></pre></div>
<h2>Building and executing queries, the basic edition</h2>
<p class="warning"><span class="warning-sign">⚠</span> Don't use this, use <strong>prepared statements</strong> (see below) instead. Contents only placed here for reference/context.</p>
<h3>Use <code>PDO::query()</code><a href="http://www.php.net/manual/en/pdo.query.php">⚑</a> to execute queries that return a resultset (SELECT, DESCRIBE, SHOW, etc.)</h3>
<div><pre class="source"><code><?php
$stmt = $db->query(
'SELECT * FROM collections WHERE user_id = ' . $db->quote(2, PDO::PARAM_INT) . ' OR name = ' . $db->quote('russia', PDO::PARAM_STR)
);</code></pre></div>
<p>Don't forget to use <code>PDO::quote()</code><a href="http://www.php.net/manual/en/pdo.quote.php">⚑</a> to prevent SQL Injection!</p>
<p>Use a <em>fetch function</em> to fetch the data (see further)</p>
<h3>Use <code>PDO::exec()</code><a href="http://www.php.net/manual/en/pdo.exec.php">⚑</a> to execute queries that do not return resultsets (UPDATE, DELETE, INSERT, etc)</h3>
<div><pre class="source"><code><?php
$stmt = $db->exec(
'DELETE FROM collections WHERE user_id = ' . $db->quote(20, PDO::PARAM_INT)
);</code></pre></div>
<p>Don't forget to use <code>PDO::quote()</code><a href="http://www.php.net/manual/en/pdo.quote.php">⚑</a> to prevent SQL Injection!</p>
<p>Use an extra function to fetch meta information about the result (see further)</p>
<h2>Building and executing queries, using Prepared Statements</h2>
<p>In short: Use <code>PDO::prepare()</code><a href="http://www.php.net/manual/en/pdo.prepare.php">⚑</a> to get a <code>PDOStatement</code>, bind parameters onto it using <code>PDOStatement::bindValue()</code><a href="http://www.php.net/manual/en/pdostatement.bindvalue.php">⚑</a>, and then execute that statement using <code>PDOStatement::execute()</code><a href="http://www.php.net/manual/en/pdostatement.execute.php">⚑</a></p>
<h3>Binding parameters one by one</h3>
<p class="warning"><span class="warning-sign">⚠</span> Don't use this, <strong>bind all parameters in one go</strong> (see below) instead. Contents only placed here for reference/context.</p>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = :user_id OR name = :name');
$stmt->bindValue(':user_id', 2, PDO::PARAM_INT);
$stmt->bindValue(':name', 'russia', PDO::PARAM_STR);
$stmt->execute();</code></pre></div>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?');
$stmt->bindValue(1, 2, PDO::PARAM_INT);
$stmt->bindValue(2, 'russia', PDO::PARAM_STR);
$stmt->execute();</code></pre></div>
<h3>Binding all parameters in one go</h3>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));</code></pre></div>
<h2>Processing the results of queries</h2>
<h3>Processing queries that produce resultsets (SELECT, DESCRIBE, SHOW, etc.)</h3>
<p>In short: Use a <em>fetch function</em> to retrieve data from the statement.</p>
<h3>Fetch rows one by one using <code>PDOStatement::fetch()</code><a href="http://www.php.net/manual/en/pdostatement.fetch.php">⚑</a></h3>
<p>One row returned:</p>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT * FROM collections WHERE id = ?');
$stmt->execute(array(1));
$collection = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($collection);</code></pre><pre class="result">array(3) {
'id' =>
string(1) "1"
'name' =>
string(6) "russia"
'user_id' =>
string(1) "4"
}</pre></div>
<p>Use a while loop to iterate multiple returned rows:</p>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT * FROM collections WHERE id != ?');
$stmt->execute(array(1));
while ($collection = $stmt->fetch(PDO::FETCH_ASSOC)) {
var_dump($collection);
}</code></pre></div>
<p>Other interesting <em>Fetch Types</em>: <code>PDO::FETCH_OBJ</code>, <code>PDO::FETCH_NUM</code> and <code>PDO::FETCH_GROUP</code></p>
<h3>Fetch all rows using <code>PDOStatement::fetchAll()</code><a href="http://www.php.net/manual/en/pdostatement.fetchall.php">⚑</a></h3>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?);
$stmt->execute(array(2, 'russia'));
$collections = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($collections);</code></pre><pre class="result">array(3) {
[0] =>
array(3) {
'id' =>
string(1) "1"
'name' =>
string(6) "russia"
'user_id' =>
string(1) "4"
}
[1] =>
array(3) {
'id' =>
string(1) "2"
'name' =>
string(6) "serbia"
'user_id' =>
string(1) "2"
}
[2] =>
array(3) {
'id' =>
string(1) "4"
'name' =>
string(8) "TestColl"
'user_id' =>
string(1) "2"
}
}</pre></div>
<p>Other interesting <em>Fetch Types</em>: <code>PDO::FETCH_OBJ</code>, <code>PDO::FETCH_NUM</code> and <code>PDO::FETCH_GROUP</code></p>
<h3>Fetch a single value/column (from the first row) using <code>PDOStatement::fetchColumn()</code><a href="http://www.php.net/manual/en/pdostatement.fetchcolumn.php">⚑</a></h3>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT name, user_id FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));
$value = $stmt->fetchColumn();
var_dump($value);</code></pre><pre class="result">string(6) "russia"
</pre></div>
<p>Add a numeric index (starting from 0) to define which column you want to extract</p>
<div><pre class="source"><code><?php
$stmt = $db->prepare('SELECT name, user_id FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));
$value = $stmt->fetchColumn(1);
var_dump($value);</code></pre><pre class="result">string(1) "4"
</pre></div>
<h2>Processing queries that do not produce resultsets (UPDATE, DELETE, INSERT, etc)</h2>
<p>In short: Use an extra function to fetch meta information about the result</p>
<h3>Use <code>PDOStatement::rowCount()</code><a href="http://www.php.net/manual/en/pdostatement.rowcount.php">⚑</a> to get to know the number of affected rows made by <code>UPDATE</code> and <code>DELETE</code> queries</h3>
<div><pre class="source"><code><?php
$stmt = $db->prepare('UPDATE collections SET name = REVERSE(name) WHERE user_id = ?');
$stmt->execute(array(2));
$affectedRows = $stmt->rowCount();
var_dump($affectedRows);</code></pre><pre class="result">int(2)
</pre></div>
<h3>Use <code>PDO::lastInsertId()</code><a href="http://www.php.net/manual/en/pdo.lastinsertid.php">⚑</a> to get to know the last inserted id (<code>AUTO_INCREMENT</code>)</h3>
<div><pre class="source"><code><?php
$stmt = $db->prepare('INSERT INTO collections (name,user_id) VALUES (?,?)');
$stmt->execute(array('testalbum', 2));
$insertId = $db->lastInsertId();
var_dump($insertId);</code></pre><pre class="result">string(1) "5"
</pre></div>
</section>
</body>
</html>