CODEWITHSUNDEEP
phpmysqlforms
rgdigi
rgdigi

Reputation: 1747

MYSQL error with PHP form

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

Upvotes: 1

Views: 282

Answers (5)

Wesley
Wesley

Reputation: 2200

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());

Upvotes: -1

CD001
CD001

Reputation: 8472

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';
  }
}

Upvotes: 1

iThink
iThink

Reputation: 1259

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

Upvotes: 1

imm
imm

Reputation: 5919

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

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

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.

Upvotes: 2

Related Questions