forked from b2much/gdi-intro-php-mysql
/
class4.html
406 lines (378 loc) · 18.4 KB
/
class4.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
<!doctype html>
<html lang="en">
<head>
<title>Class 4 ~ 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 4</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>
<!--Application Structure-->
<section>
<section>
<h3>Remember, we want d.r.y. code</h3>
<img src="img/desert-landscape.jpg" alt="Desert landscape"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/philsnyder/4216356826/" target="_blank">Phil Synder</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Application structure</h3>
<p>Some parts of your code are repeated over and over. We can move these into their own files and use <code class="">include</code> to call them.</p>
<ul>
<li>header.php</li>
<li>sidebar.php</li>
<li>footer.php</li>
</ul>
</section>
<section>
<h3>Styling</h3>
<p>Let's improve our coffeeshop page with some styles. <a href="class4-exercises.zip">Download the starter files</a> as a .zip.</p>
<img src="img/styled-site.png" alt="Screenshot"/>
<p>Thanks to <a href="http://amyhendrix.net/">Amy Hendrix</a> for the code.</p>
</section>
<section>
<h3>Let's Develop It</h3>
<p>Play with the site code.</p>
<ul>
<li>Change the page title</li>
<li>Add a link to the sidebar</li>
<li>Add your name to the footer</li>
</ul>
</section>
</section>
<!--Database Relationships-->
<section>
<section>
<h3>Database relationships</h3>
<img src="img/lemurs.jpg" alt="Lemurs hugging"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/ben124/5145427096/" target="_blank">Ben124</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Each table row has a unique primary key</h3>
<img src="img/key.jpg" alt="Single key"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/brenda-starr/3509344100/" target="_blank">Brenda-Starr</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Tables are linked together through their keys</h3>
<img src="img/keyring.jpg" alt="Multiple keys on a ring"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/djbiesack/4775029983/" target="_blank">gnuru</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>The link is called a foreign key</h3>
<img src="img/foreign-key.jpg" alt="Key from Spain"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/firelizard5/10812370016/" target="_blank">Sylvia</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>MySQL can automatically clean up related data</h3>
<img src="img/wall-e.jpg" alt="Wall-E"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/silkegb/2716201447/" target="_blank">silkegb</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
</section>
<!--Walkthrough/Tutorial-->
<section>
<section>
<h3>Let's Develop It</h3>
<p>Let's edit 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="yellow">"root"</span>. The default password is either <span class="yellow">""</span> (i.e., leave it blank) or <span class="yellow">"root"</span>.</p>
</section>
<section>
<h3>Select the coffee database</h3>
<img src="img/phpMyAdmin-useDB.png" alt="Select the database"/>
<pre><code class="sql">USE coffee;
</code></pre>
</section>
<section>
<h3>Delete the product table</h3>
<img src="img/phpMyAdmin-drop.png" alt="Drop table screen"/>
<pre><code class="sql">DROP TABLE product;
</code></pre>
</section>
<section>
<h3>Use SQL to recreate the product table</h3>
<img src="img/phpMyAdmin-SQLqueries.png" alt="SQL screen"/>
<pre><code class="sql">CREATE TABLE product (
productID int NOT NULL AUTO_INCREMENT,
companyID_fk int,
type varchar(255),
roast varchar(255),
description text,
PRIMARY KEY (productID)
);
</code></pre>
</section>
<section>
<h3>Use SQL to create a company table</h3>
<img src="img/phpMyAdmin-SQLqueries.png" alt="SQL screen"/>
<pre><code class="sql">CREATE TABLE company (
companyID int NOT NULL AUTO_INCREMENT,
name varchar(255),
phoneNumber varchar(255),
PRIMARY KEY (companyID)
);
</code></pre>
</section>
<section>
<h3>Create an index on companyID_fk</h3>
<img src="img/phpMyAdmin-addindex.png" alt="Add an index"/>
<pre><code class="sql">ALTER TABLE product ADD INDEX (companyID_fk);
</code></pre>
</section>
<section>
<h3>Open the product table and select relation view</h3>
<img src="img/phpMyAdmin-relationview.png" alt="Add an index"/>
</section>
<section>
<h3>Add a relationship</h3>
<img src="img/phpMyAdmin-addrelationship.png" alt="Add a relationship"/>
<pre><code class="sql">ALTER TABLE product
ADD CONSTRAINT FK_company FOREIGN KEY (companyID_fk)
REFERENCES company (companyID)
ON DELETE CASCADE ON UPDATE CASCADE;
</code></pre>
</section>
<section>
<h3>Add some sample data</h3>
<img src="img/phpMyAdmin-SQLqueries.png" alt="SQL screen"/>
<pre><code class="sql">INSERT INTO company SET name='Starbucks', phoneNumber='(800)555-7282';
INSERT INTO company SET name='Bean Traders', phoneNumber='(919)555-5895';
INSERT INTO company SET name='Mean Beans', phoneNumber='(303)555-8475';
INSERT INTO product SET companyID_fk=1, type='French Vanilla', roast='dark', description='Strong coffee flavor with a hint of vanilla';
INSERT INTO product SET companyID_fk=1, type='Pumpkin spice', roast='medium', description='Seasonal';
INSERT INTO product SET companyID_fk=2, type='Dip Into Decaf', roast='light', description='You will barely notice it\'s coffee!';
</code></pre>
</section>
</section>
<!--Updating submission form-->
<section>
<section>
<h3>Updating our submission forms</h3>
<p>Right now, our product entry form is a free text entry. It looks like this:</p>
<pre><code class="html">Company: <input type="text" name="company"/><br/>
</code></pre>
</section>
<section>
<h3>Restricting choices</h3>
<p>We want it to only show approved companies from our DB, so looks like this:</p>
<pre><code class="html">Company: <select name="company">
<option value="2">Bean Traders</option>
<option value="3">Mean Beans</option>
<option value="1">Starbucks</option>
</select>
</code></pre>
</section>
<section>
<h3>Using DB information in forms</h3>
<p>How can we do that? We can use SQL to ask the database for information:</p>
<pre><code class="sql">SELECT companyID, name FROM company ORDER BY name;
</code></pre>
</section>
<section>
<h3>Using DB information in forms</h3>
<p>Then use a while loop to add this to our page</p>
<pre><code class="php">$sql='SELECT companyID, name FROM company ORDER BY name';
$result = mysqli_query($link, $sql);
if (!$result) {
$error = 'Error fetching data: ' . mysqli_error($link);
echo $error;
exit();
}
while($recording=mysqli_fetch_array($result)){
//Some awesome PHP code here
}
</code></pre>
</section>
<section>
<h3>Let's Develop It</h3>
<p>Your sample code has a file called add-products.php. It is almost complete. Add code to the commented sections to get it to work.</p>
<p>Get stuck? Try looking at view-products.php to see how the while loop works there.</p>
</section>
</section>
<!--URL parameters-->
<section>
<section>
<h3>Passing information</h3>
<img src="img/message-bottle.jpg" alt="Message in a bottle"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/myklroventine/3545127104/" target="_blank">Mykl Roventine</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>URL parameters</h3>
<p>Up until now, we have only passed data via forms. We can also pass data in the URL itself. This is called a parameter.</p>
<pre><code class="string">http://www.website.com/file.php?parameter=value
</code></pre>
</section>
<section>
<h3>Using parameters</h3>
<p>You can retrieve a parameter's value and store it as a variable</p>
<p>If you use a URL like this:</p>
<pre><code class="string">http://www.website.com/file.php?parameter=value
</code></pre>
<p>You can retrieve the values like this:</p>
<pre><code class="php">$variableName = htmlspecialchars($_GET["parameter"]);
</code></pre>
</section>
<section>
<h3>Using parameters on our site</h3>
<p>How can we use this? On the view products page, I have set up a delete link for each item.</p>
<img src="img/delete-link.png" alt="Screenshot with delete link highlighted"/>
<p>This link uses a parameter to send the product ID to the delete page.</p>
</section>
<section>
<h3>Let's Develop It</h3>
<p>Your sample code has a file called delete-products.php. It is almost complete. Add code to the commented sections to get it to work.</p>
</section>
</section>
<!--Editing data-->
<section>
<section>
<h3>Putting it all together</h3>
<img src="img/puzzle.jpg" alt="Puzzle pieces"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/emmacherry/2207748365/" target="_blank">echerries</a> <a href="http://creativecommons.org/licenses/by-nc/2.0/" target="_blank">cc</a></p>
</section>
<section>
<h3>Editing data</h3>
<p>Let's combine queries, forms, parameters, and logic all into one. We will</p>
<ol>
<li>Pass a product ID to a page using parameters</li>
<li>Save that parameter as a variable</li>
<li>Use a query to pull the associated data</li>
<li>Pre-load that data into a form</li>
<li>Send the form to processing script</li>
<li>Update the database.</li>
</ol>
</section>
<section>
<h3>Let's build it - Step 1</h3>
<p>On the view products page, I have set up an edit link for each item.</p>
<img src="img/edit-link.png" alt="Screenshot with edit link highlighted"/>
<p>This link uses a parameter to send the product ID to the edit page.</p>
</section>
<section>
<h3>Let's build it - Step 2</h3>
<p>In the file edit-products.php, we will:</p>
<ol>
<li>Retrieve the product ID and save it as a variable</li>
<li>Use the product ID in an SQL query:
<pre><code class="sql">SELECT fields FROM tablename WHERE field='value'
</code></pre>
</li>
<li>Pre-load that data into a form that the user can edit</li>
<li>Send the form data to processing script</li>
</ol>
</section>
<section>
<h3>Let's build it - Step 3</h3>
<p>In the file product_edit_result.php, we will:</p>
<ol>
<li>Take in data from the form</li>
<li>Update the database</li>
<li>Send the user back to the table of products</li>
</ol>
</section>
<section>
<h3>Let's Develop It</h3>
<p>Your sample code has a file called edit-products.php and one called product_edit_result.php. They are almost complete. Add code to the commented sections to get it to work.</p>
</section>
</section>
<!-- Final slides-->
<section id="final">
<section>
<h3>You did it!</h3>
<img src="img/jumping-man.jpg" alt="Man jumping in air"/>
<p class="credit">Photo credit: <a href="http://www.flickr.com/photos/sunface13/988453859/" target="_blank">sunface13</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.</li>
<li><a href="http://overapi.com/" target="_blank">OverAPI</a>, a collection of quick-reference guides.</li>
<li>The <a href="class4-complete.zip">completed site code</a> for today's exercises.</li>
</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>