Class Days
26 SQL

Class 26: SQL

Reminders

  • Remember that with Web Development, I'm not going to be able to cover everything. There is simply too much!
  • Some of you have wanted to work with Databases, but realize that databases are also just files, and for now, from what I have taught you, you all should be able to do 80% of what a database would be able to do merely by reading and writing to files.

Instructions

Here is also a good tutorial on PDO (opens in a new tab), for those of you that are interested.

Apologies for how grainy these videos are, but if it helps any, you have the file that is referenced here, so you should still be able to review the code and follow along.

Update for Google Cloud

Because of how Google Cloud does not allow our containers to have persistent storage, this means that unless we have some additional workarounds we will not have persistent storage in the cloud. However, this may be ok as it still allows us to have a proof of concept and read data from a database.

This is all that is required for the course, to be able to write locally and then have it read from the database on the cloud.

To get this to work in a way that will work both locally and in google cloud, I think the easiest way to do this is to have a file like this

<?php
    $SEPARATOR = '/';
    $DB_NAME = $SEPARATOR . 'db.sqlite';
    $DB_DRIVER = 'sqlite:';
 
// Check to see if we are in Google Cloud
    $isCloudRun = getenv('K_SERVICE') !== false;
    $writableDir = $isCloudRun ? '/tmp' : __DIR__;
 
// Figure out the correct database path
    $sourceDb = __DIR__ . $DB_NAME;
    $dbPath = $isCloudRun ? $writableDir . $DB_NAME : $sourceDb;
 
// Handle database initialization
    if (!file_exists($dbPath)) {
        if ($isCloudRun && file_exists($sourceDb)) {
            // Copy the database to a writable location for Cloud Run
            if (!copy($sourceDb, $dbPath)) {
                die("Failed to copy database file to writable directory.");
            }
        } else {
            // Create a new database if not found
            $db = new PDO($DB_DRIVER . $dbPath);
            $db->exec("CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT)");
        }
    }
 
    $db = $db ?? new PDO($DB_DRIVER . $dbPath);

Note The above code was modified from a chatGPT prompt

Then in other files you would just use something like the require_once (opens in a new tab) keyword at the beginning of any files that need to interact with your database, which here would be the $db. This means you can just start doing SQL statements directly on the $db variable, since require_once has already imported the var (PHPStorm just doesn't know about your $db var, silly computers).

// make sure to put this inside of a PHP block!
require_once 'db.php';
 
// After this you would just be able to use the $db variable
// ignore the PHPStorm error it provides
// $db->prepare(....) etc

To get it to write to a database, our best bet would be to use another service like Neon.tech (opens in a new tab) to make a postgres sql database and then to use a URL (with a username and password to connect to the database) Please see neon tech's docs (opens in a new tab) for best practices as we don't want to store the username and password into our github repo!

Databases Intro

As mentioned before, this brief intro video (opens in a new tab) goes over some initial concepts related to PDO (opens in a new tab) and SQLite (opens in a new tab).

Think of PDO as a middleware that allows you to connect between various database vendors and PHP Code. As middleware, it also provides us with a more secure way of working with databases. It would be wise to look at some of the different PHP docs related to PDO, including:

Database Debugging

The next video (opens in a new tab) covers some topics related to debugging Databases.

Make sure that you get from the video how to use PHPStorm's various capabilities to set breakpoints and explore results more widely!

*And here is some additional documentation on fetchArray (opens in a new tab) from the SQLite3 Result (opens in a new tab) class and more generally a guide on SQLite 3 (opens in a new tab)

25 Events27 Jsplus