Sorting results using PHP/MySQL based on user input

Question

Apologies in advance for the "programming for dummies" question... I searched in vain for a previous discussion about this.

So I've got a MySQL table full of powerboats. Each row has basic powerboat info (make/model/etc.) plus scores from 1-10 for a few different traits -- "power", "comfort", and "style". I want to help users find the powerboat that meets their individual needs.

By default, the data is ordered by "totalscore" from high to low, like so:

$sql = "SELECT *,(power + comfort + style) as totalscore FROM boats ORDER BY totalscore DESC";

... and I loop through the results with PHP and display them as a list.

But then I give the user a series of Yes/No questions to determine individual preferences, and I want each answer to change the value of totalscore in the user's session -- and thus change the order of the list items.

In other words, I want $_SESSION["totalscore"] to change with each Yes/No user input...and the user's results should then be ordered from high to low by $_SESSION["totalscore"], not the original totalscore from the database.

Schematic Example

1. Boston Whaler 370. power=7, comfort=8, style=6, totalscore=21
2. Boston Whaler 350. power=5, comfort=7, style=8, totalscore=20

Question 1: Do you love style? Yes/No

if Yes {
    $_SESSION["totalscore"] = totalscore + style;
    reorder list items by $_SESSION["totalscore"]; // the 350 is now #1
  }
  if No {
     $_SESSION["totalscore"] = totalscore + (style/2);
     reorder list items by $_SESSION["totalscore"]; // they're now tied for #1
  }

Etc.

What's tripping me up conceptually is that $_SESSION["totalscore"] would be stored as a cookie, whereas the original totalscore comes from the database query. So once I switch from totalscore to $_SESSION["totalscore"], I'm at a loss as to how to reorder the list items.

Can someone point me in the right direction, if only in a schematic way? I want to figure it out, but I'm not sure WHAT to figure out at this point. Thanks very much.

Answer 1

You can store the totalscore from the session into the results that you get back from the database and use php's sort function to reorder the results.

Answer 2

One way can be, instead of selecting the total score in the query and then sorting the result once, make a query every time the user changes preferences

Example

SELECT ( power + rating + style) as total_score from boats order by total_score;

Then loop through the results and display the first set of results. Give the user options to make his choices and then run the query again as follows -

if(style_matters)
{
   $q = mysql_query("SELECT (power + rating + 2*style) as total_score from boats order by total_score");
   //use this new result set in $q to present the new listing
}
else
{
   $q = mysql_query("SELECT (power + rating + style/2) as total_score from boats order by total_score");
   //use this for result set when style doesnt matter.
}

It can be an expensive operation if you have a very huge number of DB hits, but I dont think it would be much of an issue.

You can run this query using AJAX to avoid page reloads.

EDIT: You wont even notice multiple DB queries taking place till you start hitting 6-7 digit figures. Although you should never use "SELECT * from " in your queries. Always specify the exact columns you want to use. Also in you particular case you can limit the number of results you present to the user. Present 10 results on one page, next 10 on another. Limiting the results can have significant impact on the query.

Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?

MySQL pagination without double-querying?