Mysql query error

[impact]

Hello,

In my site, I have a small database which stores emails. I am trying to retrieve data such as Inbox email count, Spam email count, Trash email count etc.

I have no idea why I am getting this MYSQL 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 = '[e-mail]' AND type = 'inbox'' at line 1


$mysql = "SELECT * FROM email WHERE to = '$email' AND type = 'inbox'";
$result = mysql_query($mysql) or die(mysql_error());
$totalNumberOfEmail = mysql_num_rows($result);


Is the error due to the fact that my database is empty? If it is so, then how do i by pass this error. Do I have to enter dummy email in my database to get read of this error?

Any help will be appreciated.

Thank you

[Tommybs]

Hi,

I'm wondering if this could be due to a reserved keyword. Did you copy and paste the above code or did you copy and paste the error? The error looks like it's missing a single quote at the end of inbox.

Perhaps try this:

$mysql = "SELECT * FROM `email` WHERE `to` = '$email' AND `type` = 'inbox'"; 

[Matthew1980]

Hi all,

Is the error due to the fact that my database is empty?

I don't think that would do that ;) If the query returns no results, or the DB is empty, it will return query as zero (0) rows, but the query would execute.

Tommybs's advice seems to be the correct direction to take, backticks are used to make 'sensitive' field name data ie, reserved words etc function within use of mysql, as well as spaces in the field name. Though sql statements can be used without backticks, but my preference is to always use back ticks, this will save headaches later :)

Personally though, I would concatenate the var into the string, this makes it easier to read IMO:-

$mysql = "SELECT * FROM `email` WHERE `to` = '".$email."' AND `type` = 'inbox'";

Though either version would work, just down to preference...

Cheers,
MRb

[dreamcatcher]

"to" is indeed a mysql reserved word. Its good practice not to use reserved words for field names. A full list can be found on the mysql website:

[dev.mysql.com...]

dc

[impact]

" "to" is indeed a mysql reserved word. Its good practice not to use reserved words for field names. "


Thank you, that solved all the problem!

Thank you again.