frustrating error

curried

Hi guys/gals,

i've been struggling with this code for sometime now.

Here is the code:

echo 'now adding to the array<br />';

echo 'vars : ' . $to . ' | ' . $type . ' | ' . $currentdate . '<br />';

mysql_select_db($database_database, $database);

$query = "INSERT INTO mails (to, subject, headers, message, type, date_qued, priority) VALUES ('$to', '$subject', '$headers', '$message', '$type', '$currentdate', '$priority')";

$result = mysql_query($query)
or die("Error: ".mysql_error());
echo 'adding info to databse<br />';

echo ' done adding to database moving to next record<br />';

i've added some comments into my coding to see what the problem is.

The output of my coding is:-

now adding to the array
vars : ****@**.co.za | no_category | 06-04-2010 22:03:29
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 'to, subject, headers, message, type, date_qued, priority) VALUES ('**********@s' at line 1

my database variables are defined as follows:-

<?php

FileName="Connection_php_mysql.htm"

Type="MYSQL"

HTTP="true"

$hostname_database = "localhost";
$database_database = "info";
$username_database = "username";
$password_database = "password";
$database = mysql_pconnect($hostname_database, $username_database, $password_database) or trigger_error(mysql_error(),E_USER_ERROR);
?>

Please guys, if you can spot any errors in my coding, it would really be appreciated.

Regards,
curried.

halojoy

you can not use 'to' or 'TO' as fieldname
it is a reserved word in mysql, see list here:
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

I would use fieldname: 'mailto' ... or .... 'sendto'

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 'to,

dagon

you can use to (and the other reserved words), but if you do you have to quote it with back-ticks `````

but as halojoy said, its better to just not use them in the first place.

Res

It's best practice to include the back ticks regardless of weather or not your field name is a reserved word or not.

`database`.`table`.`field`

Not always necessary, but will save your from beating your head against the wall from your own mistakes, or when mysql updates and adds new reserved words. This way you won't have to go back through the old code to make it play nice with a version of mysql.

List of reserved words:
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

dagon

Res;10948711 wrote:
It's best practice to include the back ticks regardless of weather or not your field name is a reserved word or not.

I disagree as the they not ANSI-SQL compliant you may have an issue with other db's if you ever ported your code.