hi is this query is right?

[mak1084]

[CODE=php]$qselect = 'SELECT ticketID,ticket .created,subjec t,dept_name,nam e,email,source, ticket.ticket_i d,ticket.dept_i d,ticket.staff_ id '.
',status,isover due,pri.* ';
$qfrom=' FROM '.TICKET_TABLE. ' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id= dept.dept_id '.
' LEFT JOIN '.TICKET_PRIORI TY_TABLE.' pri ON ticket.priority _id=pri.priorit y_id '.
' LEFT JOIN '.TICKET_LOCK_T ABLE.' tlock ON ticket.ticket_i d=tlock.ticket_ id AND tlock.expire>NO W() ';
$qwhere='WHERE ticket.created BETWEEN \''.$startDate. '\'AND\''.$endD ate.'\'' ;
$qgroup=' GROUP BY ticket.ticket_i d';

$query="$qselec t $qfrom $qwhere $qgroup ";[/CODE]


I'm getting this error

[SELECT ticketID,ticket.created,subject,dept_name,name,email,source,ticket.ticket_id,ticket.dept_id,ticket.staff_id ,status,isoverdue,pri.*   FROM vt_ticket ticket LEFT JOIN vt_department dept ON ticket.dept_id=dept.dept_id  LEFT JOIN vt_ticket_priority pri ON ticket.priority_id=pri.priority_id  LEFT JOIN vt_ticket_lock tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW()  WHERE ticket.created BETWEEN AND  GROUP BY ticket.ticket_id  ] - 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  GROUP BY ticket.ticket_id' at line 1

[Atli]

Try adding spaces around the AND in your WHERE clause.

Also, instead of using single-quoted strings and escaping all the quotes, try:
[code=php]
$where= "WHERE col BETWEEN '$start' AND '$end'";
[/code]
Double-quoted strings will replace variables with their respective values and they can use single-quotes without needing to escape them.

Edit; On second thought, you could also try:
[code=php]
$sql = <<<ENDSTR
SELECT * FROM myTable
WHERE NOW() BETWEEN '$start' AND '$end'
ENDSTR;
[/code]

[coolsti]

It may be that the error you are getting is because some variables such as $startDate and $endDate apparently are blank. In other words, they are just empty strings, and so it looks like your query is trying to do this: "BETWEEN AND "and this is of course a syntax error.

I am not certain though, because the single quotes are not appearing as I would expect they should even if these variables are blank.

Try to see why these variables are not being set. Perhaps you are not paying attention to case sensitivity? Or have misspelled the variables? Or maybe just didn't set them correctly?

[mak1084]

It may be that the error you are getting is because some variables such as $startDate and $endDate apparently are blank. In other words, they are just empty strings, and so it looks like your query is trying to do this: "BETWEEN AND "and this is of course a syntax error.

yes coolsti the variables are empty and i want to search in both case when the they have put the values and when they have not. is it possible for single query to achieve this?

[coolsti]

Yes you can do this with one query, but in all cases, the syntax for mysql must be correct, and not result in a syntax error.

I am now looking at this part:

$qwhere='WHERE ticket.created BETWEEN \''.$startDate.'\'AND\''.$endDate.'\'' ;

As pointed out by another, this is very confusing, because of your choice of ' to denote a string. PHP lets you use ' or ", so I will rewrite this:

$qwhere= "WHERE ticket.created BETWEEN " . $startDate . " AND " . $endDate . " ";

And because PHP will evaluate PHP variables to their values within "" string, this could also be rewritten like this:

$qwhere= "WHERE ticket.created BETWEEN $startDate AND $endDate ";

I don't know if this is what you meant to have here, or if you meant to have something like this:

$qwhere= "WHERE ticket.created BETWEEN '$startDate' AND '$endDate' ";

The only difference here is that I am adding single quotes around the values for the dates. I believe this is what you wanted to do, as for example if startDate had the value as character string of 2008-5-7, the single quotes here would be needed for proper syntax.

But if you look at your error statement, even though $startDate and $endDate are empty, the quotes do not appear, and so mysql is seeing something like this:

WHERE ticket.created BETWEEN AND GROUP BY

instead of this:

WHERE ticket.created BETWEEN '' AND '' GROUP BY

Therefore you get your syntax error.

I do not have a mysql console in front of me at the moment, but I believe the example with just an empty string enclosed with single quotes will be syntactically ok for mysql. But the example with no quotes is definitely an error.

However, it would be better if you change the query to be a more proper one for the case that either of the variables $startDate and $endDate are empty. You can check this in PHP and then alter the query accordingly.