Jump to content


Photo

Search queries..


  • Please log in to reply
9 replies to this topic

#1 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 05 January 2006 - 10:31 PM

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
Ben Coffin
bcoffin@hotmail.com

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 January 2006 - 11:14 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 05 January 2006 - 11:26 PM

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) View Post[/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.
[/quote]

Ben Coffin
bcoffin@hotmail.com

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 January 2006 - 11:39 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 05 January 2006 - 11:48 PM

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.

#6 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 05 January 2006 - 11:53 PM

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.
Ben Coffin
bcoffin@hotmail.com

#7 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 06 January 2006 - 12:18 AM

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
Ben Coffin
bcoffin@hotmail.com

#8 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 06 January 2006 - 12:25 AM

Maybe, but make this correction:

$terms .= "OR $terms

->

$terms .= "OR terms

#9 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 06 January 2006 - 12:40 AM

cool! will do.

thanks again, all...

benny
Ben Coffin
bcoffin@hotmail.com

#10 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 06 January 2006 - 01:52 AM

WORKED LIKE A CHARM!!

Again, you guys are irreplaceable!

Thanks again,

Benny
Ben Coffin
bcoffin@hotmail.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users