sorting mySQL

jimi_81

OSNN Senior Addict
Political User
#1
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

Glaanies script monkey
Political User
#2
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>
 
Last edited:

Khayman

I'm sorry Hal...
Political User
#5
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
 

Members online

No members online now.

Latest posts

Latest profile posts

Perris Calderon wrote on Electronic Punk's profile.
Ep, glad to see you come back and tidy up...did want to ask a one day favor, I want to enhance my resume , was hoping you could make me administrator for a day, if so, take me right off since I won't be here to do anything, and don't know the slightest about the board, but it would be nice putting "served administrator osnn", if can do, THANKS

Been running around Quora lately, luv it there https://tinyurl.com/ycpxl
Electronic Punk wrote on Perris Calderon's profile.
All good still mate?
Hello, is there anybody in there? Just nod if you can hear me ...
Xie
What a long strange trip it's been. =)

Forum statistics

Threads
61,971
Messages
673,299
Members
89,016
Latest member
Poseeut