Page 1 of 1
[SOLVED] Sorting results of query result
Posted: Fri Apr 22, 2005 7:09 am
by hessodreamy
For a search facility on my website I want to get a bunch of rows where any words match, then I want to go through the record set and assign relevance values according to which/how many fields match search terms. I've done this but am unsure as to how to sort the record set in order to display the results.
The query is like
SELECT fields... FROM tables... INNER JOIN more tables... WHERE field LIKE searchstring OR field2 LIKE searchstring etc...
then, going through the results:
Code: Select all
while($row != mysql_fetch_array($newTable))
{
if (stripos($searchstring, $subCatName)> -1) $ranking += 60;
else if (stripos($altstring, $subCatName) -1) $ranking += 60;
if (stripos($searchstring, $catName)> -1) $ranking += 60;
else if (stripos($altstring, $catName) -1) $ranking += 60;
if (stripos($searchstring, $prodName)> -1) $ranking += 20;
else if (stripos($altstring, $prodName) -1) $ranking += 20;
if (stripos($searchstring, $desc)> -1) $ranking += 10;
else if (stripos($altstring, $desc) -1) $ranking += 10;
$row['ranking'] = $ranking;
}
but what do I do now?
Many thanks
Posted: Fri Apr 22, 2005 7:31 am
by bobsta63
I dont understand what you want to do mate?
Are you trying to echo the resaults in order of the ranking?
Posted: Fri Apr 22, 2005 7:33 am
by hessodreamy
Yeah thats right. Sorry if it wasnt clear. My brain hurts...
Posted: Fri Apr 22, 2005 7:42 am
by bobsta63
Ok, Right have you connected to the database etc. Within your code?
eg.
Code: Select all
$connection = mysql_connect("localhost", "root", "") or die("Could Not Connect to Database: ".mysql_error();
Once you have done that you need to close the PHP tags.
Then add something like this:
Code: Select all
<?php echo $row_client['client_name']; ?>
^Obviously change it to match your code. This will print the resault.
Now to get it to order by rank you need to add the following code to your SQL.
For example:
Its hard to explain, but hope it helps mate

Posted: Fri Apr 22, 2005 8:06 am
by hessodreamy
I've not explained very well at all.
The ranking is not a field in the database. It needs to be worked out at runtime dependant on the search terms.
I've got a list of stuff from the table.
I want to display it in order of how many fields match the search term (in a nutshell).
Posted: Fri Apr 22, 2005 8:15 am
by bobsta63
hessodreamy wrote:I've not explained very well at all.
The ranking is not a field in the database. It needs to be worked out at runtime dependant on the search terms.
I've got a list of stuff from the table.
I want to display it in order of how many fields match the search term (in a nutshell).
oooo, Im afraid I cant help you their mate

, Sorry not sure how to do that.
Posted: Fri Apr 22, 2005 10:13 am
by malcolmboston
the way i would do it would be to:
do the query and return an
array of results
in the array loop, get the ranking and assign that to the array
eg.
Code: Select all
$array[1]['name'];
$array[1]['rank'];
then sort the array by rank then run a loop to print it
Posted: Fri Apr 22, 2005 10:34 am
by hessodreamy
So the result set that's returned from the DB - that's not treated as an array is it? WOuld I go through the resultset and put each row into the array?
At the moment I'm putting it into a new table and the script is timing out. Might the array be more efficient?
Posted: Fri Apr 22, 2005 11:04 am
by phpScott
Not sure if
http://www.php.net/manual/en/function.a ... tisort.php will be of any help but it might when you get to sorting your array.
I think what feyd is saying is that get your results from the db, as you are assigning them to an array do the calculation that you want to do for relevance and assign that within a multideminsional array.
then sort your new fancy array by relevance.
Posted: Wed Apr 27, 2005 3:25 am
by hessodreamy
Yay! That worked a treat. I think i was trying to be too clever. Thought perhaps that the DBMS might have done some of the work faster, but now i know better.