mysql INSERT INTO query - ERROR with syntax

Question

Can anyone help with advising what may be wrong with my insert into syntax please ? Working except i am receiving empty query message

    // values sent from form
    $first_name=$_POST['first_name'];
    $last_name=$_POST['last_name'];
    $email=$_POST['email'];
    $postcode=$_POST['postcode'];
    $gender=$_POST['gender'];
    $yob=$_POST['yob'];

    /*********** CONNECT TO THE DATABASE ******/
    //Step 1 CONNECT TO THE DATABASE
   $db=mysql_connect ("localhost", “db_username, “db_password);
   if (!$db) {
     die("Database connection failed miserably: " . mysql_error());
     }
     //Step2 SELECT THE DATABASE
     $db_select = mysql_select_db(“db_name,$db);
    if (!$db_select) {
    die("Database selection also failed miserably: " . mysql_error());
    }

    echo "Welcome $first_name!";
    echo " Success, connected to database but maybe not the table";

    // Insert data into database
    //##############################I THINK PROBLEM MUST BE HERE IN THIS    INSERT       STATEMENT STATEMENT###################################
    $sql="INSERT INTO newsletter-subscribers(first_name, last_name, email,    postcode, gender,    yob)VALUES('$first_name','$last_name','$email','$postcode','$gender','$yob')";
    if(mysql_query($sql)){
    echo "Records added successfully.";
    } else{
    echo "ERROR: Could not able to execute $sql. " . mysql_error($db);
    }
    $result=mysql_query($sql);

a little more white space around values ? ah yes the table name becomes a subtraction — Drew
– Drew, Commented Aug 10, 2015 at 2:58
You are open to SQL injections. If any of your values contain quotes this would fail. — chris85
– chris85, Commented Aug 10, 2015 at 3:00

Script47 · Accepted Answer · 2015-08-10 03:19:15Z

4

Your query,

$sql="INSERT INTO newsletter-subscribers(first_name, last_name, email,    postcode, gender,    yob)VALUES('$first_name','$last_name','$email','$postcode','$gender','$yob')";

Your new query,

$sql = "INSERT INTO `newsletter-subscribers` (first_name, last_name, email, postcode, gender, yob) VALUES ('$first_name','$last_name','$email','$postcode','$gender','$yob')";

So, what has changed?

Added ticks around your table name.
Removed spaces which you didn't need to make query clearer.

Without the backticks around your table name, MySQL is treating it as newsletter minus subscribers. Which is wrong, add the ticks to tell MySQL that it is a table name.

Edit 1

This might be a copy & paste error, I'm not sure, however...

Your db connect is incorrect too, you aren't assigning any values to it as your quotes are not closed and are smart quotes.

Your connect,

$db = mysql_connect ("localhost", “db_username, “db_password);

Your new connect,

$db = mysql_connect("localhost", "db_username"," db_password");

Also,

$db_select = mysql_select_db(“db_name,$db);

To,

$db_select = mysql_select_db("db_name", $db);

Notice the difference in the quotes.

Edit 2

Your code is prone to SQL injection, you are still using MySQL even though it has been deprecated, you should use either MySQLi or PDO with prepared statements.

Not to mention your $_POST data is being passed on to the query without being sanitized, you should start using htmlspecialchars it would make it better and prevent XSS.

edited Aug 10, 2015 at 3:19

answered Aug 10, 2015 at 3:00

Script47

14.6k4 gold badges49 silver badges69 bronze badges

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

5 Comments

user557846 Over a year ago

even better dont use hyphens! dev.mysql.com/doc/refman/5.0/en/identifiers.html

user557846 Over a year ago

not closed and are smart quotes, not the proper ones

user557846 Over a year ago

smart meaning dumb :-0

Funk Forty Niner Over a year ago

("localhost", “db_username, “db_password) to ("localhost", "db_username","db_password") and (“db_name,$db) to ("db_name",$db) dang those curly quotes ;-) if that's what they're really using. Just in case.

Reg Ellis Over a year ago

Thank you all for your help with this especially Script47 for taking the time to explain it the way you have. I have never placed a hyphen between a table name before so have learned a lesson ove 8 hours trying to figure out what i was doing wrong. My original code had the correct quotes - not sure how the curly quotes ended up being pasted here. Dan Lowe, you hit it the nail on the head also, that was a main issue. Now I can work on the sanitising part - Once again Thank you and have a great day :-)

Dan Lowe · Accepted Answer · 2016-01-05 14:59:35Z

2

Your table name contains a dash, so you need to quote it. The backtick or back-quote character is used to quote symbol names in MySQL (such as the names of tables, columns, etc), so you would need something like this:

INSERT INTO `newsletter-subscribers` (first_name, ...

edited Jan 5, 2016 at 14:59

answered Aug 10, 2015 at 2:59

Dan Lowe

57.5k20 gold badges133 silver badges115 bronze badges

3 Comments

user557846 Over a year ago

backtick not quote

Dan Lowe Over a year ago

Yes, backticks are the way you quote a table name in mysql. The example shows that.

user557846 Over a year ago

ok its just confusing grammar, i'll settle for the manuals version: The identifier quote character is the backtick (“`”):

Collectives™ on Stack Overflow

mysql INSERT INTO query - ERROR with syntax

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related