sorting mySQL

Discussion in 'Web Design & Coding' started by jimi_81, May 5, 2005.

  1. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    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
     
  2. j79zlr

    j79zlr Glaanies script monkey Political User

    Messages:
    2,725
    Location:
    Chicago
    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: May 5, 2005
    jimi_81 likes this.
  3. Khayman

    Khayman I'm sorry Hal... Political User Folding Team

    Messages:
    5,518
    Location:
    England
    try

    Code:
    Select *, ((wins+3)*ties) as points from standings order by points
     
  4. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
    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
     
    jimi_81 likes this.
  5. Khayman

    Khayman I'm sorry Hal... Political User Folding Team

    Messages:
    5,518
    Location:
    England
    lol, i edited mine before i saw your post to the same thing
     
    jimi_81 likes this.
  6. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    thanks guys! it works! woot.