[SOLVED] mysql query + sort strangeness

[skippylou]

Have a strange one here.

 

First if I attempt this query from the mysql command line, it displays the results properly sorted by the fbytes field descending:

 

mysql> SELECT fproto, fport, SUM(fbytes), AVG(fbytes) FROM flow_ports GROUP BY fproto, fport ORDER BY 'SUM(fbytes)' DESC;

 

When I have the same query in my php code, it randomly displays data in an order that is not desc from most to least by the SUM(fbytes):

 

$sql = "SELECT fproto, fport, SUM(fbytes), AVG(fbytes) FROM flow_ports GROUP BY fproto, fport ORDER BY 'SUM(fbytes)' DESC";

$result = mysql_query( $sql );

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

# table glue here

  echo $row;

# table glue here

}

 

Any thoughts on why a query that works perfectly from the mysql command line would behave differently as far as sort order in php?

 

Thanks,

 

Scott

[skippylou]

I left it out, because I didn't think it mattered (but maybe it does), but in the table it actually isn't: echo $row.  The columns are filled with:

 

echo $row['fproto'];

echo $row['fport'];

echo $row['SUM(fbytes)'];

echo $row['AVG(fbytes)'];

 

There doesn't appear to be any sorting going on amongst any of the columns in the php query.

[trq]

For starters, you really ought give the results of SUM and AVG a name.

 

$sql = "SELECT fproto, fport, SUM(fbytes) as sumfbytes, AVG(fbytes)as avgfbytes FROM flow_ports GROUP BY fproto, fport ORDER BY sumfbytes DESC";

[skippylou]

Thanks!  That fixed my issues!

 

Just curious, do you know why that caused a problem?

 

Thanks again,

 

Scott

[rajivgonsalves]

well you where actually trying to short with a string

 

ORDER BY 'SUM(fbytes)' DESC

 

so it would basically make no difference but as thorpe gave the field a name it sorts by the actual data

 

hope its helpful

[skippylou]

Ah, interesting, that makes sense - thanks.  I guess from the mysql command line interface, it knows the data is an integer field and sorted properly the original way I had it.

 

Thanks guys!