-
Notifications
You must be signed in to change notification settings - Fork 15
/
rdbms.html
286 lines (270 loc) · 19.7 KB
/
rdbms.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
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
---
title: Relational Databases
---
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="chrome=1">
<title>PHP101 - {{ page.title }}</title>
<link rel="stylesheet" href="stylesheets/styles.css">
<link rel="stylesheet" href="stylesheets/pygment_trac.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="javascripts/main.js"></script>
<!--[if lt IE 9]>
<script src="//html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no">
</head>
<body>
<header>
<h1>PHP101</h1>
<p>For the absolute beginner</p>
</header>
<div id="banner">
<span id="logo"></span>
<a href="https://github.com/ss23/php-tutorial" class="button fork"><strong>View On GitHub</strong></a>
</div><!-- end banner -->
<div class="wrapper">
{% include nav.html %}
<section>
<h2>Relational Databases in PHP</h2>
<p>One of the main reasons you might be looking to use PHP is to store and
retrieve data from a database. There are numerous database servers out there
and many ways for PHP to access them. This tutorial will cover database access
using the PDO classes. For easier setup in a learning environment this tutorial
will use the <a href="http://www.sqlite.org/">SQLite</a> database engine as it
is commonly already included in PHP installs and supports an in-memory database
so that there is no server to setup for these examples. The SQL used in this
tutorial will strive to be simple and portable over many databases as teaching
the finer points of SQL, especially as geared towards your specific server, is
not within the scope of this document. But keep in mind that PDO does not abstract
your SQL queries; it just provides a unified API for interacting with many database
engines. If you change engines you will likely need to modify the SQL you used.</p>
<h2>Building Blocks</h2>
<p>To use PDO and SQLite, or any other database, then support for the proper drivers must be
enabled on the installation of PHP you are using. It is fairly common, but not assured,
that PDO and SQLite are already built in. To check this look at the output from <code>phpinfo();</code>
on the server you wish to run your code on. If you are running PHP from command line then
<code>php -m</code> will work.</p>
<p>You should see 'PDO' and 'pdo_sqlite' as modules listed for this tutorial to work</p>
<p>TODO: Instructions on installing</p>
<h2>(Very) Basic SQL</h2>
<p>At the most basic a database is composed of one or more tables. Tables organize data two
dimensionally into rows and columns. A typical table might look something like:</p>
<pre>
Table: nationalAnimal
+----+-----------+----------+
| id | country | animal | <-- column headings (fields)
+----+-----------+----------+
| 1 | America | eagle | <-- rows
| 2 | China | dragon |
| 3 | England | lion |
| 4 | India | tiger |
| 5 | Australia | kangaroo |
| 6 | Norway | elk |
+----+-----------+----------+
</pre>
<p>As you can see, each table has a heading that has the column (field) names and then rows of
data. Each row contains a value for each field. Each field can have a specified data type.
Each database engine has their own set of data types and you should review the manual for your
database of choice for further details. At it's most basic, and all that SQLite supports are:
integers, floating point numbers, strings, and binary blobs. Other database engines have support
for finer grained versions of the previously mentioned types and also typically have types for
storing dates, times, and boolean values.</p>
<p>Database tables should have a primary key. Typically this is a column like the 'id' column
displayed above, but the primary key can also be a composite value generated from multiple columns.
The idea is to have a unique identifier for a row. In addition many database engines support
automatically incrementing an integer value for a column. This is commonly paired with the
primary key.</p>
<p>As a brief overview of SQL commands you will be seeing in this tutorial examine the following SQL
statements keeping in mind that this tutorial is using the SQLite database engine. The following SQL
would create, and display something similar to the above table:</p>
{% highlight sql %}
CREATE TABLE `nationalAnimal` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`country` VARCHAR(255),
`animal` VARCHAR(255)
);
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('America', 'eagle');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('China', 'dragon');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('England', 'lion');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('India', 'tiger');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('Australia', 'kangaroo');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('Norway', 'elk');
SELECT `id`, `country`, `animal` FROM `nationalAnimal`;
{% endhighlight %}
<p>These commands can be entered using the sqlite3 command line client if you so desire, but install and
setup of the client is beyond the scope of this tutorial. For now just look over the SQL to familiarize
yourself with the commands as they are explained.</p>
<p>The first SQL command used above is 'CREATE TABLE'. This command creates the database table to store the
data. Because table creation sets up the data types for your fields it is commonly the most engine specific
SQL code a developer new to databases will encounter. In this specific example we are creating a table with
'nationalAnimal' as the name with 3 fields for data. First of which is 'id' which is an integer field that
is setup to be the primary key and have the value increment automatically. Second and third we have 'country'
and 'animal'. Those two fields both hold a string up to 255 characters in length.</p>
<p>The second set of SQL commands we have are 'INSERT'. This command deals with inserting the data into the table.
You tell it what table to insert the data into, list out the field names you wish to insert data into, then
associate the values to insert into the table. As 'id' is a special column the database engine takes care of
automatically inserting the integer. Each insert command inserts a new row into the database.</p>
<p>The third SQL command contained above is 'SELECT'. This command is one of the more commonly used when accessing
databases. In a select statement you first select the fields that you want and then indicate which table to
select from. The select statement could also be written as SELECT * FROM `nationalAnimal`; but it is generally
recommended that you specify which fields you want so that you can save memory by ignoring fields that are not
needed.</p>
<h2>Making a Connection</h2>
<p>The first step in using PDO is establishing a connection to the database. This is done with the PDO class constructor.
For the purposes of this tutorial we will be using SQLite's in memory database so the connection string will be very
simple. As you edit this file and save it, go ahead and run it either via your server, php-compatible pastbin or php-cli.
Create a new file, rmdbs-tutorial.php if you require a name, and enter in the following code:</p>
{% highlight php %}
<?php
$db = new PDO('sqlite::memory:');
{% endhighlight %}
<p>In this code we create a new instance of the PDO class, stored to the variable $db, in this we pass to the constructor
the data source name (dsn) string 'sqlite::memory:' this tells PDO to use the sqlite driver, and the driver specific
command of :memory: to create a new database in memory. PDO::__construct has several more options which are outside
the scope of this introductory tutorial but can be viewed in the php manual at <a href="http://php.net/manual/en/pdo.construct.php">
http://php.net/manual/en/pdo.construct.php</a>.</p>
<h2>Executing Statements</h2>
<p>The exec method of PDO directly executes the SQL and returns the number of rows affected. Mainly this command should
be used for executing statements that are commands to your database engine or things like creating tables. Add the
following code to your rmdbs-tutorial.php file you created earlier:</p>
{% highlight php %}
$sql_createtable = "CREATE TABLE `nationalAnimal` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `country` VARCHAR(255), `animal` VARCHAR(255))";
$db->exec($sql_createtable);
{% endhighlight %}
<p>You may recognize the CREATE TABLE syntax from before. The exec method accepts a string and runs it on the database that you
connected to when creating the $db variable. Here we assigned the CREATE TABLE string to $sql_createtable and passed it to the
exec method there by running the CREATE TABLE against the database.</p>
<h2>Prepared Statements</h2>
<p>If you have user generated input to your database you should ALWAYS use prepared statements. Prepared
statements help prevent SQL injection attacks by separating the SQL commands and the data when
communicating with the database engine. They can also optimize your application by allowing the client
and/or server to cache aspects of the query if you are calling PDOStatement::execute multiple times.
One thing prepare cannot do is parameterize table or field names, if you need to allow a user input to
select one of those then you should white list options that the user could chose and do not directly
associate the input with the statement. PDO::prepare if called successfully will return a PDOStatement
object for you to further call methods on to finish the process. In using prepared statements the SQL
statement can contain zero or more named or question mark parameter markers. You cannot mix named and
? prameters. This tutorial will only use named parameters as question mark parameters offer no advantage.
One caveat to named parameters is you cannot use the same name multiple times. To use question mark
parameters you must fill them in the same order they were setup and provide no context for what is expected.
This section of the tutorial will examine, in addition to PDO::prepare, the bindParam, bindValue, and
execute methods of PDOStatement. Please examine and add the code below to your rmdbs-tutorial.php that
was created, then modified, earlier:</p>
{% highlight php %}
$sql_prepare = "INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES (:country, :animal)";
/** mutiple inserts with bindParam **/
$stmt = $db->prepare($sql_prepare);
$stmt->bindParam(':country', $country, PDO::PARAM_STR);
$stmt->bindParam(':animal', $animal, PDO::PARAM_STR);
$foo[] = array('America', 'eagle');
$foo[] = array('China', 'dragon');
foreach($foo as $data) {
$country = $data[0];
$animal = $data[1];
$stmt->execute();
}
/** multiple inserts with execute **/
$stmt = $db->prepare($sql_prepare);
$bar[] = array(':country' => 'England', ':animal' => 'lion');
$bar[] = array(':country' => 'India', ':animal' => 'tiger');
foreach($bar as $data) {
$stmt->execute($data);
// an alternative method below that (could) result in less typing depending on how the array is constructed
// $stmt->execute(array(':country' => $data[':country'], ':animal' => $data[':animal']));
}
/** single insert with bindValue, then execute **/
$stmt = $db->prepare($sql_prepare);
$stmt->execute(array(':country' => 'Australia', ':animal' => 'kangaroo');
$stmt->bindValue(':country', 'Norway', PDO::PARAM_STR);
$stmt->bindValue(':animal', 'elk', PDO::PARAM_STR);
$stmt->execute();
{% endhighlight %}
<p>As demonstrated there are multiple ways to pass data into a prepared statement. The simplest, and one
used in the rest of this tutorial, is passing parameters directly to the execute method. This tutorial
will use passing directly to the execute method from here on out. This is due to that all that is
required is passing an array of values with the key matching the parameter.</p>
<p>The first thing to see is our insert statement, from the brief intro to SQL we saw 6 statements with
country names and animals. In this one, assigned to $sql_prepare, we have replaced those with the named
parameter placeholders :country and :animal. For named parameters they must be a colon (:) followed by
the name you wish them to be known by.</p>
<p>The first block of executed statements we insert two rows using bindParam. This binds the variable to the
parameter by reference and as such is only evaluated when execute is called. We used this feature to loop
through an array holding two country/animal pairs.</p>
<p>The second block shows inserting two more rows by passing the values directly to the execute method. This
block of code shows two ways of doing that. One way involves pre-constructing the array to match the format
required, the other just passes the values into a new array created as part of the attribute passed to the
execute method.</p>
<p>The third block of code shows single row insertions either by passing directly to the execute method, or by
using bindValue. In passing directly to the execute method it's largely the same as the previous section, just
without the loop to insert multiple rows. The bindValue method is similar to the bindParam but the binding is
not passed by reference and subsequently evaluated at execute it is instead bound immediately.</p>
<h2>Queries and Fetching Results</h2>
<p>Similar to exec, the query method executes a straight SQL statement but instead of returning the number of
rows affected it returns a PDOStatement so that data can be retrieved. Just like exec you should not pass
any user inputted data into this method as no attempt is made by it to prevent SQL injection. Please examine
and add the following code to your rmdbs-tutorial.php file that you've been editing so far:</p>
{% highlight php %}
$stmt = $db->query("SELECT `id`, `country`, `animal` FROM `nationalAnimal`");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$results[] = 'id:' . $row['id'] . ', country: ' . $row['country'] . ', animal: ' . $row['animal'];
}
var_dump($results);
{% endhighlight %}
<p>The query is a direct copy of the one in the SQL introduction. The new feature introduced here is the PDOStatement
method of fetch. This, on a row being found, returns the row in the format specified. In the case of the code here,
this fetch method is asked to return an associative array, or an array with a named key that matches the field names.
If no new row is to be found it returns a boolean false, allowing the while loop to end. This code composes each row
a string and adds each row to an array for easy output with var_dump.</p>
<p>TODO: Describe other fetch styles.</p>
<h2>More Prepared Statements With More Results</h2>
<p>The power of using a database is that you can select data meeting specific criteria and sort it before you bring it
into PHP. This is advantageous because your database is much better suited to that than PHP. The best policy is to,
in so far as it's possible, bring in only the data you need and have it already sorted. This tutorial will cover a
few basic methods of limiting and sorting data but for more details you really should read more about SQL in general
and your database engine in particular. Please examine the code below and add it to your rmdbs-tutorial.php file
that you've been editing so far:</p>
{% highlight php %}
$stmt = $db->prepare("SELECT `id`, `country`, `animal` FROM `nationalAnimal` WHERE `id` != :id ORDER BY `country`");
$stmt->execute(array(":id" => 1));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
$stmt = $db->prepare("SELECT `country`, `animal` FROM `nationalAnimal` WHERE `id` = :id");
$stmt->execute(array(":id" => 3));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
{% endhighlight %}
<p>This short example shows two select statements that are in prepared statements to allow for user input. While in this
case the variables are directly inputted they could be replaced with a variable taken from data inputted by the user
and would protect against sql injection. The first one selects all the rows except where the id column is a specific
number, in this case 1, and then sorts the results by country. The second query selects the rows where id is a specific
number. As ID is the primary key there will only be one row selected in this case. Both of these statements are then fetched
by a new fetchAll statement. Like fetch, it takes a specific fetch style as a parameter. Unlike fetch, it returns all the rows.</p>
<p>One method that is useful, though slightly vendor specific is lastInsertId(). The lastInsertId method can be called right
after an insert and, depending on database drivers, return the id of the last inserted row. Too see an example of this insert
the following code after one of the single row execute statements earlier:</p>
{% highlight php %}
$db->lastInsertId();
{% endhighlight %}
<h2>Something Goes Wrong</h2>
<p>Error handling with PDO depends on the attributes set for the connection. This can be set as exampled below:</p>
{% highlight php %}
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
{% endhighlight %}
<p>By default PDO::ATTR_ERRMODE is set to PDO::ERRMODE_SILENT. In PDO::ERRMODE_SILENT most methods in the PDO and
PDOStatement classes will return FALSE on error, and then to obtain information regarding the error the method errorInfo
will need to be called on the PDO or PDOSTatement object as appropriate.</p>
<p>The other useful error mode is PDO::ERRMODE_EXCEPTION. This is useful if you are using exceptions for error handling
else where in your application. Furthermore, uncaught exceptions tend to be very vocal about a problem and instead of
checking for false returns and calling errorInfo every statement you can just wait for the exception to show for that
time a quote was slightly off.</p>
</section>
<footer>
<p><small>Hosted on GitHub Pages — Theme by <a href="http://twitter.com/#!/michigangraham">mattgraham</a></small></p>
</footer>
</div>
<!--[if !IE]><script>fixScale(document);</script><!--<![endif]-->
</body>
</html>