MySQL query with multiple AND OR statements not working.

rosegarden

I am trying to do a search in database that only returns records that have a future date listed in "Job_Close_Date".

My current date and Job_Close_Date are listed as "seconds since the Epoch" time.

ie:

$date = date ("U");

Here is my code for the query:

$sql = "SELECT Listing_ID, Dept_Name, Dept_State,
                     Job_Position, Job_Close_Date, Date_Added 
                     FROM Job_Listings 
                     WHERE Job_Close_Date >= '$date' 
                     AND Dept_Name like '%$search%' OR Dept_City 
                     like '%$search%' OR Dept_State like '%$search%' 
                     OR Job_Position like '%$search%' OR 
                     Job_Description like '%$search%' OR 
                     Job_Requirement like '%$search%' OR 
                     Default_Information like '%$search%'  

                     ORDER BY Date_Added 
                      DESC LIMIT $lowval,$highval";

azkworld

first, try putting a few parens () around your logic -- AND takes precedence over OR, and (I guess) that is not what you want.
second, since you're passing date as a string, try ... >= '2001-12-31'

rosegarden

I am not sure where you want me to use parens ().

I started using the date formated as '2001-12-31' by then my equations did not always work. I would list '2002-3-28' as greater than '2002-11-5'.

herve

Hi,

condition_A AND condition_B OR condition_C

is ambiguous, it can mean either :

(condition_A AND condition_😎 OR condition_C

or :

condition_A AND (condition_B OR condition_C)

that's why you need to 'enforce' things and explain
very clearly for the computer what you really want.

(
Job_Close_Date >= '$date'
)
AND
(
Dept_Name like '%$search%' OR
Dept_City like '%$search%' OR
Dept_State like '%$search%' OR
Job_Position like '%$search%' OR
Job_Description like '%$search%' OR
Job_Requirement like '%$search%' OR
Default_Information like '%$search%'
)

for instance.

Hervé.

rosegarden

Thank you... That was it....🆒