Search queries..

[bcoffin]

I'm putting together a movie listing site, where users can search the database via a number of different search options. They are:

 

[ 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??

 

Thanks,

 

Benny

[fenway]

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.

[bcoffin]

Hi Fenway,

 

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']"

 

if(isset($_POST['terms']))

{

$term_in = explode(" ",$_POST['terms']);

foreach($term_in as $k=>$v)

{

$terms .= "OR $terms LIKE '%$v%'";

}

$terms = ltrim($terms,"OR");

}

else

{

$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?

 

Benny

 

 

[!--quoteo(post=333723:date=Jan 5 2006, 06:14 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 5 2006, 06:14 PM) 333723[/snapback][/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.

 

[fenway]

You need to isolate each part of the query, and AND them together, since you want all conditions simultaneously satisfied:

 

"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.

[LazyJones]

Basic idea is fine, but that query sure does mess up. Have you echoed it? to see what it looks like?

 

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.

[bcoffin]

AWESOME YOU GUYS!!

 

Your advice is priceless.. thank you! I promise I will take each and every one of your suggestions!!!

 

Benny

 

ps. I have no idea how to mark this SOLVED ... I can't find the solved button anywhere.

[bcoffin]

So ultimately, my query will look like this, right:

 

 

"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?

 

Benny

[LazyJones]

Maybe, but make this correction:

 

$terms .= "OR $terms

 

->

 

$terms .= "OR terms

[bcoffin]

cool! will do.

 

thanks again, all...

 

benny

[bcoffin]

WORKED LIKE A CHARM!!

 

Again, you guys are irreplaceable!

 

Thanks again,

 

Benny