Page 1 of 1

Order by specified Array?

Posted: Thu Jul 03, 2008 6:32 am
by JimiH
Hi I have this code which returns 12 results, I would like them ordered by a specified Array, ie 10/2007, 11/2007, 12/2007, 01/2008 etc etc
at the moment they are ordered like below.

33 1/2008
30 10/2007
28 11/2007
22 12/2007
30 2/2008
35 3/2008
35 4/2008
23 5/2008
27 6/2008
31 8/2007
38 9/2007

Code: Select all

$conn=odbc_connect('DB','user','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
 
$array = array('10/2007', '11/2007', '12/2007', '1/2008', '2/2008', '3/2008', '4/2008', '5/2008', '6/2008', '7/2008', '8/2008', '9/2008');
print_r($array);
 
 
$sql="SELECT Count([ECNs by month].ECN_Number) AS CountOfECN_Number, [ECNs by month].thedate
FROM [ECNs by month]
GROUP BY [ECNs by month].thedate, [ECNs by month].Location
HAVING ((([ECNs by month].Location)='ASCO Joucomatic Ltd.'))
ORDER BY [ECNs by month].thedate";
 
 
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th>ECN Number Count</th>";
echo "<th>Date</th>";    
 
while (odbc_fetch_row($rs))
{
  $compname=odbc_result($rs,"CountOfECN_Number");
  $conname=odbc_result($rs,"thedate");
  
  echo "<tr><td>$compname</td>";
  echo "<td>$conname</td>";
  
}
odbc_close($conn);
echo "</table>";

Re: Order by specified Array?

Posted: Thu Jul 03, 2008 11:18 am
by Christopher
You will probably need to split the values on the slash to sort them. It would be much easier if the dates were like this:

Code: Select all

# $array = array('2007-01', '2007-11', '2007-12', );
Then they would be trivial to sort. You could always convert back to some display format.

Re: Order by specified Array?

Posted: Fri Jul 04, 2008 8:57 am
by JimiH
Thanks Chris

I managed to generate the require sort order within SQL using ms access first now I need to convert the
sql used by access, there are difference between the two, IIF in access and CASE in mssql.

Code: Select all

SELECT Count([dbo_ECNs by month].ECN_Number) AS CountOfECN_Number, [dbo_ECNs by month].thedate, IIf([Thedate]="10/2007",1,IIf([Thedate]="11/2007",2,IIf([Thedate]="12/2007",3,IIf([Thedate]="1/2008",4,IIf([Thedate]="2/2008",5,IIf([Thedate]="3/2008",6,IIf([Thedate]="4/2008",7,IIf([Thedate]="5/2008",8,IIf([Thedate]="6/2008",9,IIf([Thedate]="7/2008",10,IIf([Thedate]="8/2008",11,IIf([Thedate]="9/2008",12)))))))))))) AS Expr1
FROM [dbo_ECNs by month]
GROUP BY [dbo_ECNs by month].thedate, IIf([Thedate]="10/2007",1,IIf([Thedate]="11/2007",2,IIf([Thedate]="12/2007",3,IIf([Thedate]="1/2008",4,IIf([Thedate]="2/2008",5,IIf([Thedate]="3/2008",6,IIf([Thedate]="4/2008",7,IIf([Thedate]="5/2008",8,IIf([Thedate]="6/2008",9,IIf([Thedate]="7/2008",10,IIf([Thedate]="8/2008",11,IIf([Thedate]="9/2008",12)))))))))))), [dbo_ECNs by month].Location
HAVING (((IIf([Thedate]="10/2007",1,IIf([Thedate]="11/2007",2,IIf([Thedate]="12/2007",3,IIf([Thedate]="1/2008",4,IIf([Thedate]="2/2008",5,IIf([Thedate]="3/2008",6,IIf([Thedate]="4/2008",7,IIf([Thedate]="5/2008",8,IIf([Thedate]="6/2008",9,IIf([Thedate]="7/2008",10,IIf([Thedate]="8/2008",11,IIf([Thedate]="9/2008",12))))))))))))) Is Not Null) AND (([dbo_ECNs by month].Location)="ASCO Joucomatic Ltd."))
ORDER BY IIf([Thedate]="10/2007",1,IIf([Thedate]="11/2007",2,IIf([Thedate]="12/2007",3,IIf([Thedate]="1/2008",4,IIf([Thedate]="2/2008",5,IIf([Thedate]="3/2008",6,IIf([Thedate]="4/2008",7,IIf([Thedate]="5/2008",8,IIf([Thedate]="6/2008",9,IIf([Thedate]="7/2008",10,IIf([Thedate]="8/2008",11,IIf([Thedate]="9/2008",12))))))))))));
Thanks

Geoff