Query inconsistency (MySQL vs PHP)

[svein.tjonndal]

I have the following query:

select DATE_FORMAT(accountingdate,"%c") as month,sum(totalprice -
freightcost - insurancecost - vat2 - vat3 - vat4) as totalprice from
invoice where cancelled=0 and deliveryagentid=0 and (employeeid=0 or
employeeid=37 or employeeid=53 or employeeid=50) and
DATE_FORMAT(accountingdate,"%Y")="2005" group by month order by month;

Giving the following result in MySQL (correctly):
+--------+--------------+
| month | totalprice |
+--------+--------------+
| 1 | 540274351.00 |
| 10 | 119601657.00 |
| 11 | 118712994.00 |
| 12 | 109391926.00 |
| 2 | 112606305.00 |
| 3 | 99175084.00 |
| 4 | 101754796.00 |
| 5 | 120049120.00 |
| 6 | 138227787.00 |
| 7 | 166565653.00 |
| 8 | 136475650.00 |
| 9 | 133242379.00 |
+--------+--------------+
When I run the same query in PHP (on a web site, I get):

4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00

Why is this? What can be done to solve it?

Any help or pointers welcome

[Oli Filth]

sv************@gmail.com said the following on 29/12/2005 00:01:

I have the following query:

select DATE_FORMAT(accountingdate,"%c") as month,sum(totalprice -
freightcost - insurancecost - vat2 - vat3 - vat4) as totalprice from
invoice where cancelled=0 and deliveryagentid=0 and (employeeid=0 or
employeeid=37 or employeeid=53 or employeeid=50) and
DATE_FORMAT(accountingdate,"%Y")="2005" group by month order by month;

First off, if you want to make life easier for people trying to help you
here, please use standard capitalisation for your query string so that
it's easier to read (indentation wouldn't go amiss either), e.g.:

SELECT DATE_FORMAT(accountingdate, "%c") AS month,
SUM(totalprice - freightcost - insurancecost
- vat2 - vat3 - vat4)
AS totalprice
FROM invoice
WHERE cancelled = 0 AND deliveryagentid = 0 AND
(employeeid = 0 OR employeeid = 37 OR
employeeid = 53 OR employeeid = 50) AND
DATE_FORMAT(accountingdate, "%Y") = "2005"
GROUP BY month
ORDER BY month;

is *so* much easier to read.

Please read the documentation for mysql_query() in the PHP manual: "The query string should not end with a semicolon."

(http://php.net/mysql_query)

Note also that using DATE_FORMAT(...) (which returns a string) as your
sort criterion actually means that it performs a string sort, and hence
sorts incorrectly - investigate the MONTH() function, which returns an
integer, and will then sort correctly.

(Note also that if you have "GROUP BY month", you don't need the "ORDER
BY month" as MySQL does it automatically).

Which brings me to another point - as MONTH is a MySQL function name, if
you have a field with the same name, you should escape it with
back-ticks, i.e. `month`. Best practice is not to use MySQL keywords as
field names!
Fix these things, and if it still doesn't work, then post again!
--
Oli

[svein.tjonndal]

Thanks Oli,

What you point out is true but doesn't help. I am looking for some sort
of documentation or help on what I can only assume to be some sort of
caching of MySQL queries when running them from Apache/PHP. That or a
serious bug of some sort.

(The semi-colon is there because I copied it from a MySQL query viewer.
DATE_FORMAT works fine, it's the viewer that sorts the month that way.
Points taken on month as a keyword and better presentation of the
query.)

Svein

[Juliette]

sv************@gmail.com wrote:

I have the following query:

select DATE_FORMAT(accountingdate,"%c") as month,sum(totalprice -
freightcost - insurancecost - vat2 - vat3 - vat4) as totalprice from
invoice where cancelled=0 and deliveryagentid=0 and (employeeid=0 or
employeeid=37 or employeeid=53 or employeeid=50) and
DATE_FORMAT(accountingdate,"%Y")="2005" group by month order by month;

Giving the following result in MySQL (correctly):
+--------+--------------+
| month | totalprice |
+--------+--------------+
| 1 | 540274351.00 |
| 10 | 119601657.00 |
| 11 | 118712994.00 |
| 12 | 109391926.00 |
| 2 | 112606305.00 |
| 3 | 99175084.00 |
| 4 | 101754796.00 |
| 5 | 120049120.00 |
| 6 | 138227787.00 |
| 7 | 166565653.00 |
| 8 | 136475650.00 |
| 9 | 133242379.00 |
+--------+--------------+
When I run the same query in PHP (on a web site, I get):

4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00

Why is this? What can be done to solve it?

Any help or pointers welcome

Can you give us the php code for getting the query results and
generating the output ? Your error might well be the problem.

[svein.tjonndal]

Solved by closing the MySQL connection and opening a new one before the
query in question.