need help Ordering Values in a Case

General PHP Help
#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…

[php]Syntax: [ Download ] [ Hide ] [ Select ]

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

[php]Syntax: [ Download ] [ Hide ] [ Select ]

$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

[php]Syntax: [ Download ] [ Hide ] [ Select ]

$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…

[php]Syntax: [ Download ] [ Hide ] [ Select ]

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, maybe create a value association to the rows and supplt that var to the THEN # ??? AAAHHHHH… any help :slight_smile: before my eyes burn out… :-X

#2

Hmmm??? Okay, your SQL:

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

So, the SELECT is pulling all data from table where the ZipCode is in your list.
Then, you ORDER by a value that is pulled from a case and equals 2 if the ZipCode is in the list.
*** Doesn’t make sense to me!!! ***
SO, you are wanted this query to be ORDER by 2??? Is the data you are pulling able to be sorted by 2?

What do you really want to do with this query? Do you want to sort by zipcode or 2?

#3

this is my Query in Question: the value for zipcode is the first zip being pulled from the Get of the Form that brought me to this page after that there are 2 variables

[ul][li]stquery: that pulls in the zips related to the get zip in a certain order separated by a ,[/li]
[li]orderquery: that pulls in the same zips in the same order but being seperated by a THEN number.[/li][/ul]

I need that number to be ascending or adding one for each row so it reads 2, 3, 4, 5 etc and not 2, 2, 2, 2…
I do not know how to do that?

[php]

$query = “SELECT * FROM table WHERE ZipCode IN (”.$zipcode.", “.$strquery.”) order by Value, (CASE WHEN ZipCode = “.$zipcode.” THEN 1 “.$orderquery.” Else ZipCode End)";

[/php]

My Goal is to use the zip code values being pulled from [php]".$strquery."[/php] and use them for ordering in that order if I use the same variable in the order by field I can only order by ascending or descending I want to order in the exact order of the zipcode…

To do that I am using a order by CASE

the case works like this

[php]

(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]

I can not get it to do that because I do not know how to write the code to make that happen. What I have is this which references the zip code results for the WHERE.

[php]

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

[/php]

I do not know how to make the THEN 2 become Then 3, Then 4, Then 5 etc… for every zipcode value pulled. MY result is repeating THEN 2 which does not work. The only way for it to work is create something that numerically adds +1 to the value after THEN or some other way that I don’t know… That’s why I need help…

#4

Well, you might try a SQL forum for this question instead of a PHP forum, but, it sounds to me like a sorting issue not a SQL issue.

SQL is used for pulling the data from the database, and ordering (sorting) it. It is not meant to be used to alter values while pulling data. Normally, you would pull all the data into a PHP array and then, do your thing.
PHP even has routines for sorting based on a row or col in an array. It takes only a few lines to read an entire table into an array. The steps needed to sort or order the data would take a little thinking, but, would be much better than doing it all inside the SQL.

Now that I said all that, here is a link which might do what you want using GROUP-BY. It may let you create a temporary column in the table that is listed as you want…
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

Sponsor our Newsletter | Privacy Policy | Terms of Service