Assign priority to a mysql query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

Assign priority to a mysql query

Post by lovelf »

I have

One Good Result
Nice Result
Result

when retrieving the values for an autocomplete when I write "result" I would like to retrieve "Result" first from the database, then any other that contains result.

Code: Select all

$termgotten="result";
$result=mysql_query("SELECT * FROM table WHERE LOWER(row) LIKE '%".mysql_real_escape_string($termgotten)."%' LIMIT 6");
This can give me:
One Good Result
Nice Result
Result

as the results, so what would be the specification to get "result" let's say in an order of appearance - a mysql explote to order by?

Thanks
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Assign priority to a mysql query

Post by requinix »

One query for an exact match, one query for a LIKE match (that also isn't an exact match).
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

Re: Assign priority to a mysql query

Post by lovelf »

Good idea, how about a priority for results where lower(row) like match contains the lesser amount of spaces
to ensure less queries and allow for results like: this result - this longer result - to have first this result then this longer result second.

Thanks for your reply.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Assign priority to a mysql query

Post by requinix »

If the only thing you care about is using one less query and not, say, running time or efficiency, then sure you can sort by length or number of spaces or whatever else you want.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Assign priority to a mysql query

Post by twinedev »

If I am understanding what you want, I would also have a field that contain the size of `row` as mediumint and then do the following (Note, unless you specified other than the defaults when setting up the database, you do not need the LOWER() as the default is case insensitive), also normally I would specify what fields you need instead of just going for * to save on resources.

Code: Select all

$result=mysql_query('SELECT * FROM `table` WHERE `row` LIKE "%'.mysql_real_escape_string($termgotten).'%" ORDER BY `rowlen` LIMIT 6');
You could also just get the lengths on the fly, but for the use (as an autocomplete, so this will be called a lot), why have it do more work each query. Also would be good to have proper indexes set up for faster lookup (sorry, I'm not the best at them to give it to you off of the top of my head).

-Greg
Post Reply