forked from b2much/gdi-intro-php-mysql
-
Notifications
You must be signed in to change notification settings - Fork 0
/
class3.html
434 lines (404 loc) · 18.4 KB
/
class3.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
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
<!doctype html>
<html lang="en">
<head>
<title>Class 3 ~ PHP/MySQL ~ Girl Develop It</title>
<meta name="description" content="This is a modification of the official Girl Develop It PHP/MySQL curriculum, developed by Sylvia Richardson for the Raleigh/Durham chapter.
The course is meant to be taught in 4 two-hour sections. Each of the slides and practice files are customizable according to the needs of a given class or audience.">
<meta name="author" content="Girl Develop It">
<meta name="apple-mobile-web-app-capable" content="yes" />
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent" />
<link rel="stylesheet" href="css/custom.css">
<link rel="stylesheet" href="css/reveal.css">
<link rel="stylesheet" href="css/theme/simple.css" id="theme">
<!-- For syntax highlighting -->
<!-- light editor<link rel="stylesheet" href="lib/css/light.css">-->
<!-- dark editor--><link rel="stylesheet" href="lib/css/dark.css">
<link rel="stylesheet" href="lib/css/zenburn.css">
<link rel="stylesheet" href="plugin/accessibility-helper/css/accessibility-helper.css">
<!-- If the query includes 'print-pdf', include the PDF print sheet -->
<script>
if( window.location.search.match( /print-pdf/gi ) ) {
var link = document.createElement( 'link' );
link.rel = 'stylesheet';
link.type = 'text/css';
link.href = 'css/print/pdf.css';
document.getElementsByTagName( 'head' )[0].appendChild( link );
}
</script>
<!-- If use the PDF print sheet so students can print slides-->
<link rel="stylesheet" href="css/print/pdf.css" type="text/css" media="print">
<link rel="icon" type="image/x-icon" href="favicon.ico" />
<!--[if lt IE 9]>
<script src="lib/js/html5shiv.js"></script>
<![endif]-->
</head>
<body>
<div class="reveal">
<!-- Any section element inside of this container is displayed as a slide -->
<div class="slides">
<!-- Opening slide -->
<section>
<section>
<img src="img/circle-gdi-logo.png" alt="GDI Logo" class="noborder" style="max-height:400px;">
<h3>Intro to PHP and MySQL</h3>
<h4>Class 3</h4>
</section>
</section>
<!-- Welcome-->
<section id="welcome">
<section>
<h3>Welcome!</h3>
<div class = "left-align">
<p>Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on instruction.</p>
<p class ="green">Some "rules"</p>
<ul>
<li>We are here for you!</li>
<li>Every question is important.</li>
<li>Help each other.</li>
<li>Have fun.</li>
</ul>
</div>
</section>
</section>
<!-- Introduction -->
<section id="intro">
<section>
<h3>MySQL</h3>
<p>WAMP/MAMP includes three components</p>
<ol>
<li><span class="blue">A</span>pache - A web server</li>
<li><span class="blue">M</span>ySQL - Database software</li>
<li><span class="blue">P</span>HP - The PHP processor</li>
</ol>
<p>We have been using Apache and PHP. Today, we'll learn about MySQL.</p>
</section>
<section>
<h3>Today's code</h3>
<p>We'll be looking at a lot of code today. You can download a copy to work from, as a <a href="class3-codesample.txt">txt file</a> or a <a href="class3-codesample.zip">zip file</a>.</p>
</section>
</section>
<!-- What Are Databases-->
<section>
<section>
<h3>A database is a way to store information...</h3>
<img src="img/file-cabinet.jpg" alt="File cabinet"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/victoriabernal/6294851265/" target="_blank">victoriabernal</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>...and link it together</h3>
<img src="img/daisy-chain.jpg" alt="Daisy chain"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/55919472@N00/3538996117/" target="_blank">skittledog</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Data are stored in linked tables</h3>
<img src="img/chained-tables.jpg" alt="Tables attached by a chain"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/leithcote/4240469304/" target="_blank">Antony Oliver</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Instead of one giant, repetitive table...</h3>
<img src="img/large-table.png" alt="One big table for checkouts, books, and patron names"/>
</section>
<section>
<h3>...you break things into smaller tables</h3>
<img src="img/small-tables.png" alt="Seperate tables for patron, books, and checkout, linked by ID number"/>
</section>
<section>
<h3>Why?</h3>
<ul>
<li>Thoroughness</li>
<li>Consistency</li>
<li>Avoiding human error</li>
<li>Working with dynamic content</li>
</ul>
</section>
</section>
<!--Using MySQL-->
<section>
<section>
<h3>What is MySQL?</h3>
<ul>
<li>A relational database</li>
<li>Query-driven</li>
<li>BFFs with PHP</li>
</ul>
</section>
<section>
<h3>MySQL data types</h3>
<ul>
<li>VARCHAR - String up to 256 characters</li>
<li>INT - Integer of up to 11 characters</li>
<li>FLOAT - Decimal of any size</li>
<li>TEXT - Large amounts of text, no limit in size</li>
<li>DATE / TIME - date entries with specific formats</li>
</ul>
</section>
<section>
<h3>Working with MySQL</h3>
<p>Most relational databases, including MySQL, use a language called SQL (Structured Query Language). Here are some basic commands:</p>
<ul>
<li>SELECT - get data from a database table</li>
<li>UPDATE - change data in a database table</li>
<li>DELETE - remove data from a database table</li>
<li>INSERT INTO - insert new data in a database tab</li>
</ul>
</section>
</section>
<!--Walk through/Tutorial-->
<section>
<section>
<h3>Walkthrough</h3>
<p>We will create a database for a coffee shop</p>
<img src="img/coffee.jpg" alt="Cup of coffee"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/ahmedrabea/274197870/" target="_blank">Ahmed Rabea</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Let's Develop It</h3>
<p>Let's create our first database. Start up WAMP/MAMP and go to <a href="http://localhost/phpmyadmin/">http://localhost/phpmyadmin/</a> (Windows) or <a href="http://localhost:8888/phpmyadmin/">http://localhost:8888/phpmyadmin/</a> (Mac)</p>
<p>The default username is <span class="blue">"root"</span>. The default password is either <span class="blue">""</span> (i.e., leave it blank) or <span class="blue">"root"</span>.</p>
</section>
<section>
<h3>Find the Database Option</h3>
<img src="img/phpMyAdmin-intro.png" alt="PHP MyAdmin intro screen"/>
<pre><code class="sql">C:\wamp\bin\mysql\mysql15.6.12\bin>mysql.exe -u root
</code></pre>
</section>
<section>
<h3>Create a new DB called "coffee"</h3>
<img src="img/phpMyAdmin-createDB.png" alt="Create database screen"/>
<pre><code class="sql">CREATE DATABASE coffee;
</code></pre>
</section>
<section>
<h3>Select your database</h3>
<img src="img/phpMyAdmin-useDB.png" alt="Select the database"/>
<pre><code class="sql">USE coffee;
</code></pre>
</section>
<section>
<h3>Create a table called "product"</h3>
<img src="img/phpMyAdmin-createTable.png" alt="Create table screen"/>
<pre><code class="sql">CREATE TABLE product (
productID int,
company varchar(255),
type varchar(255),
roast varchar(255),
description text
);
</code></pre>
</section>
<section>
<h3>Add your columns</h3>
<ul>
<li>productID - int</li>
<li>company - varchar - 255</li>
<li>type - varchar - 255</li>
<li>roast - varchar - 255</li>
<li>description - text - 255</li>
</ul>
</section>
<section>
<h3>Add your columns</h3>
<img src="img/phpMyAdmin-createTableColumns.png" alt="Adding columns"/>
</section>
</section>
<!--Connecting to DB-->
<section>
<section>
<h3>Let's get connected</h3>
<p>Create a new file called db.inc.php and place it in the same folder as index.php</p>
<pre><code class="php">$link=mysqli_connect ('localhost', 'root', '');
if (!$link){
$output='Unable to connect to the database';
echo $output;
exit();
}
if (!mysqli_set_charset($link,'utf8')) {
$output = 'Unable to set database connection encoding.';
echo $output;
exit();
}
if (!mysqli_select_db($link, 'coffee')){
$output = 'Unable to locate the database.';
echo $output;
exit();
}
</code></pre>
</section>
<section>
<h3>What just happened?</h3>
<p>We connected to MySQL with our username and password, set our character set (the group of characters we will use), and chose the coffee database. We included some error messages as needed.</p>
</section>
</section>
<!-- Adding data-->
<section id="adddata">
<section>
<h3>Adding data</h3>
<p>You add data to a MySQL database with a statement like this:</p>
<pre><code class="sql">INSERT INTO table_name
VALUES (value1,value2,value3,...);
</code></pre>
<p>But first, we have to collect some data from our users</p>
</section>
<section>
<h3>Let's make a form!</h3>
<pre><code class="html"><form action="product_insert_result.php" method="get">
Company: <input type="text" name="company"/><br/>
Type: <input type="text" name="type"/><br/>
Roast:
<input type="radio" name="roast" value="light">Light</input>
<input type="radio" name="roast" value="medium">Medium </input>
<input type="radio" name="roast" value="dark">Dark</input>
<br/>
<textarea name="description" rows="10" cols="40"></textarea><br/>
<input type="submit" value="Submit"/>
</form>
</code></pre>
</section>
<section>
<h3>What just happened?</h3>
<p>We made a basic HTML form to collect some data, and then send it to the file product_insert_result.php. Of course, that does not exist yet.</p>
</section>
<section>
<h3>Form processing</h3>
<p>Create a new file called product_insert_result.php and place it in the same folder as index.php</p>
<pre><code class="php">include 'db.inc.php';
$company = mysqli_real_escape_string($link, $_GET['company']);
$type = mysqli_real_escape_string($link, $_GET['type']);
$roast = mysqli_real_escape_string($link, $_GET['roast']);
$description = mysqli_real_escape_string($link, $_GET['description']);
</code></pre>
</section>
<section>
<h3>What just happened?</h3>
<p>We used an <span class="blue">include</span> command to reuse our "connect to DB" code. Then, we assigned the values from the form to variables. We used the command <span class="blue">mysqli_real_escape_string</span> to prep the data for MySQL, so it won't barf it back.</p>
</section>
<section>
<h3>Inserting data</h3>
<p>Right below the previous code, add:</p>
<pre><code class="php">$sql = "INSERT INTO product SET
company='$company',
type='$type',
roast='$roast',
description='$description'";
if (!mysqli_query($link, $sql)){
$error = 'Error adding submitted data: '
. mysqli_error($link);
echo $error;
exit();
}
header('Location:product_show.php');
</code></pre>
</section>
<section>
<h3>What just happened?</h3>
<p>We created a string called $sql and used it to build an SQL statement. (Note the careful use of quotes!) We then passed that string to the database, with an error message just in case. Finally, we told the form to send the user to product_show.php.</p>
</section>
</section>
<!-- Displaying data -->
<section>
<section>
<h3>Let's get our coffee back!</h3>
<img src="img/drinking-coffee.jpg" alt="Child drinking coffee"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/thirtysixmillion/4232830060/" target="_blank">Jason Chamberlain</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Retrieving data</h3>
<p>You retrieve data from a MySQL database with a statement like this:</p>
<pre><code class="sql">SELECT column_name(s)
FROM table_name;
</code></pre>
</section>
<section>
<h3>Building the select statement</h3>
<p>Create a new file called product_show.php and place it in the same folder as index.php</p>
<pre><code class="php">include 'db.inc.php';
$sql='SELECT productID, company, type, roast, description
FROM product ORDER BY productID DESC';
$result = mysqli_query($link, $sql);
if (!$result) {
$error = 'Error fetching data: ' . mysqli_error($link);
echo $error;
exit();
}
</code></pre>
</section>
<section>
<h3>What just happened?</h3>
<p>Again, we used the <span class="blue">include</span> to reuse code. We followed the same process as before to send a request to the DB; make a statement, send it to the DB, and include an error message. We stored the results in a variable called <span class="blue">$result</span>.</p>
</section>
<section>
<h3>Displaying data</h3>
<p>Right below the previous code, add:</p>
<pre><code class="php">while($recording=mysqli_fetch_array($result)){
$id=htmlspecialchars($recording['productID'],
ENT_QUOTES, 'UTF-8');
$company= htmlspecialchars($recording['company'],
ENT_QUOTES, 'UTF-8');
$type=htmlspecialchars($recording['type'],
ENT_QUOTES, 'UTF-8');
$roast=htmlspecialchars($recording['roast'],
ENT_QUOTES, 'UTF-8');
$description=htmlspecialchars($recording['description'],
ENT_QUOTES, 'UTF-8');
echo $company . ' ';
echo $type . ' ';
echo $roast . ' ';
echo $description . '<br />';
}
echo 'Return to <a href="index.php">home</a>.'
</code></pre>
</section>
<section>
<h3>What just happened?</h3>
<p>Return of the loop! We used a while loop to go through the results and put them out on the screen. The <span class="blue">htmlspecialchars</span> function helps clean the data. Then, we added a link back to the home page.</p>
</section>
</section>
<!-- Final slides-->
<section id="final">
<section>
<h3>You did it!</h3>
<img src="img/fireworks.jpg" alt="Cup of coffee"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/markwdw85/9425240584/" target="_blank">Mark W.</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Resources</h3>
<ul>
<li><a href="http://www.php.net/manual/en/" target="_blank">PHP Manual</a>, the official PHP documentation. Check the comments; they are useful.</li>
<li><a href="http://www.codecademy.com/tracks/php" target="_blank">Code Academy</a>, with interactive PHP lessons to help you review.</li>
<li><a href="http://www.w3schools.com/sql/default.asp" target="_blank">W3Schools SQL Tutorial</a>, a great guide to basic SQL.
</ul>
</section>
</section>
</div>
<footer>
<div class="copyright">
PHP/MySQL ~ Girl Develop It
<a rel="license" href="http://creativecommons.org/licenses/by-nc/3.0/deed.en_US"><img alt="Creative Commons License" style="border-width:0" src="http://i.creativecommons.org/l/by-nc/3.0/80x15.png" /></a>
</div>
</footer>
</div>
<script src="lib/js/head.min.js"></script>
<script src="js/reveal.min.js"></script>
<script>
// Full list of configuration options available here:
// https://github.com/hakimel/reveal.js#configuration
Reveal.initialize({
controls: true,
progress: true,
history: true,
center: true,
transition: 'slide', // none/fade/slide/convex/concave/zoom
// Optional reveal.js plugins
dependencies: [
{ src: 'lib/js/classList.js', condition: function() { return !document.body.classList; } },
{ src: 'plugin/markdown/marked.js', condition: function() { return !!document.querySelector( '[data-markdown]' ); } },
{ src: 'plugin/markdown/markdown.js', condition: function() { return !!document.querySelector( '[data-markdown]' ); } },
{ src: 'plugin/highlight/highlight.js', async: true, condition: function() { return !!document.querySelector( 'pre code' ); }, callback: function() { hljs.initHighlightingOnLoad(); } },
{ src: 'plugin/zoom-js/zoom.js', async: true },
{ src: 'plugin/notes/notes.js', async: true },
{ src: 'plugin/accessibility-helper/js/accessibility-helper.js', async: true, condition: function() { return !!document.body.classList; } }
]
});
</script>
</body>
</html>