PHP/MySQL - What's wrong with this INSERT statement?

[Pherank]

<?php

include 'connect.php';

$link = mysql_connect("$host","$user","$pass") or die ("Unable to connect to database.");
mysql_select_db("$db", $link) or die (mysql_error());

$sql = "INSERT INTO customer ('first_name', 'last_name', 'telephone', 'email', 'street', 'city', 'state', 'country', 'postal_code', 'pay_method', 'artpieces') VALUES ('elmer', 'fudd', 123-435-1235, 'elmer@yahoo.com', '123 Hawaii Ave.', 'Barcelona', NULL, 'Spain', '9H3-4R5', 'money order', '11042008')";

$query = mysql_query($sql, $link);

if(!mysql_query($sql))
{
//error_log("Can't insert user: ".mysql_error());
die("Could not insert user because: ".mysql_error());
} else {
echo "Data Inserted!";
}
mysql_close($link);

// Error: Could not insert user because: 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 ''first_name', 'last_name', 'telephone', 'email', 'street', 'city', 'state', 'cou' at line 1
?>


Here is an image of the database table structure (I changed everything to NULL allowed while troubleshooting):

http://www.tornedgedesign.com/_test/kirupa/dbase.gif

[Pherank]

I'm amending the SQL to:


$sql = "INSERT INTO customer (first_name, last_name, telephone, email, street, city, state, country, postal_code, pay_method, artpieces) VALUES ('$first_name', '$last_name', '$telephone', '$email', '$street', '$city', '$state', '$country', '$postal_code', '$pay_method', '$artpieces')";

but there's still a problem with the variables...

[jsauni]

Works fine to me. First $sql works if you remove the '' around the column headings. Second $sql works fine when I create and assign values to the variables.

[Charleh]

Your telephone code is a problem

All text needs to be qualified by single or double quotes - the telephone number 123-435-1235 is

a: Not a number (numbers cannot include dashes)
b: Not text (text must be qualified)

Therefore SQL is probably treating this as a formula (and I don't think you can include a formula in the values list, though I may be wrong)

So either the SQL throws an error about that or it does 123 minus 435 minus 1235 and converts it to text and the next problem is the cause...

Quotes don't need to go round column names. Quotes should only need to go round column names if they include spaces or special characters (which really should be avoided).

This query



INSERT INTO customer (first_name, last_name, telephone, email, street, city, state, country, postal_code, pay_method, artpieces) VALUES ('elmer', 'fudd', '123-435-1235', 'elmer@yahoo.com', '123 Hawaii Ave.', 'Barcelona', NULL, 'Spain', '9H3-4R5', 'money order', '11042008')


Will work. Try running it against the database using phpMyAdmin

[jsauni]

b: Not text (text must be qualified)

What do you mean by this? I thought if you declared the telephone number as



$telephone = '123-456-789';


it's fine. And I've tested it as so and has worked fine, so not sure why this would be a problem, if you could just explain a little as I'm only just learning php/mysql myself.

[simplistik]

the - in the number make the entry a string not an integer. so when you have in your query as

123-456-7890 without quotes around it, it assumes that the number is just an integer when in fact it's a string so it needs the quotes.

[jsauni]

Oh I missed that in the first query