sorting mySQL

jimi_81 · May 5th, 2005

Hi guys.
question for the programmers..

i have a standings table.
Games played, wins, losses, ties, goals against, goals for.

i create points on the fly.
so.... $pts = 3*$wins + $ties

Is it possible to sort everything by $pts? Points should have been in the DB, but because we were running into problems when we were updating scores.

any tips would be cool
thanks

**j79zlr** · May 5th, 2005

I think the easiest thing to do would be to create another column in your table that includes points. You could use a sorting algorithm, but that would be overly complicated, if you insert the points into the table, then you could use

$query = "SELECT * FROM '$standings' ORDER BY '$pts' DESC";

I used to keep a site up for my fantasy baseball since we do monthly stats, and I would store the total in there as a similar thing, ending - beginning, here is my insert.php:

PHP Code:


			
<?php

$username = "xxx";

$password = "xxx";

$db = "stats";

$stat = $_POST['stat'];

$team = $_POST['team'];

$start = $_POST['start'];

$end = $_POST['end'];

$total = $end - $start;



mysql_connect(localhost,$username,$password);

@mysql_select_db($db) or die("Unable to connect to the database");



$query = "INSERT INTO $stat VALUES ('','$team','$start','$end','$total')";

mysql_query($query);

echo "Successful!<br /><br />";

echo "<a href=\"insert.php\">Insert more values</a><br />\n";

echo "<a href=\"view.php?stat=" . $stat . "\">View/update current values</a><br />\n";

echo "<a href=\"index.php\">Return to admin homepage</a>\n";



mysql_close();

?>

and then the standing page:

PHP Code:


			
<?php

  /* GZip compress the page */

  ob_start( 'ob_gzhandler' );     

  /* XML doctype */            

  echo('<?xml version="1.0" encoding="ISO-8859-1"?>');    

  /* database info */

  $username = "xxx";                    

  $password = "xxx";

  $db = "stats";

  /* get variables */

  $stat = $_GET['stat'];

  $sortby = $_GET['sortby'];

  /* check for null value of sortby */

  if (is_null($sortby)) {

    $sortby = 'total';

  }

  /* check for SQL injections */

  $sort_test = array("team", "start", "end", "total");

  $stat_test = array("sb", "rbi", "so", "sv", "runs", "wins");

  /* do some replacing if they aren't found */

  if (!in_array($sort, $sort_test)) {

    $sort = 'total';

  }

  if (!in_array($stat, $stat_test)) {

    $stat = 'sb';

  }

  /* Connect to the db */

  mysql_connect(localhost,$username,$password);

  @mysql_select_db($db) or die( "Unable to connect to the database");

  /* Order results by variable */

  if ($sortby == 'team') {

    $query = "SELECT * FROM $stat ORDER BY $sortby ASC";

  }

  else {

    $query = "SELECT * FROM $stat ORDER BY '$sortby' DESC";

  }  

  /* Store query info */

  $result = mysql_query($query);

  $num = mysql_numrows($result);

  /* Set categories and months */

  if ($stat == 'sb') {

    $cat = 'Stolen Bases';

    $month = 'April';

  }

  if ($stat == 'rbi') {

    $cat = 'RBI\'s'; 

    $month = 'May';

  }

  if ($stat == 'so') {

    $cat = 'Strikeouts';

    $month = 'June';

  }

  if ($stat == 'sv') {

    $cat = 'Saves';

    $month = 'July';

  }

  if ($stat == 'runs') {

    $cat = 'Runs';

    $month = 'August';

  }

  if ($stat == 'wins') {

    $cat = 'Wins';

    $month = 'September';

  }

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"

    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

<head> 

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />

<meta http-equiv="Content-Style-Type" content="text/css" />

<title>xxx :: <?php echo "$month"; ?> Stats</title>

<link rel="stylesheet" type="text/css" href="css/style.css" />

<link rel="icon" type="image/x-icon" href="favicon.ico" />

</head>

<body>

<table >

  <tr>

    <td class="page">

<?php 

  include('blocks/top.html');

?>

<table class="content">



  <tr>



    <th>Links</th>



    <th><?php echo "$month"; ?> Stats</th>



  </tr>



  <tr>



    <td class="nav">







      <?php 



        include('blocks/nav.html');



      ?>







    </td>



    <td class="body">



<?php 



echo "<table class=\"stats\">\n";



echo "<tr class=\"header\">\n";



echo "<td><a href=\"monthly.php?stat=$stat&amp;sortby=team\">Team</a></td>\n";



echo "<td><a href=\"monthly.php?stat=$stat&amp;sortby=start\">Starting $cat</a></td>\n";



echo "<td><a href=\"monthly.php?stat=$stat&amp;sortby=end\">Ending $cat</a></td>\n";



echo "<td><a href=\"monthly.php?stat=$stat&amp;sortby=total\">Total $cat</a></td>\n";



echo "</tr>\n";







$i=0;



while ($i < $num) {



$id = mysql_result($result,$i,"id");



$team = mysql_result($result,$i,"team");



$start = mysql_result($result,$i,"start");



$end = mysql_result($result,$i,"end");



$total = mysql_result($result,$i,"total");



echo "<tr class=\"hl\">\n";



echo "<td>$team</td>\n";



echo "<td>$start</td>\n";



echo "<td>$end</td>\n";



echo "<td>$total</td>\n";



echo "</tr>\n";



++$i;



}







echo "</table>\n";



mysql_close();



?>







    </td>



  </tr>



</table>







<?php



  include('blocks/bottom.html');



?>



    </td>



  </tr>



</table>



</body>



</html>

**Khayman** · May 5th, 2005

try

Code:

Select *, ((wins+3)*ties) as points from standings order by points

**X-Istence** · May 5th, 2005

Originally Posted by Khayman

try

Code:

Select ((wins+3)*ties) as points from standings

I agree with this posting, except

it would have to be like

Code:

select *, ((wins*3)+ties) as points from standings ORDER BY points DESC

**Khayman** · May 5th, 2005

Originally Posted by X-Istence

I agree with this posting, except

it would have to be like
Code:
select *, ((wins*3)+ties) as points from standings ORDER BY points DESC

lol, i edited mine before i saw your post to the same thing

jimi_81 · May 6th, 2005

thanks guys! it works! woot.

May 5th, 2005	Top \| #1
jimi_81 OSNN Senior Addict Joined: May 2002 Location: Stoney Creek, ON, Canada Posts: 820 Reputation: 500 Power: 135	sorting mySQL Hi guys. question for the programmers.. i have a standings table. Games played, wins, losses, ties, goals against, goals for. i create points on the fly. so.... $pts = 3*$wins + $ties Is it possible to sort everything by $pts? Points should have been in the DB, but because we were running into problems when we were updating scores. any tips would be cool thanks

May 5th, 2005	Top \| #2
j79zlr Glaanies script monkey Joined: February 2003 Location: Chicago Posts: 2,725 Reputation: 1520 Power: 155	Re: sorting mySQL I think the easiest thing to do would be to create another column in your table that includes points. You could use a sorting algorithm, but that would be overly complicated, if you insert the points into the table, then you could use $query = "SELECT * FROM '$standings' ORDER BY '$pts' DESC"; I used to keep a site up for my fantasy baseball since we do monthly stats, and I would store the total in there as a similar thing, ending - beginning, here is my insert.php: PHP Code: <?php $username = "xxx"; $password = "xxx"; $db = "stats"; $stat = $_POST['stat']; $team = $_POST['team']; $start = $_POST['start']; $end = $_POST['end']; $total = $end - $start; mysql_connect(localhost,$username,$password); @mysql_select_db($db) or die("Unable to connect to the database"); $query = "INSERT INTO $stat VALUES ('','$team','$start','$end','$total')"; mysql_query($query); echo "Successful!<br /><br />"; echo "<a href=\"insert.php\">Insert more values</a><br />\n"; echo "<a href=\"view.php?stat=" . $stat . "\">View/update current values</a><br />\n"; echo "<a href=\"index.php\">Return to admin homepage</a>\n"; mysql_close(); ?> and then the standing page: PHP Code: <?php /* GZip compress the page / ob_start( 'ob_gzhandler' ); / XML doctype / echo('<?xml version="1.0" encoding="ISO-8859-1"?>'); / database info / $username = "xxx"; $password = "xxx"; $db = "stats"; / get variables / $stat = $_GET['stat']; $sortby = $_GET['sortby']; / check for null value of sortby / if (is_null($sortby)) { $sortby = 'total'; } / check for SQL injections / $sort_test = array("team", "start", "end", "total"); $stat_test = array("sb", "rbi", "so", "sv", "runs", "wins"); / do some replacing if they aren't found / if (!in_array($sort, $sort_test)) { $sort = 'total'; } if (!in_array($stat, $stat_test)) { $stat = 'sb'; } / Connect to the db / mysql_connect(localhost,$username,$password); @mysql_select_db($db) or die( "Unable to connect to the database"); / Order results by variable / if ($sortby == 'team') { $query = "SELECT FROM $stat ORDER BY $sortby ASC"; } else { $query = "SELECT * FROM $stat ORDER BY '$sortby' DESC"; } /* Store query info / $result = mysql_query($query); $num = mysql_numrows($result); / Set categories and months */ if ($stat == 'sb') { $cat = 'Stolen Bases'; $month = 'April'; } if ($stat == 'rbi') { $cat = 'RBI\'s'; $month = 'May'; } if ($stat == 'so') { $cat = 'Strikeouts'; $month = 'June'; } if ($stat == 'sv') { $cat = 'Saves'; $month = 'July'; } if ($stat == 'runs') { $cat = 'Runs'; $month = 'August'; } if ($stat == 'wins') { $cat = 'Wins'; $month = 'September'; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <title>xxx :: <?php echo "$month"; ?> Stats</title> <link rel="stylesheet" type="text/css" href="css/style.css" /> <link rel="icon" type="image/x-icon" href="favicon.ico" /> </head> <body> <table > <tr> <td class="page"> <?php include('blocks/top.html'); ?> <table class="content"> <tr> <th>Links</th> <th><?php echo "$month"; ?> Stats</th> </tr> <tr> <td class="nav"> <?php include('blocks/nav.html'); ?> </td> <td class="body"> <?php echo "<table class=\"stats\">\n"; echo "<tr class=\"header\">\n"; echo "<td><a href=\"monthly.php?stat=$stat&sortby=team\">Team</a></td>\n"; echo "<td><a href=\"monthly.php?stat=$stat&sortby=start\">Starting $cat</a></td>\n"; echo "<td><a href=\"monthly.php?stat=$stat&sortby=end\">Ending $cat</a></td>\n"; echo "<td><a href=\"monthly.php?stat=$stat&sortby=total\">Total $cat</a></td>\n"; echo "</tr>\n"; $i=0; while ($i < $num) { $id = mysql_result($result,$i,"id"); $team = mysql_result($result,$i,"team"); $start = mysql_result($result,$i,"start"); $end = mysql_result($result,$i,"end"); $total = mysql_result($result,$i,"total"); echo "<tr class=\"hl\">\n"; echo "<td>$team</td>\n"; echo "<td>$start</td>\n"; echo "<td>$end</td>\n"; echo "<td>$total</td>\n"; echo "</tr>\n"; ++$i; } echo "</table>\n"; mysql_close(); ?> </td> </tr> </table> <?php include('blocks/bottom.html'); ?> </td> </tr> </table> </body> </html> http://www.j79zlr.com

May 5th, 2005	Top \| #3
Khayman I'm sorry Hal... Joined: January 2002 Location: England Posts: 5,514 Reputation: 1210 Power: 194	Re: sorting mySQL try Code: Select , ((wins+3)ties) as points from standings order by points "I'm on the server side. I don't know what side you're on"

May 5th, 2005	Top \| #4
X-Istence * Joined: December 2001 Location: USA Posts: 6,496 Reputation: 2808 Power: 220	Re: sorting mySQL Originally Posted by Khayman try Code: Select ((wins+3)ties) as points from standings I agree with this posting, except it would have to be like Code: select , ((wins*3)+ties) as points from standings ORDER BY points DESC An engineers technical notebook

May 5th, 2005	Top \| #5
Khayman I'm sorry Hal... Joined: January 2002 Location: England Posts: 5,514 Reputation: 1210 Power: 194	Re: sorting mySQL Originally Posted by X-Istence I agree with this posting, except it would have to be like Code: select , ((wins3)+ties) as points from standings ORDER BY points DESC lol, i edited mine before i saw your post to the same thing "I'm on the server side. I don't know what side you're on"

Latest News Posts

Latest Forum Posts

Latest Member Blogs

May 6th, 2005	Top \| #6
jimi_81 OSNN Senior Addict Joined: May 2002 Location: Stoney Creek, ON, Canada Posts: 820 Reputation: 500 Power: 135	Re: sorting mySQL thanks guys! it works! woot.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
mp3 sorting by id3 tag	Abbadon2001	Windows Desktop Systems	7	March 6th, 2005 7:10pm
[C++] sorting MASSIVE amounts of data	Rexy	Web Design & Coding	9	August 22nd, 2003 4:16am
spam sorting prog?	cky	Windows Desktop Systems	3	December 29th, 2002 8:40am
WMP Video Freeze, Folder Sorting	aidoru	Windows Desktop Systems	3	September 27th, 2002 3:00am
sorting favorites	bisher	Windows Desktop Systems	1	August 24th, 2002 3:48pm