sorting mySQL

jimi_81 · 5 May 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 · 5 May 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:

<?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:

<?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 · 5 May 2005

try

Code:

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

X-Istence · 5 May 2005

Khayman said:
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 · 5 May 2005

X-Istence said:
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 · 6 May 2005

thanks guys! it works! woot.

sorting mySQL

jimi_81

OSNN Senior Addict

j79zlr

Glaanies script monkey

Khayman

I'm sorry Hal...

X-Istence

*

Khayman

I'm sorry Hal...

jimi_81

OSNN Senior Addict

Members online

Affiliates

Latest profile posts

Forum statistics