COMP3512
winter 2024
lec-php-08
Buh-bye, January
About that midterm
- You get one 3"x5" index card you can use as a reference guide.
- I have some if you don't have any.
- No MCQ. Short answer, fill in the blanks, and code writing.
- Covers all topics from the start of semester up to and including today.
- Look up PHP's explode() . Almost as good a name as die().
RECALL
Let's do a PDO word web.
Project Requirements
are up
let's talk about these things today:
◉ Let's finish our example from last lecture.
◉ What's a prepared statement - and why should I use them?
◉ Can we clean this up at all?
DB
Let's finish our example from last lecture.
We were making a page that displays all cheeses that are out of stock in the Banff cheese store.
Example, continued.
1-stock
What's a prepared statement - and why should you use them?
Let's build another page.
We want to show all the names of cheeses of a given classification code at a given store that are in stock.
The classification and store ids could come in via a query string or a form, perhaps.
For example, if we were given the store id 1 (Calgary) and classification code "B" , then we'd want to display "Stilton" and "Gorgonzola".
Bringing in outside data.
2-stock-with-outside-data
Then one day, your co-worker shows you this comic.
...and you begin to sweat.
First, you make a dummy database lab_rat with a dummy table.
Then, in your database admin tool of choice, you try out this query...
USE lab_rat;
SELECT cheese.name
FROM cheese
INNER JOIN
inventory ON (cheese.id = cheese_id)
INNER JOIN
classification cl ON (cl.id = classification_id)
WHERE store_id = 1
AND code = 'B'; DROP TABLE cheese; --
AND stock_level > 0
ORDER BY 1...and your heart sinks.
ruh roh
Shaking, you then try THIS query...
USE lab_rat;
SELECT cheese.name
FROM cheese
INNER JOIN
inventory ON (cheese.id = cheese_id)
INNER JOIN
classification cl ON (cl.id = classification_id)
WHERE store_id = 1
AND code = 'B'; DROP DATABSE lab_rat; --
AND stock_level > 0
ORDER BY 1...and you start looking at job listings.
oh dear
Then you go into your code an try this...
Uh-oh.
2-stock-with-outside-data/uh-oh.php
Never trust outside data. Never.
But we NEED outside data for any useful system!
What can we do?
Solution: Prepared Statements
Prepared statements FTW.
2-stock-with-outside-data/whew.php
BRAIN BREAK
Can we clean this up at all?
As it stands, we've got a dumpster fire here.
<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=cheese_db;charset=utf8mb4";
try {
$pdo = new PDO($dsn, "root", "mariadb", [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
} catch (PDOException $e) {
die($e->getMessage());
}
$query = <<<QUERY
SELECT ch.name AS cheese
FROM cheese AS ch
QUERY;
$results = $pdo->query($query);
$pdo = null;
?>
<ul>
<?php foreach ($results as $result) : ?>
<li><?= $result['cheese'] ?> </li>
<?php endforeach ?>
</ul>➀ connect & handle errors
➁ run a query
➃ use results
➂ free resources & close connection
Oh crud - I didn't talk about ➂, did I?
Wet cleanup on aisle 4.
3-cleanup
Having these queries sitting "raw" in our controllers has good points and bad points.
We could make some helper functions that name what we're trying to do with a given query, and group them together in one file.
We could even take that a step further, by making some sort of query helper class....
...which you could take even further by bringing in some inheritance.
lec-php-08
By Jordan Pratt
lec-php-08
prepared statements | our DB library
