1

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");
} 
14
  • Query only returns a Boolean value if there was an error i.e. 'false'. If, however, the query was successful it returns the values that where effected (known as the result set). Think about this when you consider how you've implemented your if statement.
    – user103853
    Aug 19, 2014 at 5:02
  • Double check cash is named accordingly in your SQL database. In your first prepared statement, you put quotes around the question marks(placeholders) and value for cash but failed to do it the second time. It's important to be consistent.
    – user103853
    Aug 19, 2014 at 5:06
  • 1
    @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. As for the cash column, are you trying to add it in phpMyAdmin? If so, what is the error message you receive while doing so?
    – user103853
    Aug 19, 2014 at 21:06
  • 1
    Actually I figured out the problem. I switched cash and hash and 10000.00 and the ? in the if statement and it fixed that problem. However the new error is: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 in /home/jharvard/vhosts/pset7/includes/functions.php on line 139 Aug 20, 2014 at 19:58
  • 1
    The ?> should be omitted if it is the last thing in the php file: stackoverflow.com/questions/3219383/…. Also, please try to limit your code to the part that's causing the error, this means people can't copy your code, and per academic misconduct. When inserting a new user in your if statement, you've put an extra comma in the values section just after a ?. It's hard to tell what else is wrong. Open phpmyadmin and test the sql statements you've wrote in the sql section but replace the ? with hard coded values to test the syntax.
    – user103853
    Aug 20, 2014 at 21:42

1 Answer 1

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.

Add

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.

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.

7
  • Thanks for helping with my code. I have used what you have posted into my code and it has helped, but now it says on the line of $result = query($stmt, .......); it still has an unknown column "cash" in "field list". I know I am still a noob in PHP... Aug 21, 2014 at 16:09
  • @RichardQiu The unknown column error is coming from MySQL, not from your PHP code. Check the structure of the users table in phpMyAdmin -- have you made any changes? Is there still a column named cash?
    – Air
    Aug 21, 2014 at 16:15
  • I have the column named cash in my portfolio table in phpMyAdmin, not the users. The users table had jharvard, caesar, malan, skroob, zamyla, etc. Aug 21, 2014 at 16:22
  • @RichardQiu Sounds like that's your problem, then. The users table is set up to have the three columns you're inserting into in the statement. I'd recommend keeping cash in the users table -- a portfolio might have value but it's the person who owns the portfolio who has any cash.
    – Air
    Aug 21, 2014 at 16:30
  • But I can not add any columns in the users table. Do I set it up in structure? Aug 21, 2014 at 16:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .