Re: [PHP] Problem with SQL query
LIMIT was not included in the SQL92 SQL standards and very few vendors implement all of SQL99; the use of ANSI standards to promote portable programs has always been beset by this kind of problems. Wow! There's actually an SQL99 that vendors are targeting, kinda sorta? Hey, with any luck, but 2009, they'll all be SQL99-compliant, and we can start all over! Not that they ever all reached SQL92 compliance. Oh, just in case people don't know. SQL92 is named SQL92 because it was defined in... [drum roll] 1992 That's 100 years ago in doggie years :-) Consider yourself lucky if any of your SQL is really portable without jumping through hoops. I do want to apologize for my month_view/monthly_views typo. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Problem with SQL query
I can't figure out what is wrong with this: $query = select count(*) as monthly_views from visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1; $result = mysql_query($query); echo $query; $row = mysql_fetch_array($result); echo $row['monthly_views']; echo mysql_error(); I get this: select count(*) as monthly_views from visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 Warning: Supplied argument is not a valid MySQL result resource in C:\Inetpub\TecEco_PHP\stats_interface\summary.php on line 76 You have an error in your SQL syntax near ''year', time), extract('month', time) order by monthly_view desc limit 1' at line 1 I don't know any advanced SQL so I don't know how to debug this. I am using mySQL. The person who gave me this code appeared to be using postgreSQL if that makes any differance -- JJ Harrison [EMAIL PROTECTED] www.tececo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
ðÒÉ×ÅÔ! I can't figure out what is wrong with this: $query = select count(*) as monthly_views from visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1; AFAIK this is no ANSI SQL, which is why is not portable. Your query should really be SELECT extract('year', time), extract('month', time) count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 *BUT* extract doesn't seem to be a MYSQL function, you most probably need to use DATE_FORMAT. Look in the Mysql docs for this. As a general ANSI rule group by functions *need* the fields on which result is grouped to appear *first* in the query. Take half an hour to look at the GROUP BY docs in MySQL online manual, everything will become much clearer. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -- -_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_- LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
ðÒÉ×ÅÔ! I forgot to add: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 I take it that you have a *monthly_view* column in your table that is not the *monthly_views* alias yoiu use in your query. if that's a typo and you mean to order by the numebr of visitors (that is, you want the most visited month on top of the result) your query should probably look like: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by 3 desc limit 1 That's because most databases (and I guess MySql is no exception) will not be able to use an alias in their GROUP BY, ORDER BY clauses. But you may want to try, maybe MySql *is* an exception, after all. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -- -_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_- LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
Thanks for giving me something to look up :) -- JJ Harrison [EMAIL PROTECTED] www.tececo.com Alberto Serra [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... ðÒÉ×ÅÔ! I forgot to add: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 I take it that you have a *monthly_view* column in your table that is not the *monthly_views* alias yoiu use in your query. if that's a typo and you mean to order by the numebr of visitors (that is, you want the most visited month on top of the result) your query should probably look like: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by 3 desc limit 1 That's because most databases (and I guess MySql is no exception) will not be able to use an alias in their GROUP BY, ORDER BY clauses. But you may want to try, maybe MySql *is* an exception, after all. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -- @-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@ LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
No matter what I try I still seem to get the error message. The coloumn that contains the dates has unix timestamps. I think this is why it is not working. I will research it further. If you can be of any help please tell me. -- JJ Harrison [EMAIL PROTECTED] www.tececo.com Alberto Serra [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... ðÒÉ×ÅÔ! I forgot to add: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 I take it that you have a *monthly_view* column in your table that is not the *monthly_views* alias yoiu use in your query. if that's a typo and you mean to order by the numebr of visitors (that is, you want the most visited month on top of the result) your query should probably look like: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by 3 desc limit 1 That's because most databases (and I guess MySql is no exception) will not be able to use an alias in their GROUP BY, ORDER BY clauses. But you may want to try, maybe MySql *is* an exception, after all. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -- @-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@ LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
I have fixed it now don't worry! -- JJ Harrison [EMAIL PROTECTED] www.tececo.com Alberto Serra [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... ðÒÉ×ÅÔ! I forgot to add: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 I take it that you have a *monthly_view* column in your table that is not the *monthly_views* alias yoiu use in your query. if that's a typo and you mean to order by the numebr of visitors (that is, you want the most visited month on top of the result) your query should probably look like: SELECT extract('year', time), extract('month', time), count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by 3 desc limit 1 That's because most databases (and I guess MySql is no exception) will not be able to use an alias in their GROUP BY, ORDER BY clauses. But you may want to try, maybe MySql *is* an exception, after all. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -- @-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@ LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Problem with SQL query
LIMIT was not included in the SQL92 SQL standards and very few vendors implement all of SQL99; the use of ANSI standards to promote portable programs has always been beset by this kind of problems. Mark Charette Former ANSI X3H3.1 member -Original Message- From: Alberto Serra [mailto:[EMAIL PROTECTED]] SELECT extract('year', time), extract('month', time) count(*) as monthly_views FROM visitors group by extract('year', time), extract('month', time) order by monthly_view desc limit 1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
ðÒÉ×ÅÔ! Mark Charette wrote: LIMIT was not included in the SQL92 SQL standards and very few vendors implement all of SQL99; the use of ANSI standards to promote portable programs has always been beset by this kind of problems. Yes, and vendors just love to have proprietary standards to protect their market shares. The basic idea is that since you cannot just switch from an engine to another without seriously risking your application stability you will tolerate the yes, it's a known bug answer, whenever your 100 thousand dollars application cannot print a simple data report because trying to set fonts size will crash the current job. Or when real numbers are returned with a different values from the one you wrote in. Not talking about MeAndMyFriendJoe'sXperimentalSQLMachineGun 0.0, That was Oracle 7.3 with Developer (fonts) and Oracle 8.something under WinNT (floating real number values). Eventually they solved both the problems (I have to say they even did it quick) but you can imagine the atmosphere when the final customer had to be told that they had invested an overall amount of 25k$ a day for 2 years just not to be able to print a common report and that Oracle just answered yes, it's a known bug - bug precedence level: low. Which actually meant: go ** yourselves, we ain't got no time for your stupid customers. If only they could switch engine... But they switched to their lawyers in instead and kept the engine running, because no ANSI was there (and because we all knew that no better stability was to be found on other vendors anyway). Some things in escaping the ANSI standard are useful, though. Things like Oracle's DECODE and the LIMIT clause do make query sets smaller and quicker. And yet, IMHO most of the opposition to ANSI comes from a mere commercial point of view. This way vendors can keep releasing poor alpha stuff and call it a stable release without having to worry about spending test money. Test is something you are going to do yourself, paying for it with your own money and your own professional credibility. No wonder vendors are happy with it. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -- -_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_- LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php