Ordering Values in a Case when .. have a question

Discussion in 'PHP' started by rtblizz, Mar 22, 2012.

  1. #1
    I need to associate an increasing value to a row entry and I'm Stuck...

    Hears the code so far.. the original $zipcode is pulled in from a get action on a form and inserted in the query before the other values are looped in..

    
    
        $result2 = mysql_query($sqlstring2) or die('query failed: ' . mysql_error());
    
    while ($row = mysql_fetch_array($result2, MYSQL_ASSOC))
        {
            $strquery .= " ".$row["zipcode"].", ";
        }  
    
        $strquery = rtrim($strquery, ', ');
    
        $result3 = mysql_query($sqlstring2) or die('query failed: ' . mysql_error());
    
    while ($row = mysql_fetch_array($result3, MYSQL_ASSOC))
        {
            $orderquery .= "WHEN ZipCode = ".$row["zipcode"]." THEN 2 ";
        }  
    
    $query = "SELECT * FROM `table` WHERE `ZipCode` IN (".$zipcode.", ".$strquery.") order by Value, (CASE WHEN ZipCode = ".$zipcode." THEN 1 ".$orderquery." Else ZipCode End)";
    
    
    PHP:
    the resulting Query reads like this and is not breaking just not giving the desired results.

    
    
    $query = "SELECT * FROM `table` WHERE `ZipCode` IN (21108, 21146, 21032, 21123, 21144, 21113, 21061, 21054, 21062) order by Value
    (CASE WHEN ZipCode = 21108 THEN 1 WHEN ZipCode = 21146 THEN 2 WHEN ZipCode = 21032 THEN 2 WHEN ZipCode = 21123 THEN 2 WHEN ZipCode = 21144 THEN 2 WHEN ZipCode = 21113 THEN 2 WHEN ZipCode = 21061 THEN 2 WHEN ZipCode = 21054 THEN 2 WHEN ZipCode = 21062 THEN 2 Else ZipCode End)";
    
    
    PHP:
    its the Then 2 that is killing me.. I need it to say 3 then 4 then 5 etc... like this

    
    
    $query = "SELECT * FROM `table` WHERE `ZipCode` IN (21108, 21146, 21032, 21123, 21144, 21113, 21061, 21054, 21062) order by Value
    (CASE WHEN ZipCode = 21108 THEN 1 WHEN ZipCode = 21146 THEN 2 WHEN ZipCode = 21032 THEN 3 WHEN ZipCode = 21123 THEN 4 WHEN ZipCode = 21144 THEN 5 WHEN ZipCode = 21113 THEN 6 WHEN ZipCode = 21061 THEN 7 WHEN ZipCode = 21054 THEN 8 WHEN ZipCode = 21062 THEN 9 Else ZipCode End)";
    
    
    PHP:
    that code would work but I do not know how in this part of the code to make that happen?? hear is where my error is..

    
    
    while ($row = mysql_fetch_array($result3, MYSQL_ASSOC))
        {
            $orderquery .= "WHEN ZipCode = ".$row["zipcode"]." THEN 2 ";
        }  
    
    
    PHP:
    it need to be THEN "num +1" or something ????? AAAHHHHH... any help :) before my eyes burn out... :)Ordering Values in a Case when .. have a question
     
    rtblizz, Mar 22, 2012 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,899
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I get what you are trying to do but I'd do it this way
    
    [LEFT][COLOR=#0000FF][FONT=monospace]SELECT *,
    [/FONT][/COLOR][COLOR=#0000FF][FONT=monospace]   (CASE WHEN ZipCode = 21108 THEN 1 WHEN ZipCode = 21146 THEN 2 WHEN ZipCode = 21032 THEN 3 WHEN ZipCode = 21123 THEN 4 WHEN ZipCode = 21144 THEN 5 WHEN ZipCode = 21113 THEN 6 WHEN ZipCode = 21061 THEN 7 WHEN ZipCode = 21054 THEN 8 WHEN ZipCode = 21062 THEN 9 Else ZipCode End) as sortorder[/FONT][/COLOR][COLOR=#0000FF][FONT=monospace]
       FROM `table` 
       WHERE `ZipCode` IN (21108, 21146, 21032, 21123, 21144, 21113, 21061, 21054, 21062) 
       order by Value, sortorder[/FONT][/COLOR]
    Code (markup):



    [/LEFT]
    don't be afraid to add extra columns to the query if it simplifies the code and makes maintenance easier when you have to revisit the code in 6 months time.
     
    sarahk, Mar 24, 2012 IP