#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0

    Mysql category and greatest comparision


    i have a mysql table like so
    Code:
    +------------------+-------------+------+-----+---------+----------------+
    | Field            | Type        | Null | Key | Default | Extra          |
    +------------------+-------------+------+-----+---------+----------------+
    | dyno_id          | int(11)     | NO   | PRI | NULL    | auto_increment |
    | orders_id        | int(11)     | YES  |     | NULL    |                |
    | customers_id     | int(11)     | NO   |     | 0       |                |
    | dyno_time        | time        | YES  |     | NULL    |                |
    | last_modified    | datetime    | YES  |     | NULL    |                |
    | date_purchased   | datetime    | YES  |     | NULL    |                |
    | customers_name   | varchar(64) | NO   |     |         |                |
    | dyno_runone_tq   | int(6)      | NO   |     | 0       |                |
    | dyno_runone_hp   | int(6)      | NO   |     | 0       |                |
    | dyno_runtwo_tq   | int(6)      | NO   |     | 0       |                |
    | dyno_runtwo_hp   | int(6)      | NO   |     | 0       |                |
    | dyno_runthree_tq | int(6)      | NO   |     | 0       |                |
    | dyno_runthree_hp | int(6)      | NO   |     | 0       |                |
    | dyno_engine      | varchar(32) | NO   |     |         |                |
    | dyno_model       | varchar(32) | NO   |     |         |                |
    | dyno_nitrous     | varchar(32) | NO   |     |         |                |
    | division         | varchar(64) | NO   |     | NULL    |                |
    +------------------+-------------+------+-----+---------+----------------+

    right now with this query

    Code:
    select *  from dyno_runs order by division DESC
    and this php

    PHP Code:

    if($orders['division'] != $cat) {
    ?>

       <table border="0" width="100%" cellspacing="0" cellpadding="0">
              <tr>
        <td   class="dataTableContent" align="center"colspan="2"><div align="center">
          
         <? echo "<h3>".$orders['division']."</h3>\r"?>
        </tr>
              <tr>
                <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
                  <tr class="dataTableHeadingRow">
                    <td class="dataTableHeadingContent" width="200">Name</td>
                   
                    <td class="dataTableHeadingContent"  width="200"align="center">First Run</td>
                    <td class="dataTableHeadingContent"  width="200"align="center">Second Run</td>
                    <td class="dataTableHeadingContent"  width="200"align="center">Third Run</td>
                  </tr>
    <? ?>

    <table border="" width="100%" cellspacing="0" cellpadding="0"> 

      <tr>
        <td  class="dataTableContent" align="left" width="200"><?php echo $orders['customers_name']; ?></td>
        
        <td  class="dataTableContent" align="center" width="200"><?php if ($orders['dyno_runone_hp'] == '') {
        echo 
    'N/A';
        } else {
        
        echo 
    $orders['dyno_runone_hp'] . ' HP '$orders['dyno_runone_tq'] . ' TQ';  
        
        }
        
    ?></td>
        <td  class="dataTableContent" align="center" width="200"><?php if ($orders['dyno_runtwo_hp'] == '') {
        echo 
    'N/A';
        } else {
        
        echo 
    $orders['dyno_runtwo_hp'] . ' HP '$orders['dyno_runtwo_tq'] . ' TQ';  
        
        }
    ?></td>
        <td  class="dataTableContent" align="center" width="200"><?php  if ($orders['dyno_runthree_hp'] == '') {
        echo 
    'N/A';
        } else {
        
        echo 
    $orders['dyno_runthree_hp'] . ' HP '$orders['dyno_runthree_tq'] . ' TQ';  
        
        }
    ?></td>
      </tr>
    </table>




    <?    /*    
                $dyno_schedule = tep_check_dyno_schedule($orders['orders_id']);

    $dyno_time = tep_check_dyno_time($orders['orders_id']);
    $dyno_time = strtotime("$dyno_time");
    $dyno_time = date('g:i a', $dyno_time);

      if ($dyno_schedule == '0') { 
       ?> 
       <?php
    }
    else 
    {
    echo $dyno_time; 
    }
    */
        
    $cat $orders['division'];
    }   

    ?>

    i can sort the information from the dyno_runs table into groups with headings based on division. What i need to do is then within the division groups is sort the listings on the highest number from any three of the columns

    dyno_runone_hp
    dyno_runtwo_hp
    dyno_runthree_hp

    i have tried a nested mysql query with Greatest but i get nothing but errors.


    Any help is appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    select *  
      from dyno_runs 
     order
        by division desc,
           greatest(dyno_runone_hp,dyno_runtwo_hp,dyno_runthree_hp)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    wow that was super simple not sure how i did not come up with that command.

    is there anyway to now highlight the cell with the highest number?
  6. #4
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    You would do that with your php code. you'd have to compare the numbers in each cell for each row when you are stepping through the output.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Be aware that this kind of table structure, in which tables are named ___one, ___two, ___three, etc is often indicative of poor design.

    See normalisation!

IMN logo majestic logo threadwatch logo seochat tools logo