Ordering Values in a Case when .. have a question
Posted: Fri Mar 23, 2012 12:13 am
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..
the resulting Query reads like this and is not breaking just not giving the desired results.
its the Then 2 that is killing me.. I need it to say 3 then 4 then 5 etc... like this
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..
it need to be THEN "num +1" or something ????? AAAHHHHH... any help
before my eyes burn out... 
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)";
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)";
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)";
Code: Select all
while ($row = mysql_fetch_array($result3, MYSQL_ASSOC))
{
$orderquery .= "WHEN ZipCode = ".$row["zipcode"]." THEN 2 ";
}