Week 8, continued

The web server in Problem Set 6 demonstrates writing software that knows how to take HTTP requests from a browser (or a human with a program called telnet), and respond to those requests with an HTML, JPG, or PHP file.

But a web server shouldn’t return a raw PHP file, but interpret it first.

We took care of that part for you in Problem Set 6, so all you had to do was support static content, among other things.

In Problem Set 7, we’ll start to write the PHP code that gets interpreted, talking to the back-end database that stores our information.

Remember from last time that we have these superglobals:

On Monday we used $_GET, which had all the parameters we put in the query string. When we implemented our own version of Google, the URL had a question mark and then q= (like https://www.google.com/search?q=cats). The question mark indicated the start of the query string q=cats, and $_GET would automatically have a key named q with the value cats.

All of these superglobals are associative arrays, or hash tables that store keys and values.

In Problem Set 5, the hash table or trie you implemented was really an associative array where the keys were associated with values of true or false, whether the word was in the dictionary or not.

But we can associate more interesting values with keys, and return arbitrary strings, which is what $_GET and these other variables allow us to do.

$_POST stores forms sent by the POST method, and files are actually stored in a variable not listed, $_FILES.

$_SERVER gives you details about the server, which we won’t go into detail about.

$_COOKIE and $_SESSION are what we need to implement things like a simple shopping cart.

Last time we had an example of a counter.php file that counted how many times we visited the page:

We can open Chrome’s Developer Tools, but first clear the cache:

We’ll do this for debugging purposes, getting rid of cookies, a piece of data that a server asks to put on your computer, to remember who you are.

After you log into a website like Gmail or Facebook, the server remembers that you are logged in, even though you’re not typing in your username and password at every page. Cookies are the answer. They are like a digital handstamp that you might get at an amusement park or club, that indicates you’ve already showed your ID, and have been identified already.

We can reload counter.php and view the request as we’ve done before (clicking view source next to Request Headers to see the raw request):

But if we scroll down to Response Headers and click view source, we see:

Notice that the server doesn’t store our username or (god forbid) our password, but rather a pseudorandom piece of information so our login info isn’t saved there, to prevent other people from finding it. The server, on the other hand, remembers any personal information that should be associated with 0vlk8t…​.

So every time we reload the page, the server knows how many times we’ve visited. After we reload the page, we look at Request Headers again, and we see this time:

In the source code of counter.php, we indeed store a $counter variable in $_SESSION:

<?php

    // enable sessions
    session_start();

    // check counter
    if (isset($_SESSION["counter"]))
    {
        $counter = $_SESSION["counter"];
    }
    else
    {
        $counter = 0;
    }

    // increment counter
    $_SESSION["counter"] = $counter + 1; (17)

?>

So that’s how $_SESSION works under the hood. Modern websites today might set half a dozen cookies or more, and troublingly, if a central party is serving advertisements embedded in multiple websites, then the cookies (which are per domain) will allow that central party to track who you are and what you’re visiting.

If you’re super paranoid (like Cheng, who doesn’t even have a Facebook) and turn off your cookies, lots of websites will stop working, because many websites require cookies for you to log in, and have no other way of remembering who you are.

So let’s take for granted that we can remember information like that.

In the Frosh IMs example from last time, all we did was email the information that a user submitted, like the name, gender, and dorm, to the proctor (or John Harvard, as we were testing).

We can do that better, by using SQL, Structure Query Language, read as "sequel." This is a language we use to talk to a database, which is like an Excel (or Numbers or Google Sheets) file. Those files are essentially relational databases, which have rows and columns that store information. SQL, however, is special because we can execute queries, commands to sort or remove or look for data, programmatically.

SQL has fundamental statements like:

SQL operates entirely on rows, so SELECT for example would return a result set, or an array of rows.

We can communicate with our database in a Terminal window:

But that’s not particularly fun. A graphical user interface, GUI, is much better:

The tool we recommend and have preinstalled in the appliance is phpMyAdmin. The "php" in the name refers to the language it was written in, and the tool administers a MySQL server.

On the left we see a list of databases that we have in the appliance (or a server on the Internet, in the case of many final projects), and here we only have the pset7 database.

On the top there are a bunch of tabs, Databases, SQL, Status, Users, etc.

In Problem Set 7 we’ll be making a website called C$50 Finance, which lets you "buy" and "sell" stocks. It’ll get prices from Yahoo! Finance which has a free service where we can pass in a stock ticker, like GOOG for Google, Inc., and it will give you back the current stock price for Google. So we’ll use that to allow users to pretend to buy and sell stocks with virtual money.

The first screen that users will see is this:

Let’s look more closely at databases. In the appliance, we can go to localhost/phpmyadmin/, and click the + next to pset7:

A table is like an individual spreadsheet with its own rows and columns, represented by the tabs along the bottom of the screen in Excel or Numbers files. A database is just made up of one or more of these tables.

If we click on users and scroll down a bit, this is what that table looks like:

We see that there are columns id, username, and hash, and in Problem Set 7 we give you this information to start you off. Notice that there are 6 usernames, each with unique IDs from 1 to 6. To the right are hashes, or basically encrypted passwords. (The hashes and names are from Problem Set 2’s Hacker Edition.) And to the left are links to GUI operations, like editing, copying, or deleting rows.

We can now click on the SQL tab and try out queries. (Remember that phpmyadmin is just a tool for us to poke around, but we’ll really program all of these queries into our .php files when we want to use them.) Let’s take a look at that tab:

The big text box is where we can input a query, and let’s try to run this:

We can scroll down and click GO, and we’ll see this:

Now let’s try to run this:

And when we run it we get a warning, to be sure we really wanted to DELETE something:

We click OK, but when we go back to the users table, it looks exactly the same:

Turns out we made a couple of mistakes. The column is named username, not name, and her username is stored as zamyla with a lowercase, not uppercase, "z."

So let’s try this again:

And now we actually see a confirmation page that tells us we completed this query successfully:

To be sure, we can go back to our table and see all the rows:

We can also register Gabe. We can do something like this:

We click GO, and we get:

And when we go back to the table, we see that his id is actually:

As an aside, Facebook has an API where you can get all sorts of data about users. Mark Zuckerberg’s account had an ID of 3, with users 1 and 2 being test accounts. And all of us have numbers much larger. (In fact, they’ve moved from using an int to the equivalent of a long long so they can eventually accomodate more users.)

So that’s just an introduction to the syntax of SQL, with which we can do many more powerful things.

In Problem Set 7 we’ll allow you to make a number of design decisions, one of which is the types of data to use.

Just like in C, there are datatypes in SQL including:

Let’s go to the lectures database that David has, and create a new table called users with 3 columns:

Once we click GO, we get to a screen that looks like:

Here we can name each column:

And when we click on Type, we get a menu of the whole list of types possible:

We can scroll right and see more fields, which we’ll leave blank since none of them are applicable:

When we get to Attributes, we have a design decision to make, where we can choose UNSIGNED for the id column:

If we keep scrolling right, we see a few more columns:

If we click on Index, we see these options:

The next field, A_I, just means AUTO_INCREMENT, which we want to check so the id column is automatically updated:

And if we go further to the right to the last few columns, not many interesting things are there:

So we can go back to the beginning, and take a look at the types for username and hash:

We’ll scroll a bit to the right, and make a stop at the Index column:

Another design decision you might have is the storage engine you use, but we’ll come back to that someday.

Let’s open this week’s source code in our Terminal:

jharvard@appliance (~/vhosts/localhost): ls
includes  public  templates

Let’s take a quick look at config.php:

<?php

    // display errors, warnings, and notices
    ini_set("display_errors", true);
    error_reporting(E_ALL);

    // requirements
    require("constants.php");
    require("functions.php");

    // enable sessions
    session_start();

?>

We can also open constants.php:

<?php

    // your database's name
    define("DATABASE", "lecture");

    // your database's password
    define("PASSWORD", "crimson");

    // your database's server
    define("SERVER", "localhost");

    // your database's username
    define("USERNAME", "jharvard");

?>

And in the file functions.php, there are lots of functions, but let’s look at function query:

    /**
     * Executes SQL statement, possibly with parameters, returning
     * an array of all rows in result set or false on (non-fatal) error.
     */
    function query(/* $sql [, ... ] */)
    {
        // SQL statement
        $sql = func_get_arg(0);

        // parameters, if any
        $parameters = array_slice(func_get_args(), 1);

        ...

The file also contains function redirect that we can call to send the user to another URL:

     /**
      * Redirects user to destination, which can be
      * a URL or a relative path on the local host.
      *
      * Because this function outputs an HTTP header, it
      * must be called before caller outputs any HTML.
      */
      function redirect($destination)
      {
      ...

render renders a template, but more about these functions in Problem Set 7’s walkthroughs.