RE: [PHP-DB] MySQL Problem

the statement runs without errors, its just the output thats wrong (see the two tables) I'm displaying the results by doing a simple dump of the query, through my own code and have also tried through phpMyAdmin. -----Original Message----- From: Michael Hazelden [mailto:michael.hazelden@ocado.com] Sent: 21 November 2002 16:41 To: Griffiths, Daniel; PHP List Subject: RE: [PHP-DB] MySQL Problem Maybe I'm missing something - but the MySQL statement looks right ... How are you displaying the results? -----Original Message----- From: Griffiths, Daniel [mailto:DANIEL.GRIFFITHS@conconf.org] Sent: 21 November 2002 16:37 To: PHP List Subject: [PHP-DB] MySQL Problem Hi, I have a problem with an MySQL statement that is driving me mad, I'm using php to connect to the database. this is it : - SELECT LINE,SUM(IF(MONTH=2,WB.TEU,0)) AS '2' ,SUM(IF(MONTH=3,WB.TEU,0)) AS '3' ,SUM(IF(MONTH=4,WB.TEU,0)) AS '4' ,SUM(IF(MONTH=5,WB.TEU,0)) AS '5' ,SUM(IF(MONTH=6,WB.TEU,0)) AS '6' ,SUM(IF(MONTH BETWEEN 2 AND 6,WB.TEU,0)) AS '2 TO 6' FROM WB WHERE EAST_PORT='A' AND WEST_PORT='B' GROUP BY LINE what I want to produce with this is this : - LINE 2 3 4 5 6 2 TO 6 A 1335 1118 1260 1395 1276 6384 B 432 469 451 480 555 2387 C 504 554 575 517 764 2914 however what I get is this : - LINE 2 3 4 5 6 2 TO 6 A 1118 1260 1395 1276 6384 6384 B 469 451 480 555 2387 2387 C 554 575 517 764 2914 2914 the last column of figures (2 TO 6) has been replicated under 6 and pushed the values that were in 6 into 5 and so on! I can not see for the life of me why this should or could be happening, especially as the equivalent code in MS Access : - SELECT LINE, Sum(IIf(MONTH=2,TEU,0)) AS ['2'], Sum(IIf(MONTH=3,TEU,0)) AS ['3'], Sum(IIf(MONTH=4,TEU,0)) AS ['4'], Sum(IIf(MONTH=5,TEU,0)) AS ['5'], Sum(IIf(MONTH=6,TEU,0)) AS ['6'], SUM(IIF(MONTH BETWEEN 2 AND 6,TEU,0)) AS 'FEB TO JUNE' , SUM(IIF(MONTH BETWEEN 1 AND 6,TEU,0)) AS 'YTD' FROM DUMP WHERE (((DUMP.[EAST PORT])='HONG KONG') AND ((DUMP.[WEST PORT])='UK')) GROUP BY DUMP.LINE; gives the correct results. If anyone has any idea of what is going on here please let me know, I'm hoping its something simple I'm missing.. Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _____________________________________________________________________ This message has been checked for all known viruses by the MessageLabs Virus Control Centre. This message has been checked for all known viruses by the MessageLabs Virus Control Centre. ********************************************************************* Notice: This email is confidential and may contain copyright material of Ocado Limited (the "Company"). Opinions and views expressed in this message may not necessarily reflect the opinions and views of the Company. If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. Company reg. no. 3875000. Swallowdale Lane, Hemel Hempstead HP2 7PY *********************************************************************