Query Question

afoster · #1 (**permalink**) 02-25-10, 03:19 PM

I have a table in an mySQL database that lists total points (see attached screen shot), by year with a column that shows total points.

The screen shot shows the results of a query against the database table, which is not the problem. I am looking for a query that will display the ranking for each player for each year, based on the display shown. To further clarify I would like a column after 2005 which is headed 2009 ranking that would show where in the ranking for 2009, Rick T's total would fall. I have been trying to figure this out for a few days, but quite frankly don't even know where to start, as a matter of fact I don't know if it is even possible.

Jcbones · #2 (**permalink**) 02-26-10, 06:58 PM

Post your code, it is do-able, but we need to know what you're working with the help you out.

afoster · #3 (**permalink**) 02-27-10, 12:02 AM

Here is the code that I started working with after posting this request.

Code:

<?php

// Make a MySQL Connection

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("database") or die(mysql_error());



// Get all the data from the "example" table

$result = mysql_query("SELECT * FROM table ORDER BY total DESC, name ASC") 

or die(mysql_error());

$rank09 = mysql_query("SELECT yr2009 FROM table ORDER BY yr2009 ASC") 

or die(mysql_error());  



echo "<table class=datatable align=center border=0 cellpadding=0 cellspacing=2 width=100%><tr><td valign=top>";

echo "<table class=datatable align=center width=100% cellspacing=1 cellpadding=2>";

echo "<tr> <th width=20%>Name</th> <th width=6%>Tot Points</th> <th width=6%>2010</th> <th width=6%>2009</th><th width=6%>2008</th><th width=6%>2007</th><th width=6%>2006</th><th width=6%>2005</th><th width=6%>2010<br>Rank</th> <th width=6%>2009<br>Rank</th><th width=6%>2008<br>Rank</th><th width=6%>2007<br>Rank</th><th width=6%>2006<br>Rank</th><th width=6%>2005<br>Rank</th></tr>";

// keeps getting the next row until there are no more to get

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

	

	// Print out the contents of each row into a table

	

	

	echo "<tr class=evenrow onMouseover=this.style.backgroundColor='#FFFFFF';

	 onMouseout=this.style.backgroundColor='#E9E9E9'><td bgcolor=#e0e0e0>"; 

	echo $row['name'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>"; 

	echo $row['total'];

	echo "</td><td bgcolor=#e0e0e0>";

	echo "<center>";

	echo $row['yr2010'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>";

	echo $row['yr2009'];

	echo "</td><td bgcolor=#e0e0e0>";

	echo "<center>";

	echo $row['yr2008'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>";

	echo $row['yr2007'];

	echo "</td><td bgcolor=#e0e0e0>";

	echo "<center>";

	echo $row['yr2006'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>";

	echo $row['yr2005'];

	echo "</td>;

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

	 <td bgcolor=#e0e0e0>";

	echo "<center>";

	echo $row['yr2010'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>";

	echo $row['yr2009'];

	echo "</td><td bgcolor=#e0e0e0>";

	echo "<center>";

	echo $row['yr2008'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>";

	echo $row['yr2007'];

	echo "</td><td bgcolor=#e0e0e0>";

	echo "<center>";

	echo $row['yr2006'];

	echo "</td><td bgcolor=#e9e9e9>";

	echo "<center>";

	echo $row['yr2005'];

	echo "</td></tr>";

	echo "</center>"; 

} 



echo "</table>";

echo "</center>";

?>

This code has generated the display noted in the attached screen dump, and it also has a bunch of error msgs above the display. The error msgs are as follows. This is but one line of many with the same error msg.

while(Array = mysql_fetch_array( Resource id #3 )) { ; while(Array = mysql_fetch_array( Resource id #3 )) { ; while(Array = mysql_fetch_array( Resource id #3 )) {

The display in the screen dump is how I want it to look, except that the column starting with Ranking 2009 should contain the ranking of players for that year, not the total points that it currently displays. I have a feeling that I don't have enough information in the table, but thought I would check. thanks in advance for your help.

Jcbones · #4 (**permalink**) 02-27-10, 09:52 PM

I don't have the database info, so I made a passing try at getting it right on the first go around. It will most likely need some tweaking, so post back if it doesn't go off to well on this try.

PHP Code:


			
<?php

// Make a MySQL Connection

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("database") or die(mysql_error());



// Get all the data from the "example" table

$result = mysql_query("SELECT * FROM table ORDER BY total DESC, name ASC") 

or die(mysql_error());

$rank09 = mysql_query("SELECT yr2009 FROM table ORDER BY yr2009 ASC") 

or die(mysql_error());  



echo "<table class=datatable align=center border=0 cellpadding=0 cellspacing=2 width=100%><tr><td valign=top>";

echo "<table class=datatable align=center width=100% cellspacing=1 cellpadding=2>";

echo "<tr> <th width=20%>Name</th> <th width=6%>Tot Points</th> <th width=6%>2010</th> <th width=6%>2009</th><th width=6%>2008</th><th width=6%>2007</th><th width=6%>2006</th><th width=6%>2005</th><th width=6%>2010<br>Rank</th> <th width=6%>2009<br>Rank</th><th width=6%>2008<br>Rank</th><th width=6%>2007<br>Rank</th><th width=6%>2006<br>Rank</th><th width=6%>2005<br>Rank</th></tr>";

// keeps getting the next row until there are no more to get

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

    
    //Lets do some homework first
    //Lets also set some variables, so the code will be easier to read.
    $name = $row['name'];
    $total = $row['total'];
    $y10 = $row['yr2010'];
    $y09 = $row['yr2009'];
    $y08 = $row['yr2008'];
    $y07 = $row['yr2007'];
    $y06 = $row['yr2006'];
    $y05 = $row['yr2005'];    

    //Now let us process these values.
    //We will store all of the points together.
    $r10[] = $y10;
    $r9[] = $y09;
    $r8[] = $y08;
    $r7[] = $y07;
    $r6[] = $y06;
    $r5[] = $y05;

    //Now let us get all the data put together for sorting.
    $hold[$name][$total][$y10] = 10;
    $hold[$name][$total][$y09] = 9;
    $hold[$name][$total][$y08] = 8;
    $hold[$name][$total][$y07] = 7;
    $hold[$name][$total][$y06] = 6;
    $hold[$name][$total][$y05] = 5;
    
}

//Now we should have everythng we need to process these items.
//Line up the scores from highest to lowest.
rsort($r10);
rsort($r9);
rsort($r8);
rsort($r7);
rsort($r6);
rsort($r5);
    // Print out the contents of each row into a table
$n = 0;
foreach($hold as $name => $a2) {
    echo "<tr class=evenrow onMouseover=this.style.backgroundColor='#FFFFFF';

     onMouseout=this.style.backgroundColor='#E9E9E9'><td bgcolor=#e0e0e0>$name</td>";
     
     foreach($a2 as $total => $a3) {
        echo "<td bgcolor=#e9e9e9><center>$total</td>";
        
        foreach($a3 as $points => $value) {
            $bg = ((++$n % 2) == 0) ? '#e0e0e0' : '#e9e9e9';
            echo "<td bgcolor=$bg><center>$points</td>";
                        $v = 'r' . $value;
            $ranks[$value] = array_search($points,$$v);
        }
            $n = 0;
        for($i = 10; $i >= 5; $i--) {
            $bg = ((++$n % 2) == 0) ? '#e0e0e0' : '#e9e9e9';
             echo "<td bgcolor=$bg><center>" . $ranks[$i] . "</td>";
        }
    }
    echo "</tr>";
}

echo '</table>';

?>

afoster · #5 (**permalink**) 02-27-10, 11:50 PM

Thanks for your response Jcbones, I think we are getting close. I took the code you provided in the previous post, and other than entering the database and table names, I uploaded it the script to the following site:

Lifetime Stats to see the result. I am not adept enough to be able to figure it out, but there are some errors as noted in the display. For instance, there should be no entries in the 2010 Rank column, as there are no points in the 2010 column. In addition, there are duplicate numbers in some of the other Rank columns.

As an example, the content of the 2009 Rank column (first five places) should be: (the names are the names in the first column (name)

Fred B 6
Rick T 5
John K 11
Kevin B 7
David H 22
Etc

I hope this makes sense, and thanks again for your help on this.

Jcbones · #6 (**permalink**) 03-01-10, 07:35 PM

Sorry it took so long for me to get back with you, I never have enough time for my projects.

Are all of the total points, and the 05/10 points correct?

Jcbones · #7 (**permalink**) 03-01-10, 08:35 PM

Try this one. very slight modifications.

PHP Code:


			
<?php

// Make a MySQL Connection

mysql_connect("localhost", "root", "") or die(mysql_error());

mysql_select_db("test") or die(mysql_error());



// Get all the data from the "example" table

$result = mysql_query("SELECT * FROM `test` ORDER BY `total` DESC, `name` ASC") 

or die(mysql_error());

$rank09 = mysql_query("SELECT yr2009 FROM `test` ORDER BY `yr2009` ASC") 

or die(mysql_error());  



echo "<table class=datatable align=center border=1 cellpadding=0 cellspacing=2 width=100%><tr><td valign=top>";

echo "<table class=datatable align=center width=100% cellspacing=1 cellpadding=2>";

echo "<tr> <th width=20%>Name</th> <th width=6%>Tot Points</th> <th width=6%>2010</th> <th width=6%>2009</th><th width=6%>2008</th><th width=6%>2007</th><th width=6%>2006</th><th width=6%>2005</th><th width=6%>2010<br>Rank</th> <th width=6%>2009<br>Rank</th><th width=6%>2008<br>Rank</th><th width=6%>2007<br>Rank</th><th width=6%>2006<br>Rank</th><th width=6%>2005<br>Rank</th></tr>";

// keeps getting the next row until there are no more to get

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

    
    //Lets do some homework first
    //Lets also set some variables, so the code will be easier to read.
    $name = $row['name'];
    $total = $row['total'];
    $y10 = $row['yr2010'];
    $y09 = $row['yr2009'];
    $y08 = $row['yr2008'];
    $y07 = $row['yr2007'];
    $y06 = $row['yr2006'];
    $y05 = $row['yr2005'];    

    //Now let us process these values.
    //We will store all of the points together.
    $r10[] = $y10;
    $r9[] = $y09;
    $r8[] = $y08;
    $r7[] = $y07;
    $r6[] = $y06;
    $r5[] = $y05;

    //Now let us get all the data put together for sorting.
    $hold[$name][$total][10] = $y10;
    $hold[$name][$total][9] = $y09;
    $hold[$name][$total][8] = $y08;
    $hold[$name][$total][7] = $y07;
    $hold[$name][$total][6] = $y06;
    $hold[$name][$total][5] = $y05;
    
}

//Now we should have everythng we need to process these items.
//Line up the scores from highest to lowest.
rsort($r10);
rsort($r9);
rsort($r8);
rsort($r7);
rsort($r6);
rsort($r5);

//Debugging
// echo '<pre>';
// print_r($hold);
// echo '</pre>';

    // Print out the contents of each row into a table
$n = 0;
foreach($hold as $name => $a2) {
    echo "<tr class=evenrow onMouseover=this.style.backgroundColor='#FFFFFF';

     onMouseout=this.style.backgroundColor='#E9E9E9'><td bgcolor=#e0e0e0>$name</td>";
     
     foreach($a2 as $total => $a3) {
        echo "<td bgcolor=#e9e9e9><center>$total</td>";
        
        foreach($a3 as $points => $value) {
            $value = ($value == '' || $value == 0) ? '-' : $value;
            $bg = ((++$n % 2) == 0) ? '#e0e0e0' : '#e9e9e9';
            echo "<td bgcolor=$bg><center>$value</td>";
            $v = 'r' . $points;            
            $ranks[$points] = array_search($value,$$v);
        }
            $n = 0;
        for($i = 10; $i >= 5; $i--) {
            $bg = ((++$n % 2) == 0) ? '#e0e0e0' : '#e9e9e9';
// echo '<pre>';
// echo $name . '<br/>';
// print_r($ranks);
// echo '</pre>';
            $ranking = ($ranks[$i] === false) ? '-' : $ranks[$i] + 1;
             echo "<td bgcolor=$bg><center>" . $ranking . "</td>";
        }
    }
    echo "</tr>";
}

echo '</table>';

?>

afoster · #8 (**permalink**) 03-01-10, 09:49 PM

Yes, I believe this last bit of code you sent works. I'm double checking to make sure that the numbers are the same, but at first glance they are. Thank you very much for your help.

Now to get greedy, would it be possible to link the headers so that when clicked on, they would be sorted? I would need the headings labeled 2010, 2009, etc sorted by tot points DESC and the headings labeled 2010 Rank, 2009 Rank, etc sorted by place ASC. That would be awesome.

Jcbones · #9 (**permalink**) 03-02-10, 05:03 PM

You could do this with javascript, and save server load. Or, just pass the sort in the url using the GET method.

PHP Code:


			


<a href="list.php?column=10&sort=desc">^</a>



<?php

$column = (isset($_GET['column'])) ? mysql_real_escape_string(strip_tags($_GET['column'])) : 10;

$sort = (isset($_GET['sort'])) ? mysql_real_escape_string(strip_tags($_GET['sort'])) : 'DESC';



switch($column) {

case 5:

  $column = 'yr2005';

  break;

case 6:

  $column = 'yr2006';

  break;

case 7:

  $column = 'yr2007';

  break;

case 8:

  $column = 'yr2008';

  break;

case 9:

  $column = 'yr2009';

  break;

default:

  $column = 'yr2010';

}



switch($sort) {

 case 'asc':

  $sort = 'ASC';

  break;

default:

  $sort = 'DESC';

}



$sql = "SELECT * FROM `table` ORDER BY $column $sort";



?>

Jcbones · #10 (**permalink**) 03-02-10, 05:06 PM

Link to javascript sorting tutorial.

Sortable Table JavaScript: Sort your tables easily!