SQL Query sort order

[TRI0N]

Okay starting to loose my hair over this but why is this not extracting the information from the database in ASC order as it creates the page?

 

<?php
$i = 0 ;

// Find Player ID for each Player
$result9 = mysql_query("select distinct * from players WHERE (eventid = '$eventid')") ;
if(mysql_num_rows($result9) == 0){
echo "<font color=#800000>None</font>" ;
}else{
while($row = mysql_fetch_row($result9)) {
$playerid = $row[0] ;
$fname = $row[4] ;
$lname = $row[5] ;
$gkname = $row[6] ;
$rounds_comp = $row[8] ;
$i = $i + 1 ;

if ($rounds_comp >= 2) {
// Extract Score Data 1
$result10 = mysql_query("select distinct * from scores WHERE (eventid = '$eventid' and playerid = '$playerid' and round = '2' and complete = '1') ORDER BY gross ASC") ;
while($row = mysql_fetch_row($result10)) {
$gross3 = $row[22] ;
$net3 = $row[23] ;
$stable_gross3 = $row[24] ;
$stable_net3 = $row[25] ;
}

$score = $gross3 ;
echo "<tr>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">$i</td>" ;
echo "<td width=\"80%\">" ;
echo "<p align=\"left\">".$fname." ".$lname."      <img src=\"images/gk.jpg\" alt=\"Member\"</td>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">".$score."</td>" ;
echo "</tr>" ;
}
}
}
?>

 

Any help and tips would be GREAT!

[Psycho]

From the manual:

 

"Because DISTINCT may use GROUP BY, you should be aware of how MySQL works with columns in ORDER BY or HAVING clauses that are not part of the selected columns"

 

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

 

Basically the DISTINCT is using a GROUP BY. So, how is it supposed to order by "gross" when each record that is returned can be the culmination of several records?

[scottybwoy]

Just a suggestion, if you are not using all the data from the database, you could select just the fields you want by specifying :

<?php
$sql = "SELECT DISTINCT 0, 4, 5, 6, 8 FROM players WHERE eventid = '$eventid')"

while($row = mysql_fetch_row($result9)) {
$playerid = $row[0] ;
$fname = $row[1] ;
$lname = $row[2] ;
$gkname = $row[3] ;
$rounds_comp = $row[4];
}

etc...
?>

 

Then you can look back at you qry to reference what the cols are and it'll speed up your script.

[TRI0N]

Okay tried your tip there and for $fname I get 4 and for $lname I get 5..  Are you saying to use the numbers like you do in your distinct example or use the acutal table colum names for each of those..  Till then I'll go back to * since the tip just went AWOL.

[TRI0N]

Okay removed the DISTINCT from the query that is to be SORTED BY gross DESC.

 

Still no go. On having this sort out properly.

 

Code as changed:

<?php
$i = 0 ;

// Find Player ID for each Player
$result9 = mysql_query("SELECT DISTINCT * FROM players WHERE (eventid = '$eventid')") ;
if(mysql_num_rows($result9) == 0){
echo "<font color=#800000>None</font>" ;
}else{
while($row = mysql_fetch_row($result9)) {
$playerid = $row[0] ;
$fname = $row[4] ;
$lname = $row[5] ;
$gkname = $row[6] ;
$rounds_comp = $row[8] ;
$i = $i + 1 ;

if ($rounds_comp >= 2) {
// Extract Score Data 1
$result10 = mysql_query("SELECT * FROM scores WHERE (eventid = '$eventid' AND playerid = '$playerid' AND round = '2' AND complete = '1') ORDER BY gross DESC") ;
while($row = mysql_fetch_row($result10)) {
$gross3 = $row[22] ;
$net3 = $row[23] ;
$stable_gross3 = $row[24] ;
$stable_net3 = $row[25] ;
}

$score = $gross3 ;
echo "<tr>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">$i</td>" ;
echo "<td width=\"80%\">" ;
echo "<p align=\"left\">".$fname." ".$lname."      <img src=\"images/gk.jpg\" alt=\"Member\"</td>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">".$score."</td>" ;
echo "</tr>" ;
}
}
}
?>

[TRI0N]

Okay looking at that link provided with sure isn't much help to any newbie so perhaps asking the question further in terms to where it "MIGHT HELP A NEWBIE" lets see if this sheds light. From what I can get out of it your saying since there is 2 Database Calls to 2 Differnt Tables I need to combine them both into groups and since $eventid is the key to both of the tables uses.

 

SELECT DISTINCT players.playerid, players.firstname, players.lastname, players.gkname, players.rounds_comp, scores.gross, scores.net, scores.stable_gross, scores.stables_net FROM players, scores WHERE eventid = '$eventid' SORT BY gross DESC;

 

or should it be:

 

SELECT DISTINCT players.playerid, players.firstname, players.lastname, players.gkname, players.rounds_comp, scores.gross, scores.net, scores.stable_gross, scores.stables_net FROM players, scores WHERE eventid = '$eventid' GROUP BY gross;

 

Then some sort of other mumbo jumbo that isn't even discussed in that link.

 

God that looks ugly as heck.  Not even sure that is right.  But from that link you provided that is what I make out of the poor example they used and the effort put in to help the problem at hand.  So maybe a more Newbie Approach to my problem would be good towards getting this working?

[TRI0N]

So logical example of a working code that will do what I want is:

 

<?php
$i = 0 ;

$results9 = mysql_query("SELECT DISTINCT players.playerid, players.firstname, players.lastname, players.gkname, players.rounds_comp, scores.gross, scores.net, scores.stable_gross, scores.stable_net FROM players, scores WHERE eventid = '$eventid' AND players.rounds >= '2' SORT BY players.gross");
while($row = mysql_fetch_row($result9)) {
$playerid = $row[0] ;
$fname = $row[1] ;
$lname = $row[2] ;
$gkname = $row[3] ;
$rounds_comp = $row[4] ;
$gross = $row[5] ;
$net = $row[6] ;
$stable_gross = $row[7] ;
$stable_net = $row[8] ;

$i = $i + 1 ;
$score = $gross ;
echo "<tr>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">$i</td>" ;
echo "<td width=\"80%\">" ;
echo "<p align=\"left\">".$fname." ".$lname."      <img src=\"images/gk.jpg\" alt=\"Member of Greenskeeper\"</td>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">".$score."</td>" ;
echo "</tr>" ;
}
?>

 

Is this exceptible? Getting there? Not even in the ballpark?

[TRI0N]

Well the idea I got from those links is not working as to what I made out of it.  So still need help getting this to sort properly.

 

even tried to SORT BY players.gross and GROUP BY players.gross.

 

Its not even putting anything out on the page now so its broken.  Pulled things out fine before with orginal code but it wouldn't sort properly to what I wanted.

[TRI0N]

Okay this is getting me edgy..  I keep trying to peice it together in my head how to get each one to sort correctly..

 

1) Get Player ID by Event ID out of Players Table.

2) List All Players by Event ID and Player ID who have Completed 2 Rounds out of Scores Table Sort By Gross Score.

3) Output List by DESC order.

 

Still my orginal code looks right but I can see the logic in why it doesn't sort because it pulls the Player ID at the start of the loop and then looks for scores to sort by that doesn't control how it looks for the start of the situation for Player ID.

 

Maybe I don't even need to search by player ID now that I think about it.  If Event ID matches and 2 Rounds are played it will pull those scores but then Names and other information will be lost.  I'm going 5150 here..

 

 

[TRI0N]

Okay now I've actually got it to pull out ths scores in the right order but now its showing the same player for both scores that is wrong..  Example:

 

1 Joe Dirt 54

2 Joe Dirt 73

 

When it should be:

 

1 Joe Dirt 54

2 John Doe 73

 

Here is the code..

 

<?php
$i = 0 ;

// Extract Score Data 1
$result9 = mysql_query("SELECT * FROM scores WHERE (eventid = '$eventid' AND round = '2' AND complete = '1') ORDER BY gross ASC") ;
while($row = mysql_fetch_row($result9)) {
$playerid = $row[0] ;
//$eventid = $row[1] ;
$gross3 = $row[22] ;
$net3 = $row[23] ;
$stable_gross3 = $row[24] ;
$stable_net3 = $row[25] ;

$result10 = mysql_query("SELECT * FROM players WHERE (eventid = '$eventid' AND playerid = '$playerid')") ;
while($row = mysql_fetch_row($result10)) {
$fname = $row[4] ;
$lname = $row[5] ;
$gkname = $row[6] ;
$score = $gross3 ;
$i = $i + 1 ;

echo "<tr>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">$i</td>" ;
echo "<td width=\"80%\">" ;
echo "<p align=\"left\">".$fname." ".$lname."      <img src=\"images/gk.jpg\" alt=\"Member\"</td>" ;
echo "<td width=\"10%\">" ;
echo "<p align=\"center\">".$score."</td>" ;
echo "</tr>" ;
  }
}
?>

 

Please help me get this fixed before I jump in front of a UPS Truck...