Id from GET

In the last lesson, we hard coded the wombat id:

  • $stmt->execute(['id' => 3]);

Suppose we want to let the user type in an id, at the end of the URL.

  • ...?id=5
Show all

Variable id

Here's the code:

  1. <?php
  2. // Get the id of the wombat the user wants to see.
  3. $id = $_GET['id'];
  4. ...
  5. // Prepare SQL.
  6. $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  7. // Run it.
  8. $stmt->execute(['id' => $id]);
  9. ...
  10. <body>
  11.     <h1>Wombat <?php print $id; ?></h1>
  12.     <p>Name: <?php print $row['name']; ?></p>
  13.     <p>Weight: <?php print $row['weight']; ?></p>
  14.     <p>Comments: <?php print $row['comments']; ?></p>
  15. </body>
  • Get the id from the URL (line 3)
  • Make a placeholder for the id in the SQL (line 6)
  • Run the query with the placeholder (line 8)

I also output all of the fields, not just the name.

You can try it. Try different ids.

Show all

With validation

Let's validate the id. Try this.

Mess with the id, and see what happens.

Show all

Here's the code. I'm using validation functions here.

  1. <?php
  2. // To connect to a DB, need DB name, user name, and password.
  3. $dbName = 'wombats';
  4. $dbUserName = 'something';
  5. $dbPassword = 'something';
  6. // Create DSN.
  7. $dsn = "mysql:host=localhost;dbname=$dbName";
  8. // Connect.
  9. global $dbConnection;
  10. $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);
  11. $errorMessage = '';
  12. // Get the id of the wombat the user wants to see.
  13. $id = getParamFromGet('id');
  14. $errorMessage = checkWombatId($id);
  15. if ($errorMessage == '') {
  16.     // Prepare SQL.
  17.     $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  18.     // Run it.
  19.     $isQueryWorked = $stmt->execute(['id' => $id]);
  20.     if (!$isQueryWorked) {
  21.         $errorMessage = 'Sorry, something went wrong looking up the wombat.';
  22.     }
  23.     else {
  24.         $row = $stmt->fetch();
  25.     }
  26. }
  27. ?><!DOCTYPE html>
  28. <html lang="en">
  29.     <head>
  30.         <meta charset="UTF-8">
  31.         <title>Wombat query</title>
  32.         <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  33.         <link rel="stylesheet" href="styles.css">
  34.     </head>
  35.     <body>
  36.         <h1>Wombat query</h1>
  37.         <?php
  38.         if ($errorMessage != '') {
  39.             print "<p class='error-message'>$errorMessage</p>";
  40.         }
  41.         else {
  42.             print "<p>Id: $id</p>";
  43.             print "<p>Name: {$row['name']}</p>";
  44.             print "<p>Weight: {$row['weight']}</p>";
  45.             print "<p>Comments: {$row['comments']}</p>";
  46.         }
  47.         ?>
  48.     </body>
  49. </html>
  50. <?php
  51.  
  52. /**
  53.  * Get a value for parameter in the GET array.
  54.  * @param string $paramName Name of the parameter.
  55.  * @return string|null Value, or null if not found.
  56.  */
  57. function getParamFromGet(string $paramName) {
  58.     $returnValue = null;
  59.     if (isset($_GET[$paramName])) {
  60.         $returnValue = $_GET[$paramName];
  61.         if ($returnValue == '') {
  62.             $returnValue = null;
  63.         }
  64.     }
  65.     return $returnValue;
  66. }
  67.  
  68. /**
  69.  * Check a wombat id.
  70.  * @param mixed $id Wombat id to check.
  71.  * @return string Error message, MT if none.
  72.  */
  73. function checkWombatId($id) {
  74.     global $dbConnection;
  75.     $errorMessage = '';
  76.     if (is_null($id)) {
  77.         $errorMessage ='Sorry, you must give a wombat id';
  78.     }
  79.     if ($errorMessage == '') {
  80.         // Check id is numeric.
  81.         if (!is_numeric($id)) {
  82.             $errorMessage = 'Sorry, wombat id must be a number.<br>';
  83.         }
  84.     }
  85.     if ($errorMessage == '') {
  86.         // Range check.
  87.         if ($id < 1) {
  88.             $errorMessage = 'Sorry, wombat id must be one or more.<br>';
  89.         }
  90.     }
  91.     if ($errorMessage == '') {
  92.         // Existence check.
  93.         // Prepare SQL.
  94.         $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  95.         // Run it.
  96.         $stmt->execute(['id' => $id]);
  97.         // Anything returned?
  98.         if ($stmt->rowCount() == 0) {
  99.             $errorMessage = "Sorry, no wombat has an id of $id.";
  100.         }
  101.     }
  102.     return $errorMessage;
  103. }

DB connection is the same as before. The line...

  • $id = getParamFromGet('id');

... calls the function we made earlier. It returns a string value, or null if the value is missing.

The next line calls the validation function:

  • $errorMessage = checkWombatId($id);

As usual, it returns an error message if something is wrong. If all's well, it returns MT.

Let's take a look at the function. Most if the same as before, but there's some new stuff at the end.

  1. /**
  2.  * Check a wombat id.
  3.  * @param mixed $id Wombat id to check.
  4.  * @return string Error message, MT if none.
  5.  */
  6. function checkWombatId($id) {
  7.     global $dbConnection;
  8.     $errorMessage = '';
  9.     if (is_null($id)) {
  10.         $errorMessage ='Sorry, you must give a wombat id';
  11.     }
  12.     if ($errorMessage == '') {
  13.         // Check id is numeric.
  14.         if (!is_numeric($id)) {
  15.             $errorMessage = 'Sorry, wombat id must be a number.<br>';
  16.         }
  17.     }
  18.     if ($errorMessage == '') {
  19.         // Range check.
  20.         if ($id < 1) {
  21.             $errorMessage = 'Sorry, wombat id must be one or more.<br>';
  22.         }
  23.     }
  24.     if ($errorMessage == '') {
  25.         // Existence check.
  26.         // Prepare SQL.
  27.         $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  28.         // Run it.
  29.         $stmt->execute(['id' => $id]);
  30.         // Anything returned?
  31.         if ($stmt->rowCount() == 0) {
  32.             $errorMessage = "Sorry, no wombat has an id of $id.";
  33.         }
  34.     }
  35.     return $errorMessage;
  36. }

Let's add a check to see whether the wombat exists. Seems reasonable that would be part of a function validating a wombat id. The relevant code is:

  1. // Existence check.
  2. // Prepare SQL.
  3. $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  4. // Run it.
  5. $stmt->execute(['id' => $id]);
  6. // Anything returned?
  7. if ($stmt->rowCount() == 0) {
  8.     $errorMessage = "Sorry, no wombat has an id of $id.";
  9. }

Line 98 has something new:

  • $stmt->rowCount()

That's the number of records in $stmt. If rowCount() is zero, the returned nothing, so the wombat does not exist.

Show all

The PDO connection

There's something else new on line 74:

  • global $dbConnection;

checkWombatId() needs a DB connection to run the SQL. We made one in the main program:

  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

checkWombatId() will need access to $dbConnection. We have two choices. One is to pass $dbConnection to checkWombatId(), like this:

  • $errorMessage = checkWombatId($id, $dbConnection);
  • ...
  • function checkWombatId($id, $dbConnection) {
  •   ...

That would be fine. Except that, later in the course, we're going to make a bunch of functions that need $dbConnection. We could pass $dbConnection to all of them, but it gets messy.

Instead, we'll use a global variable.

When you learned about functions, we talked about scope. The only way for data to get into a function was through params, and the only way out was through the return value.

Variable scope

Global variables are the exception. You can make a variable that everyone shares:

Globals

When one function, or the main program (that's the one in the lower left), changes a global, it changes for everyone. The variable refers to the same memory location, no matter where the reference is.

To make a variable global, add a line:

  • function something($p1, $p2) {
  •   global $thing;
  •   ...
  •  
  • function somethingElse($p1, $p2) {
  •   global $thing;
  •   ...

If you don't declare it as global, the variable stays local.

  • function somethingElseAgain($p1, $p2) {
  •   // This is a local variable.
  •   $thing = 17;
  •   ...

Globals should be used sparingly. We're just going to use one global in the entire course, to pass around $dbConnection. It's used so often, that making it global reduces code complexity.

Here's the main program again:

  1. $dsn = "mysql:host=localhost;dbname=$dbName";
  2. // Connect.
  3. global $dbConnection;
  4. $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

Line 9 makes $dbConnection global, so that it will be available to any code that wants it.

Here's the start of the function.

  1. function checkWombatId($id) {
  2.     global $dbConnection;
  3.     $errorMessage = '';

When a function wants to use a global, it must say so, as in line 74.

Show all

Not just an id

Data in GET can be anything, not just an id. Say you had a DB of goats, and wanted to find the number of underweight goats. You make the page underweight.php, and call it like this: underweight.php?weight=12.

  • <?php
  • $weight = $_GET['weight'];
  • // Validate
  • ...
  • // Make SQL.
  • $sql = 'SELECT COUNT(*) AS underweight FROM goats WHERE weight < :weight';
  • $stmt = $dbConnection->prepare($sql);
  • // Run it.
  • $stmt->execute(['weight' => $weight]);
  • // Get the row returned.
  • $row = $stmt->fetch();
  • // Get the count from the row.
  • $count = $row['weight'];
Show all

Or maybe you have a DB of movie reviews. You want to find how many times a word was used in the comments. You make a page, and call it like this: count-word.php?word=suck, or count-word.php?word=awesome.

In SQL, you can search string fields with a WHERE like this:

  • WHERE name LIKE '%skywalker%'

LIKE '%skywalker%' matches strings that have "skywalker" anywhere in them.

Here's the code for the comments word finder:

  • $word = $_GET['word'];
  • // Validate
  • ...
  • // Make SQL.
  • $sql = "
  •   SELECT COUNT(*) AS num_words
  •   FROM movie_reviews
  •   WHERE comments LIKE :findSpec;
  • ";
  • $stmt = $dbConnection->prepare($sql);
  • // Run it.
  • $stmt->execute(['findSpec' => "%$word%"]);
  • // Get the row returned.
  • $row = $stmt->fetch();
  • // Get the count from the row.
  • $count = $row['num_words'];

So, you can pass in any data you want in GET, and do anything you want with it. Use it in SQL as a primary key, use it to filter rows by any field... whatevs.

Show all

From localhost to Reclaim

Getting your database programs from your PC to Reclaim can be a bit tricky. Here's an explanation.

Show all

Exercises

Exercise

Id from GET

Make a DB about something. Shoes, movies, beer, whatevs. Put one table in it, storing data about something. Give the table at least two fields:

  • An autoincremented INT id, that's a primary key.
  • A name, like "Windsor Oatmeal Stout."

Add some rows to the table.

Write a program that takes an id as a GET param, like the wombat thing, and shows the record with that id.

Include validation functions. Show errors when:

  • The id is missing.
  • The id is not numeric.
  • The id is zero or less.
  • The id does not exist in the table.

Use a global variable for the DB connection.

Submit a URL, and a zip of your files.

Exercise

Supernatural accuracy

​Coach Doc

Supernatural is Cthulhu's fave workout app, for the Oculus Quest. That's Coach Doc, lunging to hit some virtual targets.

Some of Cthulhu's goaty friends use the app, too. Each workout is scored on accuracy and power, numbers from 0 to 100.

The latest workout scores have been put into a database called supernatural. There's one table, called workout. Here's some of its data:

Data

Download a zip that you can import into a database, to recreate the tables and data. So, make the database in phpMyAdmin, then use the import tab to import the file you downloaded.

Write a program to help Cthulhu see how many goats meet a minimum accuracy standard. The minimum comes through the URL, like this:

  • something.php?min=90
Show all

What now?

Let's hide the DB connection data. It'll make the program more secure, and easier to deploy. Then we'll come back to some last SQL stuff.