mysql sort problem

[tom_b]

I hope I can explain this O.K.  I have a database with 3 columns.  I can input numbers into it, run a query and output the last entry in each column, then run a second query and output the total of each column so the output looks like this:

 

a  10    50

b  15    60

c  12    55

 

it works fine but I'm stumped as how to sort the output by totals.  Any ideas?

 

Thanks, Tom

[hitman6003]

Not sure what you mean...like add the latter two columns and sort by that?

 

SELECT col_a ORDER BY (col_b + col_c) ASC

 

I think should work...haven't tried it.

[tom_b]

I want it to end up looking like this:

 

b  15  60

c  12  55

a  10  50

 

showing the last entry, then the totals.  My problem is that they are all separate queries, one to find the total of a, then one for b and one for c, so I can't just sort on one query.

[hitman6003]

Well, you can either combine your queries into one, then place an ORDER BY clause on it, or you can do two (or however many) queries and place all your data into an array, then sort the array.

 

What are your queries?

[redarrow]

post your database query to help you?

[tom_b]

These queries select the last entry from column a, then the total of that column.  I have similar queries for columns b and c.  Everything works fine, I just can't sort the output based on the separate queries that total columns a, b and c.

 

$query = "select a from week where id=(select max(id)from week)";   

$result=mysql_query($query);

$num=mysql_numrows($result);

 

    while ($row = mysql_fetch_array($result)) {

    ?><tr><td> <?php

    $a= $row['a'];

    echo "<font color ='blue' font size = '3'>$a<br>";

}

$result = mysql_query ("select sum(a) from week");   

 

    $sum = mysql_fetch_row($result);

    $sum = $sum[0]; 

    echo "<font color ='blue' font size = '3'>$sum<br>";

 

Thanks, I do appreciate your time!!  Tom

 

[hitman6003]

Well, you can at least reduce it to two queries...

 

$values = array();

$query = 'SELECT a, b, c FROM week ORDER BY id DESC LIMIT 1';
$result = mysql_query($query) or die(mysql_error());

$values['a']['current'] = mysql_result($result, 0, "a");
$values['b']['current'] = mysql_result($result, 0, "b");
$values['c']['current'] = mysql_result($result, 0, "c");

$query = 'SELECT SUM(a) AS a, SUM(b) AS b, SUM(c) AS c FROM week';
$result = mysql_query($query) or die(mysql_error());

$values['a']['sum'] = mysql_result($result, 0, "a");
$values['b']['sum'] = mysql_result($result, 0, "b");
$values['c']['sum'] = mysql_result($result, 0, "c");

 

You should then be able to use the example here:

 

http://us2.php.net/array_multisort#id2766574

 

to sort your results.

[tom_b]

O.K., the queries you wrote make sense (except I'm not sure what 'current' means!!).  I'll have to check out that link more closely tomorrow.  Would I then put my data into an array to sort it?  Haven't done anything like that before, but it does make sense.  Thanks again for you help!!

 

Tom