PHP/MySQL question -- MySQL Syntax Error?

Question

if(isset($_POST['sendmessage'])){

$from = $_SESSION['user']['userid'];
$to = $_POST['client'];
$datetime = date("Y m d H:i:s");
$message = $_POST['messages'];
$read = 0;

$query = "INSERT INTO messages (messageid, from, to, datetime, message, read) VALUES (NULL,'$from','$to','$datetime','$message','$read')";
$result = mysql_query($query) or die(mysql_error());

}

Having a really stupid problem with this code.

When checking the echoes of it everything is posting. When I go to post into the SQL I get an error of this:

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 'from, to, datetime, message, read) VALUES (NULL,'1','1','2011 05 13 14:46:37','T' at line 1

My table setup is as follows. Note that to and from and both userids:
messageid int 11 PRIMARY KEY
from int 11
to int 11
datetime datetime
message varchar 1000
read varchar 1

Please offer any and all advice. I'm sure I'm missing something stupid. I've rewrote the code like five times though.

Answer 1

Actualy, you have several errors...

The first one is that you use keywords as field names in your table!

You cannot have a field name called "datetime", "date", "time", "from", "to", "read" etc....

When I say "cannot", I say "it is not advisable", because you will have to take a lot of precautions...

The field names that are also keywords MUST always be enclosed in back quotes ( ` ):

To insert the values, you cannot use single quotes with php variables: php does NOT parse values in single quotes, so you must use single-double quotes: "values ( ' " . $value . " ' , ' " . $othervalue . " '...

(spaces added for clarity)

The tablenames, also, need to be enclosed in single reverse quotes...

Use lower cases all the way (conformity with all versions of mysql).



SO: here is your insert, reviewed:

$query = "insert into `messages` (`messageid`, `from`, `to`, `datetime`, `message`, `read`) values (NULL, '" . $from . "', '" . $to . "', '" . $datetime . "', '" . $message . "', "T")";

$result = mysql_query($query) or die(mysql_error());



My advices:

1. Change your table field names!

from => originator

to => recipient

datetime => datum

read => viewed

(or anything similar)

You will also see often "desc" for description: change to "descr" (desc is a keyword for "descending")

2. Change "message" from varchar 1000 to "text" (varchar is limited to 256 if my memory serves me well)

3. Change your messageid type to "int 11" AUTOINCREMENT and index. That way you can enter null, and you won't have duplicate numbers

Answer 2

It sounds like the linked fee of $nov has an invalid character in it. examine what it contains. examine the different values besides. What I did is create a functionality referred to as MakeMySQLSafe that escapes the strings going into MySQL. it may be a super thought besides to sidestep MySQL injection assaults, which could be VERY severe.

Answer 3

Why the message id is null and it is primary key.



primary key should not be null, it is used for indexing





try running the query individually first, in mysql command prompt