Unknown column cash php?

Question

When I try to test register.php for pset7, the website says that there is an unknown column cash in the error. Can anyone see what is my mistake? Here is the part of my coding with this error:

$rows = query("SELECT * FROM users WHERE username = ?", $_POST["username"]);

if (count($rows) == 1)
{
    apologize("Specified username already exists.");
}

if (query("INSERT INTO users (username, hash, cash) VALUES('?', '?', '10000.00')", $_POST["username"], crypt($_POST["password"])) !== false)
{
    // registers user and redirects to homes
    $rows = query("SELECT LAST_INSERT_ID() AS id");
    $id = $rows[0]["id"];
    $_SESSION["id"] = $id;

    query("INSERT INTO questions (id, questionid, answer) VALUES(?, ?, ?)", $_SESSION["id"], $_POST["questionid"] , crypt($_POST["answer"]));   

    redirect("/");
} 
else
{
    apologize("Registration failed, please try again");
} 

Answer 1

You need to make sure you have added the column cash to the users table as described in the pset 7 specification:

Okay, let’s give each of your users some cash. Assuming you’re still on phpMyAdmin’s Structure tab, you should see a form with which you can add new columns. Click the radio button immediately to the left of After, select hash from the drop-down menu, as in the below, then click Go.

Via the form that appears, define a field called cash of type DECIMAL with a length of 65,4, with a default value of 0.0000, and with an attribute of UNSIGNED, as in the below, then click Save.

---

You don't need quotes around placeholder values in a parameterized query. This is what Chris meant in the following comment (emphasis mine):

@Qiu The ? is not a parameter. It is the string literal "?". This query will insert a row in the users table with the "?" values for the username, hash, and cash columns. So the correct form is to omit the single quotations.

The point of using the ? characters as placeholders in the query string and then supplying the values as arguments to query() is that the function will format the statement as appropriate for whatever values you supply.

You can improve this code:

if (query("INSERT INTO users (username, hash, cash) VALUES('?', '?', '10000.00')", $_POST["username"], crypt($_POST["password"])) !== false)
{
    // if statement body
}

In a few ways. First, break it up into several lines. You're doing several things here, and when you get an error message that tells you what line is at fault, one big line means more work to figure out which part is broken.

Second, as long as you're using a parameterized query, it makes sense to insert all the values as parameters, not just some of them. Although you can save some lines of code by hard-coding the value 10000.00, your priority while learning should be making your code as readable and understandable as possible; not as brief as possible.

Here's how I would suggest rewriting that line:

$stmt = "INSERT INTO users (username, hash, cash) VALUES (?, ?, ?)";
$starting_cash = 10000.00;
$result = query($stmt, $_POST["username"], crypt($_POST["password"]), $starting_cash);
if ($result !== false)
{
    // if statement body
}

This way you can see at a glance what the query is that's going to be executed for this section, without having to even think about the values. You can see the reason that 10000.00 is being inserted, because it's named $starting_cash. And you can see exactly what's being tested -- the result of the query. Much more readable, much more understandable, probably easier to debug.

To fix the syntax error, make sure you don't have any extra or missing quotation marks, commas or parentheses in your query. Declaring the query statement in a separate variable can help here, too.