Posted 05 January 2006 - 10:31 PM
[ DROPDOWN: Director Name ]
[ FREE TEXT: search terms ]
[ ] Check box: Genre 1
[ ] Check box: Genre 2...
I'd like for MySQL searching as follows, but I really need your advice on how to do this:
If a director name is selected from the drop down ALL results must be by that director.
If free text search terms are specified ALL results must have at least one of the search terms.
If checkboxes are selected ALL results must be in at least one of those selected genre categories.
Right now, the genre is a 1/0 tinyint in the movie record.
Can somebody please give me some advice about how to set up a mysql query to do this??
Posted 05 January 2006 - 11:14 PM
Posted 05 January 2006 - 11:26 PM
That's where I get confused. I wanted to just put it all in one query string, but I don't know how to distinguish between ANDs and ORs for something like that?
Here's an example I'm currently using:
if(isset($_POST['director'])) $director = "DIRECTOR = '$_POST['director']"
$term_in = explode(" ",$_POST['terms']);
foreach($term_in as $k=>$v)
$terms .= "OR $terms LIKE '%$v%'";
$terms = ltrim($terms,"OR");
$terms = "";
foreach($_POST['genre'] as $genre_key=>$genre_category)
if(isset($genre_category)) $genre = "OR $genre_category='1'";
$genre = ltrim($genre,"OR");
"SELECT * FROM listings WHERE $director $terms $genre ORDER BY title ASC";
But obviously the ANDs and ORs get screwy...
More help, please?
[!--quoteo(post=333723:date=Jan 5 2006, 06:14 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 5 2006, 06:14 PM) [/div][div class=\'quotemain\'][!--quotec--]
Well, you need to first determine which conditions have been inputted (in PHP), and make a (possibly) combined query that, in principle, can include all three. Usually, this requires JOINing an extra table for each addition condition, and updating the WHERE clause accrodingly. I built the query string in middleware, and then run the query. Why don't you post what you have for each _individual_ query, and then try and combine them.
Posted 05 January 2006 - 11:39 PM
"SELECT * FROM listings WHERE ($director) AND ($terms) AND ($genre) ORDER BY title ASC";
Of course, you only need to include the part if it was requested, otherwise "AND ()" will throw an error. The way I get around this is simply to simply start of each query part with "1=1", which will always be true, and then add on additional restrictions.
So I would do the following instead, as an example:
$director = "1=1"; if(isset($_POST['director'])) $director = " AND DIRECTOR = '$_POST['director']"
Do you see what I mean? You can do this for your other conditions as well -- but be careful will the ORs (you'll have to group all the ORs first, then AND them with 1=1). That way, you always end up with a properly formatted SQL statement regardless of the input. BTW, you might consider having a full-text index on the terms column, and you've forgotten the concatenation operator for the genre condition (you have = instead of .=)!
Hope that makes sense.
Posted 05 January 2006 - 11:48 PM
I think it would be somethink like this
"SELECT * FROM listings WHEREDIRECTOR = 1OR 2 LIKE '%3%'OR 3 LIKE '4%' ..."
You need the spaces between, (and still you are trimming )
Extra spaces wont do you no harm, just put them after every string.
Posted 05 January 2006 - 11:53 PM
Your advice is priceless.. thank you! I promise I will take each and every one of your suggestions!!!
ps. I have no idea how to mark this SOLVED ... I can't find the solved button anywhere.
Posted 06 January 2006 - 12:18 AM
"SELECT * FROM listings WHERE (director='director name') AND (terms like '%term1%' OR terms like '%term2%') AND (genre = 'genre1' OR genre='genre2' OR genre='genre3') ORDER BY title ASC";
Hopefully I've finally gotten this right?
Posted 06 January 2006 - 12:25 AM
$terms .= "OR $terms
$terms .= "OR terms
Posted 06 January 2006 - 12:40 AM
thanks again, all...
Posted 06 January 2006 - 01:52 AM
Again, you guys are irreplaceable!
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users