What is wrong with this?

[Able Net Design]

i get this error all the time "Invalid query: 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 '== ('') AND paid = '1'' at line 1"

here is the code:

$result3 = mysql_query("SELECT * FROM mail WHERE domain = ('$dom') AND read = '0'") or die("Invalid query: " . mysql_error());
$newmail = mysql_num_rows($result3);
echo "[<font color=#99c6e2>$newmail</font>]";

[bcolflesh]

AND read = 0")

[Able Net Design]

Same error message.

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 'read = 0' at line 1

[bcolflesh]

Ooops - try:

("SELECT * FROM mail WHERE domain LIKE '".($dom)."' AND read = 0")

[Able Net Design]

same error:

Invalid query: 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 'read = 0' at line 1

i have had a look at some other code in this app which is similar however that isn't working.

for the two demo records in their it should return two overdue invoices but it returns none.
function overdue()
{
//new select statment to connect to invoice table and search
$result1 = mysql_query("SELECT * FROM invoice WHERE domain = ('$dom') AND curdate() >= duedate AND paid = 0") or die("Invalid query: " . mysql_error());
$myrow = mysql_num_rows($result1);
echo "[<font color=red>$myrow</font>]";
}

[jatar_k]

$result1 = mysql_query("SELECT * FROM invoice WHERE domain='$dom' AND curdate() >= duedate AND paid=0")

why parentheses around $dom and I assume paid is an int col

[Able Net Design]

ok i got it to work as a general statment for all invoices over due however i cant get it when i check just for one user.

maybe their is something wrong with the amount of AND or something.

SELECT * FROM invoice WHERE domain = '$dom' AND curdate() >= duedate AND paid = 0

[slade7]

$result1 = mysql_query("SELECT * FROM invoice WHERE domain = '$dom' AND curdate >= 'duedate' AND paid = '0'");

This is the syntax I use... I don't use () inside unless I'm grouping like... SELECT SUM(somecolumn) AS... WHERE... etc.

or

SELECT * FROM table WHERE (col1 = 'foo' OR col2 = 'bar') AND (col3 = 'whateva' OR col4 = 'britneyspears')

I use single quotes even where there's integers or doubles. Always works as expected

But I don't know all the propers... just want it to work.

[Able Net Design]

slade7 it can't be that cause you get an "Invalid query: Unknown column 'curdate' in 'where clause'" cause curdate requires the ()

[slade7]

well if curdate is not a column, but the actual current date that you are grabbing for comparison, and if 'duedate' is a column and not a comparison string, then I would handle it like this:

First grab curdate as a timestamp from the server w/php and set a variable to represent it at the top of your script. Then change that part of your SQL to read:
...AND duedate <= '$curdate' AND...

Another roundabout hack if you don't have time to read up on comparing Unix timestamps to mysql timestamp(14) - or trying to find out if they are the same... or whatever

Set up a two column table 'getdate' in mysql - make the first column an integer and set the second as a mysql timestamp() w/ the function NOW & same setup as the 'duedate' column in your 'invoice' table. & at the top of your script do this

$kill = mysql_query("DELETE FROM getdate");

$resurrect = mysql_query("INSERT INTO getdate(integercol)
VALUES('1')");

$grabdate = mysql_query("SELECT timestampcol FROM getdate");

$curdate = mysql_result($grabdate, 0);

Then you have the current date & time as $curdate. Every time you run the script with this at the top it will give you a new value for $curdate if that column is timestamp(14)

Disclaimer: I didn't test this, so there's bound to be a bug. Plus there are probably ten easier ways dealing with converting the unix timestamp to a date format that mysql can compare to your duedate col.

In any case, it probably easier to get the $curdate set first and then compare to your column 'duedate' in the WHERE clause instead of vice-versa