I have the following query:
select DATE_FORMAT(acc ountingdate,"%c ") as month,sum(total price -
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(acc ountingdate,"%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 4 1568 sv************@ gmail.com said the following on 29/12/2005 00:01: I have the following query:
select DATE_FORMAT(acc ountingdate,"%c ") as month,sum(total price - 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(acc ountingdate,"%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(acc ountingdate, "%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(acc ountingdate, "%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
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 sv************@ gmail.com wrote: I have the following query:
select DATE_FORMAT(acc ountingdate,"%c ") as month,sum(total price - 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(acc ountingdate,"%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.
Solved by closing the MySQL connection and opening a new one before the
query in question. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jan Nordgreen |
last post by:
I use php4 and winxp.
This query works as expected:
$result = mysql_query("
SELECT feventid, UNIX_TIMESTAMP(fdate) as fdate, ftitle, fpostedby,
fdetails, factive, UNIX_TIMESTAMP(fpostdate) as fpostdate
FROM events
WHERE
fdate between '$datefrom' and '$dateto'
|
by: 21novembre |
last post by:
Hi all,
I'm working on my first php+mysql program.
I have a mysqld running and there's a DB named "example" with a table
"tbl". Here it is:
--------------
mysql> use example;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
|
by: Willem Berendsen |
last post by:
Hello,
I setting up my mysql-server and php on IIS6 and it works almost fine.
But I got the next error:
Warning: mysql_numrows(): supplied argument is not a valid MySQL result
resource in file.php on line 46.
But I'am sure that the code is good, because I have use the code very much.
Create a connection is working, mysql_query is working but
mysql_Numrows($query) is not working:
$query = mysql_query("SELECT * FROM users");
|
by: Daz |
last post by:
Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.
My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides...
|
by: laverdir |
last post by:
<?
$query = "SELECT " . $this->tabela3 . ".id, " . $this->tabela3 .
".naziv, " . $this->tabela3 . ".spisatelj, " . $this->tabela3 .
".najava, " . " DATE_FORMAT(" . $this->tabela3 . ".vrijeme_stavljanja,
'%e. %c. %Y. %k:%i') FROM " . $this->tabela3 . ", " . $this->tabela2 .
" AS xref WHERE " . $this->tabela3 . ".id=xref.spis_id AND
xref.vrsta_id=" . $this->DB->quote($vrsta) . " ORDER BY " .
$this->tabela3 . ".vrijeme_stavljanja DESC";
?>
| |
by: Flic |
last post by:
Hi,
I have a basic db that I access with MySQL query browser. Everything
seems fine to me but I am using this db as part of a php shopping
basket and when I try to add an item I get:
Notice: Query failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '>function.extract]: First argument should be an
array in functions.inc.php on line 31
|
by: kumarboston |
last post by:
Hi All,
I have a mysql database and 3 pages which queries and returns the data.
1st page(main.html) is just a form in html which takes query from the user and is connected to "form action="index.php"".
2nd page(index.php) has the mysql queries and gives a brief information(one line) about the user query. Now the index.php has a hyperlink to the third page(summary.php) which gives detailed information about the user query.
3rd...
|
by: alf8kitty |
last post by:
Hello,
Im still very new to php and am having a problem.
I return a MySQL query to a form in my php page and I want to be able to export the form data to Excel when the users clicks a link (or a button).
I found some code and can apparently export the entire contents of a table in my database, but when i try to limit the export result using variables...i get nothing. I am hoping someone can point me in the right direction?
This is the...
|
by: lisles |
last post by:
i have a page funtion.php which hs the function to connect to the db
/* Mysql Connection */
function connect(){
global $db_server,$db_user,$db_pass,$db;//Global Values from the config.php
$conn = @mysql_connect($db_server,$db_user,$db_pass) or die("Connection to Database Server Failed");
@mysql_select_db($db) or die("Database Selection Failed");
return $conn;
}
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |