Page 1 of 1
Assign priority to a mysql query
Posted: Sat Jul 28, 2012 4:40 pm
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
Re: Assign priority to a mysql query
Posted: Sat Jul 28, 2012 5:43 pm
by requinix
One query for an exact match, one query for a LIKE match (that also isn't an exact match).
Re: Assign priority to a mysql query
Posted: Sat Jul 28, 2012 5:59 pm
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.
Re: Assign priority to a mysql query
Posted: Sat Jul 28, 2012 6:42 pm
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.
Re: Assign priority to a mysql query
Posted: Sat Jul 28, 2012 7:24 pm
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