Jump to content

Search queries..


bcoffin

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.