search conditions
Posted: Tue Mar 30, 2010 4:11 pm
I'm trying to make a search query that basically takes 2 variables from a form and returns true if:
A is true
B is true
or (A and B) are true.
I say basically 2 variables because in actuality, there are 4, but I concat the city/state and month/year together to make two variables.
The purpose of the form is to allow users to search the database for information based on location and month.
If the user searches by just location...the results are true and works.
If the user searches by just month...the results are true and works.
The problem arises when the user tries to search by location AND month. The results display all the cities and months that the user searched for, when in actuality, I just want the results to be specific to the location and month the user had searched for. Hope that makes sense.
Here's my php script:
Thank you in advance for any advice!
A is true
B is true
or (A and B) are true.
I say basically 2 variables because in actuality, there are 4, but I concat the city/state and month/year together to make two variables.
The purpose of the form is to allow users to search the database for information based on location and month.
If the user searches by just location...the results are true and works.
If the user searches by just month...the results are true and works.
The problem arises when the user tries to search by location AND month. The results display all the cities and months that the user searched for, when in actuality, I just want the results to be specific to the location and month the user had searched for. Hope that makes sense.
Here's my php script:
Code: Select all
//get search criteria
$performance_search_city = $_POST['performance_search_city'];
$performance_search_state = $_POST['performance_search_state'];
$performance_search_month = $_POST['performance_search_month'];
$performance_search_year = $_POST['performance_search_year'];
"SELECT venue_name, venue_address1, venue_address2, venue_city, venue_state, venue_zipcode, venue_phone, show_description, show_date, show_notes, showID
FROM shows INNER JOIN venues
ON shows.venueID = venues.venueID
WHERE ((venue_city = '".$performance_search_city."' AND venue_state = '".$performance_search_state."')
OR DATE_FORMAT('show_date', '%Y%m') = DATE_FORMAT(CURDATE(), '%".$performance_search_year."%".$performance_search_month."')
OR (venue_city = '".$performance_search_city."'
AND venue_state = '".$performance_search_state."'
AND DATE_FORMAT('show_date', '%Y%m') = DATE_FORMAT(CURDATE(), '%".$performance_search_year."%".$performance_search_month."'))
AND show_date >= CURDATE() <--added to list the results from current date onward for both conditions
ORDER BY show_date";