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.