Page 1 of 1

Ordering Values in a Case when .. have a question

Posted: Fri Mar 23, 2012 12:13 am
by rtblizz
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..

Code: Select all


    $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)";

the resulting Query reads like this and is not breaking just not giving the desired results.

Code: Select all


$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)";

its the Then 2 that is killing me.. I need it to say 3 then 4 then 5 etc... like this

Code: Select all


$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)";

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..

Code: Select all


while ($row = mysql_fetch_array($result3, MYSQL_ASSOC))
    {
		$orderquery .= "WHEN ZipCode = ".$row["zipcode"]." THEN 2 ";
    }  

it need to be THEN "num +1" or something ????? AAAHHHHH... any help :) before my eyes burn out... :)

Re: Ordering Values in a Case when .. have a question

Posted: Fri Mar 23, 2012 3:23 pm
by TonsOfFun
I am not completely sure this will work, but it's worth a shot.

Code: Select all

$x = 2;
while ($row = mysql_fetch_array($result3, MYSQL_ASSOC))
    {
                $orderquery .= "WHEN ZipCode = ".$row["zipcode"]." THEN ". $x;
                $x++;
    }  

 
That will start $x at 2, then increase by one every time the "while" loops.