Re: [PHP] Problem with SQL query

2002-07-09 Thread Richard Lynch

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




Re: [PHP] Problem with SQL query

2002-07-07 Thread Alberto Serra

ðÒÉ×ÅÔ!

 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

2002-07-07 Thread Alberto Serra

ðÒÉ×ÅÔ!

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

2002-07-07 Thread JJ Harrison

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

2002-07-07 Thread JJ Harrison

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

2002-07-07 Thread JJ Harrison

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

2002-07-07 Thread Mark Charette

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

2002-07-07 Thread Alberto Serra

ðÒÉ×ÅÔ!

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