[SOLVED] Prioritized search

[mapleleaf]

Is there a MySQL function that can prioritize?

For example I want to search for books. The books table contains the city, street,zip and country of the book.

If i want the results to show the zip matches, then the city matches, then the state matches and lastly the country matches in that order can it be done with one query?

Like zip is 1, city is 2 etc in priority.

<?php
//so something like:
$query = "SELECT * FROM books WHERE title LIKE '%$posted%' AND zip[1] = '$zip' AND city[2] = '$city' AND state[3] = '$state' AND country[4] = '$country' ";
?>

This is of course not valid but just to convey the concept.

Just had the thought that maybe ORDER by can be used for columns.

ORDER BY zip,city,state,country.

 

Help much appreciated

[fenway]

Yes, you can do this:

 

$query = "SELECT * FROM books WHERE title LIKE '%$posted%' AND zip = '$zip' AND city = '$city' AND state = '$state' AND country = '$country' ORDER BY (  zip = '$zip' DESC, city = '$city' DESC, state = '$state' DESC, country = '$country' DESC )";

[mapleleaf]

Fenway

Thanks so much as I'm sure this is close but I am getting this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC, city = 'jamestown' DESC, state = 'CO' DESC, country = 'USA' DESC )' at line 1

 

Maybe it needs to be more complicated as in having a sub query for each of the ORDER BYs

What do you think?

[fenway]

Sorry, my bad about the parens.... try:

 

$query = "SELECT * FROM books WHERE title LIKE '%$posted%' AND zip = '$zip' AND city = '$city' AND state = '$state' AND country = '$country' ORDER BY ( zip = '$zip' ) DESC, ( city = '$city' ) DESC, ( state = '$state' ) DESC, ( country = '$country' ) DESC";

[mapleleaf]

Actually it works with all the parenths out

But thanks anyway

 

$clause = "WHERE $type LIKE '%$posted%' AND (zip = '$zip' OR city = '$city' OR state = '$state' OR country = '$country') ORDER BY zip = '$zip' DESC, city = '$city' DESC, state = '$state' DESC, country = '$country' DESC";

 

Is giving me what I wanted

[corbin]

Whoa, I never knew that was possible.

 

 

 

*Just had his mind blown.*