Order by variable outside mysql

[Anon]

(Sorry, posted this the wrong place before... still got the problem!)

Hi!

I've got a problem. Let me explain:

Usersystem:
I've got this usersystem, where a user can choose his/her favourite fruit.

The fruits is stored in a mysql table called "fruits", and each fruit has an ID-number. (example> Orange has 1).

When the user has selected his/her favourite fruit, it's stored in the table "userlist" in a field called "favefruit".

Now what I want to do is count the most popular fruits and order the output by percentages:
- The most popular.
- The second most popular.
- The third most popular.

And so on.

Code:

What I've done is this:

<?

$getfruits = mysql_query("SELECT * FROM fruits");
//Selects all fruits.

$getnumberoffruits = mysql_num_rows($getfruits);
//Counts the number of fruits.

$getuserfavetotal = mysql_query("SELECT * FROM userlist WHERE favefruit != '0'");
//Selects all the fields where favourite fruit is selected.

$userfavetotal = mysql_num_rows($getuserfavetotal);
//Counts the number of users who've selected their favorite fruit.

for ($i = 0; $i < $getnumberoffruits; $i++) {
//Creates a loop to count which fruits people like.

$getfruitrow = mysql_fetch_array($getfruits);
//Fetches the array from the string $getfruits.

$fruitname = $getfruitrow['name'];
//Sets the variable fruitname to this fruit. Let's say that it's "Oranges" in this example.

$fruitid = $getfruitrow['id'];
//Sets the variable fruitid to this fruit. Let's say that it's "1" in this example.

$getuserfave = mysql_query("SELECT * FROM userlist WHERE favefruit = '$fruitid'");
//Selects all the fields where users have Oranges as their favourite fruit.

$getfruitfavetotal = mysql_num_rows($getuserfave);
//Counts the number of fields where users have Oranges as their favourite fruit.

$popularity = ($getfruitfavetotal/userfavetotal)*100;
$popularity = ereg_replace("%0d", $popularity, $popularity);
//Creates the popularity percentage of Oranges.
?>

<table>
<tr>
<td>
<? echo $fruitname; ?>
</td>
<td>
<? echo $popularity; ?>
</td>
</tr>
</table>

//It's this I like to change. The fruits will not be ordered by $popularity, but by the $fruitid. How can I order by popularity?

Example> If Bananas are the most popular, and Oranges the second most popular, it would still show:

Oranges 2%
Bananas 98 %

What I want is:

Bananas 98%
Oranges 2%

<?
}
//It closes the loop and continues on to the next fruit.
?>

I hope this wasn't to confusing, but I've thought a lot about how I should explain this.

• Brian

[[deleted]]

I use something along these lines in my logging script. The basics of which, I got from an article on this site.

<TABLE>

<?
// connect to mysql and db then

$result = mysql_query("SELECT favefruit, count(favefruit) as num FROM userlist GROUP BY favefruit ORDER BY num DESC");

$list = mysql_num_rows($result);
while ($i < $list)
{
$row = mysql_fetch_array($result);
$key = $row["favefruit"];

/ i haven't described the relationship between userlist.favefruit and fruits.fruitid here so you may have to fiddle with this a bit /

$resulta= mysql_query ("SELECT * FROM userlist WHERE favefruit = '$key'");
$count = mysql_num_rows($resulta);

// prints in table format print"<TR><TD>$key</TD><TD>$count</TD></TR>";
$i++;
}

?>
</TABLE>

<P>

<?

/ you could use this to find percentage of each fruit /

$resulta= mysql_query ("SELECT * FROM userlist");
$count = mysql_num_rows($resulta);
print"<P>Total Users = $count";

?>

[Anon]

select favefruit, count(*) from userlist where favefruit <> 0 group by favefruit order by favefruit desc

Charley

[Anon]

jrd wrote:
/ i haven't described the relationship between userlist.favefruit and fruits.fruitid here so you may have to fiddle with this a bit /

Well that's exactly the problem i've got.

[hayguy]

$key is userlist.favefruit which corresponds with your id number in table 'fruit'.

$resulta = mysql_query("SELECT * FROM userlist, fruit WHERE fruit.id = userlist.favefruit AND userlist.favefruit = '$key'");
$count = mysql_num_rows($resulta);
$rowa = mysql_fetch_array($resulta);

$fruitname = $rowa["fruitname"];

print "<TR><TD>$fruitname</TD><TD>$count</TD></TR>";

$i++;
}