[SOLVED] Sorting results of query result

This forum is for discussing the future of the PHP Developer's Network, as well as for us to get your opinion on things.

This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

[SOLVED] Sorting results of query result

Post 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
bobsta63
Forum Commoner
Posts: 28
Joined: Thu Apr 21, 2005 7:03 pm
Location: Ipswich, UK

Post by bobsta63 »

I dont understand what you want to do mate?

Are you trying to echo the resaults in order of the ranking?
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Post by hessodreamy »

Yeah thats right. Sorry if it wasnt clear. My brain hurts...
bobsta63
Forum Commoner
Posts: 28
Joined: Thu Apr 21, 2005 7:03 pm
Location: Ipswich, UK

Post 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:

Code: Select all

SELECT * FROM table ORDER BY rank
Its hard to explain, but hope it helps mate :)
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Post 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).
bobsta63
Forum Commoner
Posts: 28
Joined: Thu Apr 21, 2005 7:03 pm
Location: Ipswich, UK

Post 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.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Post 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?
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Post 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.
Post Reply