MYSQL error with PHP form

Question

This might be a noob question but I can't find anything wrong with my code and I'm still getting mysql errors.

I am using this code to submit a form to a MYSQL database ->

    if($forename == '' || $surname == '' || $email == '' || $address1 == '' || $address2 == '' || $terms == ''){
        $_SESSION['status'] = 'error';
    } else {
            $sql = "INSERT INTO dbEntry ('forename','surname','email','address1','address2','terms')
            VALUES ('$forename','$surname','$email','$address1','$address2','$terms')" or die(mysql_error());
            $results = mysql_query($sql);

            if ($results) { echo "Details added."; }

            //if (mysql_error()) echo mysql_error();

        $_SESSION['status'] = 'success';
    }

//Print query for debugging ->
echo $sql;

But it's throwing this error ->

INSERT INTO dbEntry ('forename','surname','email','address1','address2','terms') VALUES ('asd','asd','[email protected]','asd','as','NULL')
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''forename','surname','email','address1','address2','terms') VALUES ('asd','as' at line 1

I have created a table to this structure ->

id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
forename VARCHAR(32) NOT NULL,
surname VARCHAR(32) NOT NULL,
email VARCHAR(64) NOT NULL,
address1 VARCHAR(64) NOT NULL,
address2 VARCHAR(64) NOT NULL,
terms TINYINT(1)

I can't see any syntax errors, where am I going wrong? Apologies if I included too much info.

Cheers

Michael Berkowski · Accepted Answer · 2011-10-04 11:27:53Z

2

Do not surround the column names in single quotes in the insert column list.

INSERT INTO dbEntry (forename,surname,email,address1,address2,terms) VALUES ('asd','asd','[email protected]','asd','as','NULL')

If your column names happen to be MySQL reserved keywords, or include spaces, hyphens, or other special but valid characters, you must surround them in backquotes:

INSERT INTO tbl (`desc`, `space table`, `etc`)...

As a hint, when MySQL prints a syntax error, 99% of the time the error occurs at the exact character where the right syntax to use near " opening quote begins.

answered Oct 4, 2011 at 11:27

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rgdigi Over a year ago

Fixed it, helpful tip as well. Thanks!

iThink · Accepted Answer · 2011-10-04 11:36:03Z

1

INSERT INTO dbEntry (forename,surname,email,address1,address2,terms) VALUES ('asd','asd','[email protected]','asd','as','NULL')

answered Oct 4, 2011 at 11:36

iThink

1,2593 gold badges13 silver badges34 bronze badges

Comments

CD001 · Accepted Answer · 2011-10-04 12:03:49Z

1

You've got a handful of oddities going on there...

$sql = "INSERT INTO dbEntry ('forename','surname','email','address1','address2','terms')
            VALUES ('$forename','$surname','$email','$address1','$address2','$terms')" or die(mysql_error());

Doesn't actually make sense - you're assigning a value to a string and telling the program to die (with a mysql_error()) if the string is empty - the die() should reflect the response from the mysql_query() function - you want it to die when that function returns false. Oh and as @Michael pointed out - don't wrap your field names in quotes (apostrophes) - if required, use backticks.

if ($results) { echo "Details added."; }
$_SESSION['status'] = 'success';

That means that you'll echo out "Details added" only if the query executes successfully - however you're assigning 'success' to $_SESSION['status'] even if the query fails.

I'd try something more like the following; I've put some escapes in - just in case you'd missed them :) Since PHP is loosely typed empty strings and (int) 0 can be evaluated as (bool) false - so !$forename will be equivalent to $forename == ''.

$forename = mysql_real_escape_string($forename);
$surname = mysql_real_escape_string($surname);
$email = mysql_real_escape_string($email);
$address1 = mysql_real_escape_string($address1);
$address2 = mysql_real_escape_string($address2);
$terms = (int) $terms;

if(!$forename || !$surname || !$email || !$address1 || !$address2 || !$terms) {
  $_SESSION['status'] = 'error';
}

else {
  $sql = "INSERT INTO dbEntry(forename, surname, email, address1, address2, terms) "
       . "VALUES ('{$forename}', '{$surname}', '{$email}', '{$address1}', '{$address2}', {$terms})";

  $response = mysql_query($sql);
  if(!$response) {
    die(mysql_error());
  }
  else {
    echo "Details added";
    $_SESSION['status'] = 'success';
  }
}

edited Oct 4, 2011 at 12:03

answered Oct 4, 2011 at 11:49

CD001

8,5123 gold badges26 silver badges29 bronze badges

2 Comments

rgdigi Over a year ago

Nice, really helpful of you. I've added some of this. Cheers

CD001 Over a year ago

No worries - gave me something to do while I was eating my lunch - back to work now I guess :)

imm · Accepted Answer · 2011-10-04 11:29:00Z

0

It looks like it's trying to insert the string "NULL" in the terms field, which is an int.

answered Oct 4, 2011 at 11:29

imm

5,9391 gold badge29 silver badges32 bronze badges

3 Comments

CD001 Over a year ago

You can enter NULL into an INT field as long as it's the value NULL NOT the string "NULL" (if the field allows NULL of course).

imm Over a year ago

@CD001, The "NULL" is in quotes, though. Won't that represent the string "NULL" rather than the NULL value?

CD001 Over a year ago

yes, which is what I meant - you can use NULL in an INT field but not "NULL" ;)

Wesley · Accepted Answer · 2011-10-04 20:43:29Z

-1

You should use no quotes for you column list. Also use a escape function to make sure you don't have any errors when you put something in the database.

$sql = "INSERT INTO (field, field) VALUES = ('". mysql_real_escape_string( $field ) ."', '". mysql_real_escape_string( $field ) ."')";

Also to get better errors use this in your query function:

mysql_query( $sql ) or trigger_error (mysql_error());

Or depending if you want to keep running your script

mysql_query( $sql ) or die (mysql_error());

edited Oct 4, 2011 at 20:43

answered Oct 4, 2011 at 11:33

Wesley

2,20018 silver badges27 bronze badges

Collectives™ on Stack Overflow

MYSQL error with PHP form

5 Answers 5

1 Comment

Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related