RE: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
-Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: 05 August 2009 01:41 Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY `uniqueDate`; That's how I'd write it, anyway. Cheers! Mike -- Mike Ford, Electronic Information Developer, Libraries and Learning Innovation, Leeds Metropolitan University, C507, Civic Quarter Campus, Woodhouse Lane, LEEDS, LS1 3HE, United Kingdom Email: m.f...@leedsmet.ac.uk Tel: +44 113 812 4730 To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: ... That's how I'd write it, anyway. I like to know your thinking; I'm working to learn to 'think in MySQL'.. so every input is appreciated. thanks! -G -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Me/this again. this works good: $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62; except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? You should only need one ORDER BY clause at the end of the whole query: (SELECT...) UNION ALL (SELECT...) UNION ALL (SELECT...) ORDER BY... I'm not sure if this syntax is portable to other SQL-based DBMSes, though. I'm certain that one system we use at work doesn't support it, but it's kind of a dinosaur so I don't like to draw too many conclusions from what it doesn't support. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Ford, Mike wrote: -Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: 05 August 2009 01:41 Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY `uniqueDate`; That's a mysqlism :( It's not portable to other db's (apparently it's not part of the sql-spec). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: [8] That's a mysqlism :( It's not portable to other db's (apparently it's not part of the sql-spec). I think I've even seen MySQL reject it in some cases. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 Ben, Chris, This is as far as I could get so far: $query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing UNION ALL SELECT count(*) AS confirm_count, date(solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw; $foundUniqueDateROWS = mysql_query($query) or die(query failed: .mysql_error()); giving this error: query failed: Unknown column 'solarLandingDateTime' in 'field list' but I most certainly do have a column named 'solarLandingDateTime' in the table named 't7solar_landing'. So I am not sure what it's unhappy. ? John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Niel, thanks (I was wondering about that... thought it seemed odd
without a FROM clause)
Now I have this:
$query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime)
AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM
t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT
count(*) AS confirm_count, date(solarAweberConfDateTime) AS
solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS
aweber_7solar_confirm FROM aweber_7solar_confirm GROUP BY
DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
thankyou_count, date(solarAWDateTime) AS solarAWDate,
'aweber_7solar_awALIAS' AS aweber_7solar_aw FROM aweber_7solar_aw
GROUP BY DATE(solarAWDateTime) ;
$foundUniqueDateROWS = mysql_query($query) or die(query failed:
.mysql_error());
while ($uniqueDateROW = mysql_fetch_assoc($foundUniqueDateROWS)) {
echo tr
td.htmlentities($uniqueDateROW-solarLandingDate)/td
td.htmlentities($uniqueDateROW-landing_count)./td
td.htmlentities($uniqueDateROW-thankyou_count)./td
td.(some math coming)./td
td.htmlentities($uniqueDateROW-confirm_count)./td
td.(some math coming)./td
td.(some math coming)./td
/tr;
}
Which seems to be outputting (I assume) the correct number of records
(one for each unique date in each of the 3 tables, combined).
But those table cells are coming out empty; i.e.
$uniqueDateROW-solarLandingDate
and
$uniqueDateROW-landing_count
are returning nothing.
Can someone point me to understand why? I thought that:
SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS
solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM
t7solar_landing GROUP BY DATE(solarLandingDateTime)
would, among other thing, assign the number of records counted in that
first table to the alias 'landing_count'. No?
I know it is annoying to hand-hold when newbies are this clueless.
It's just hard for me to even know where to study when there are many
blanks in my understanding. I am going to systematically plod through
my books once I get them here. Meanwhile thanks to all who make time
to help!
-Govinda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Can someone point me to understand why? I thought that: SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) would, among other thing, assign the number of records counted in that first table to the alias 'landing_count'. No? Yes, but when you UNION that query with others it gets a bit more complicated. The UNION keyword adds more rows to the result-set, not more columns. So, when you join queries with a UNION, corresponding columns in each of those queries should all have the same aliases. So you probably don't want to say: SELECT nameABC AS `name1` FROM table1 UNION ALL SELECT nameDEF AS `name2` FROM table2 Here you're asking the DBMS to give you a result-set with just one column, in which the column is called `name1` some of the time, and `name2` the rest of the time. Doesn't make much sense, and MySQL will silently ignore one or the other of these aliases. Instead, say: SELECT nameABC AS `name` FROM table1 UNION ALL SELECT nameDEF AS `name` FROM table2 This will produce a result-set with just one column, called `name`. The number of rows in the result-set will equal the number of rows produced by the first SELECT, plus the number of rows produced by the second SELECT. Does that help make sense of why you need to add a second column to each query, with the name of the table? Like so: SELECT nameABC AS `name`, 'table1' AS `table_name` FROM table1 UNION ALL SELECT nameDEF AS `name`, 'table2' AS `table_name` FROM table2 This query will produce a result-set with two columns. The first column will be called `name` and the second will be called `table_name`; for example, supposing that table1 contains only boys' names and table2 contains only girls' names, you might get a result-set that includes these rows: name| table_name Robert | table1 James | table1 Lucy| table2 Teresa | table2 Then for each row, you would need to examine the value of the `table_name` column in PHP, to figure out which table the name is from. It looks like your current code is operating as though each row contains results from all three of your tables, which it doesn't. Each row only contains a result from one table. BTW, mysql_fetch_assoc() returns an array, not an object, so you'd need to use this syntax: $row['column'] As opposed to: $row-column If you prefer the latter syntax, you can use mysql_fetch_object(). Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Yes, but when you UNION that query with others it gets a bit more
complicated
...
If you prefer the latter syntax, you can use mysql_fetch_object().
Ben
Bless you Ben!!
That last post of yours led to my first decent dose of real
significant SQL-syntax understanding since I started writing more than
the *simplest* of SQL statements which I had gleaned from an intro PHP
book. Your time will not go to waste! Thank you!
I got it performing as expected. But there is one remaining thing
that is mysterious to me. It will be easiest to explain/ask by
showing my code and the result:
this:
-
$query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime)
UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
`CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
DATE(solarAWDateTime) ;
while { ...
echo htmlentities($uniqueDateROW['uniqueDate']).
.htmlentities($uniqueDateROW['CountUniqueDatesInMyTbl']).
.htmlentities($uniqueDateROW['tableAlias']).br /\n;
-
returns this:
2009-07-28 5 t7solar_landing
2009-07-29 1 t7solar_landing
2009-08-02 2 t7solar_landing
2009-08-03 3 t7solar_landing
2009-08-04 2 t7solar_landing
2009-07-28 2 aweber_7solar_confirm
2009-07-29 1 aweber_7solar_confirm
2009-07-30 1 aweber_7solar_confirm
2009-07-31 1 aweber_7solar_confirm
2009-08-01 2 aweber_7solar_confirm
2009-08-02 1 aweber_7solar_confirm
2009-08-03 2 aweber_7solar_confirm
2009-08-04 1 aweber_7solar_confirm
2009-07-29 1 aweber_7solar_aw
2009-07-30 1 aweber_7solar_aw
2009-07-31 1 aweber_7solar_aw
2009-08-01 2 aweber_7solar_aw
2009-08-02 1 aweber_7solar_aw
2009-08-03 2 aweber_7solar_aw
2009-08-04 1 aweber_7solar_aw
Now the first (date) and last (table) column are as expected. But
what happened to count(*)??
There ARE 5 unique dates represented in the first table, as the
highest returned value for count(*) shows, but as you can see, there
are 8 unique dates in the second table, and 7 unique dates in the last
table. Why then do we see only 1s and 2s in those columns for
the latter 2 tables? And why does NOT the first table's rows include
exclusively ONLY the values 1, 2, 3, 4, 5? I see two 2s
and no 4. (??)
I'm sure I can hack PHP in that while loop to generate the right count
of unique dates for each table.. but I want to take advantage of this
thread up to now and learn more SQL. The docs say count() Return[s]
a count of the number of rows returned. In relation to that
definition, I don't know what I am looking at in the results I got
above.
Can you 'splain me?
John Butler (Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Aug 4, 2009, at 6:21 PM, Chris dmag...@gmail.com wrote: Govinda wrote: I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 Ben, Chris, This is as far as I could get so far: $query = SELECT COUNT(*) AS landing_count, date (solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing UNION ALL SELECT count(*) AS confirm_count, date (solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw; $foundUniqueDateROWS = mysql_query($query) or die(query failed: .mysql_error()); giving this error: query failed: Unknown column 'solarLandingDateTime' in 'field list' but I most certainly do have a column named 'solarLandingDateTime' in the table named 't7solar_landing'. So I am not sure what it's unhappy. With a union the columns have to have the same name (or alias). Eg: SELECT COUNT(*) AS count, date(solarLandingDateTime) AS unique_date, 't7solar_landingALIAS' AS origin FROM tablename UNION ALL SELECT count(*) AS count, date(solarAweberConfDateTime) AS unique_date, 'aweber_7solar_confirmALIAS' AS origin FROM tablename etc -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Correction the column names will come form the first query, but they need to be the same datatype in those columns. So if the first query has a date datatype, the subsequent queries must have date columns in the first position Bastien Sent from my iPod -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
this: - $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) ; returns this: 2009-07-28 5 t7solar_landing 2009-07-29 1 t7solar_landing 2009-08-02 2 t7solar_landing 2009-08-03 3 t7solar_landing 2009-08-04 2 t7solar_landing 2009-07-28 2 aweber_7solar_confirm 2009-07-29 1 aweber_7solar_confirm 2009-07-30 1 aweber_7solar_confirm 2009-07-31 1 aweber_7solar_confirm 2009-08-01 2 aweber_7solar_confirm 2009-08-02 1 aweber_7solar_confirm 2009-08-03 2 aweber_7solar_confirm 2009-08-04 1 aweber_7solar_confirm 2009-07-29 1 aweber_7solar_aw 2009-07-30 1 aweber_7solar_aw 2009-07-31 1 aweber_7solar_aw 2009-08-01 2 aweber_7solar_aw 2009-08-02 1 aweber_7solar_aw 2009-08-03 2 aweber_7solar_aw 2009-08-04 1 aweber_7solar_aw Now the first (date) and last (table) column are as expected. But what happened to count(*)?? There ARE 5 unique dates represented in the first table, as the highest returned value for count(*) shows, but as you can see, there are 8 unique dates in the second table, and 7 unique dates in the last table. Why then do we see only 1s and 2s in those columns for the latter 2 tables? And why does NOT the first table's rows include exclusively ONLY the values 1, 2, 3, 4, 5? I see two 2s and no 4. (??) Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Your query says: get the date() from the solarAWDateTime field then group by that date and give me the date and number of results ie - you asked for the number of results per date - which is what you got. You didn't ask for the number of unique dates. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
With a union the columns have to have the same name (or alias). Eg: SELECT COUNT(*) AS count, date(solarLandingDateTime) AS unique_date, 't7solar_landingALIAS' AS origin FROM tablename UNION ALL SELECT count(*) AS count, date(solarAweberConfDateTime) AS unique_date, 'aweber_7solar_confirmALIAS' AS origin FROM tablename etc Correction the column names will come form the first query, but they need to be the same datatype in those columns. So if the first query has a date datatype, the subsequent queries must have date columns in the first position Thanks for the correction, I thought the column names/aliases had to match but you're right. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Your query says: get the date() from the solarAWDateTime field then group by that date and give me the date and number of results ie - you asked for the number of results per date - which is what you got. You didn't ask for the number of unique dates. yes. And in fact that was what I wanted. I better quit for the day while I am ahead, and not spread around any more brain-fry now. Thank you Chris, and all. John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Aug 3, 2009, at 12:29 AM, Govinda govinda.webdnat...@gmail.com wrote: Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The issue is with the select distinct, if you wrap the date in the parantheses for the with the distinct, the example I sent last night works fine. Select distinct ( date_format( solarLandingDate , '%Y-%m-%d')), solarLandingIP,... If you don't place the distinct parentheses around the date, the engines tries for a distinct on the entire row, which is why you end up with all rows Bastien Sent from my iPod -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Bastien, I had tried it with the parantheses around the date for the distinct. I tried again just now. Same result. But that's ok. I am onto the next step now. Niel, Jack, I got your fix working. It shows me that I am still so new; I own yet so little mastery of MySQL. Nisse, I see what you are suggesting. It seems I can go that route too. I have much to learn in every direction, so for right now anyway I am thinking to pursue the stream of thought started with what Niel and Jack just gave me. I do need data from the other columns too, and not just the date extracted from that timestamp field, ...and I need to count # of records in other tables that have the same unique date as the list of unique dates I just found in my first table, etc. .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I need to play with everything before asking for more detailed help; I am just now asking if you think I am on the right track with my thinking - as I just mentioned in the sentence above this one? Thanks everyone! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote: .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Just keep in mind that while that may be a very possible solution when datasets are small. This could get problematic when for instance there are a 10 years worth of dates and millions of records in the other tables. The resulting program could end up taking lots of time to display data. In your case this might not happen if you do not get that much data, but again we do not know. Just something to keep in mind before deploying. Jack -Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: Tuesday, August 04, 2009 12:34 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column? Bastien, I had tried it with the parantheses around the date for the distinct. I tried again just now. Same result. But that's ok. I am onto the next step now. Niel, Jack, I got your fix working. It shows me that I am still so new; I own yet so little mastery of MySQL. Nisse, I see what you are suggesting. It seems I can go that route too. I have much to learn in every direction, so for right now anyway I am thinking to pursue the stream of thought started with what Niel and Jack just gave me. I do need data from the other columns too, and not just the date extracted from that timestamp field, ...and I need to count # of records in other tables that have the same unique date as the list of unique dates I just found in my first table, etc. .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I need to play with everything before asking for more detailed help; I am just now asking if you think I am on the right track with my thinking - as I just mentioned in the sentence above this one? Thanks everyone! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.40/2276 - Release Date: 08/01/09 18:04:00 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
.. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben Thanks Ben. And yes Jack, ..I was attracted to the nested query as that required less new SQL ground to learn right now while I am expected to produce! But as that user (and others in that thread you gave, Ben) said, better to learn to do things the right way. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? Here's what I am trying to do: I have a table created by this: $SQL=CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT); and other tables too, like this: $SQL=CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAwConfIP TINYTEXT); and this: $SQL=CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking TINYTEXT, ... (plus more columns); I need to query these 3 tables (in one query! ;-) ...to return to me: one iteration of a while loop... ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/ tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... ...*per unique DATE* found in the 'solarLandingDateTime' column of the 1st (t7solar_landing) table. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote:
.. so my thought is to want to do nested query(ies), where:
*within* the while loop of the first recordset (which is now
successfully returning just rows with unique dates), I do other
query(ies) which will (in their own code block) find all rows of the
date we are iterating.. so I can, for example, count number of records
for each unique date, do math/statistics, etc.
I had to do something similar in code of my own a little while ago,
and got
some very good guidance on Stack Overflow. Here's the thread, you
might find it
helpful:
http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop
The user whose answer is marked as the correct one (Quassnoi) also
writes a
helpful blog on SQL. You should be able to find the blog by clicking
on the
username.
Ben
Thanks Ben. And yes Jack,
..I was attracted to the nested query as that required less new SQL
ground to learn right now while I am expected to produce! But as that
user (and others in that thread you gave, Ben) said, better to learn to
do things the right way.
So I need to read/learn more MySQL. Can you guys point me to where in
the mysql docs I should be burying myself?
Here's what I am trying to do:
I have a table created by this:
$SQL=CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY
KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT);
and other tables too, like this:
$SQL=CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY
KEY,solarAwConfIP TINYTEXT);
and this:
$SQL=CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP
TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking TINYTEXT,
... (plus more columns);
I need to query these 3 tables (in one query! ;-) ...to return to me:
one iteration of a while loop...
...which will echo:
trtd#records in 't7solar_landing' matching the given (iterating)
date (in the 'solarLandingDateTime' column)/tdtd#records in
'aw_7solar_confirm' matching the given (iterating) date (in the
'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw'
matching the given (iterating) date (in the 'solarAWDateTime'
column)/td/tr...
...*per unique DATE* found in the 'solarLandingDateTime' column of the
1st (t7solar_landing) table.
What's the obsession with just doing one loop?
To start off, do it in two steps, then worry about making it one
statement (though it doesn't necessarily need to be done in one go).
You need to get it right first before anything else.
Get unique dates:
$query = select DISTINCT DATE(solarLandingDateTime) AS landing_date
from t7solar_landing;
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
$date = $row['landing_date'];
$query =
select
count(solarLandingDateTime) as landing_count,
count(solarAwConfDateTime) as confirm_count,
count(solarAWDateTime) as aw_count
from
t7solar_landing
left join aw_7solar_confirm
left join aw_7solar_aw
where
date(solarLandingDateTime) = '.mysql_real_escape_string($date).'
or
date(solarAwConfDateTime) = '.mysql_real_escape_string($date).'
or
date(solarAWDateTime) = '.mysql_real_escape_string($date).'
;
// print results
}
After you're sure that you are getting the right results, work on doing
it in one query.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? In my experience the MySQL manual isn't a great resource for learning SQL, at the level you're looking for. It's a fine reference if you already have a solid understanding of the basics. But to get that understanding, you might try the O'Reilly book called Learning SQL: http://oreilly.com/catalog/9780596520830/?CMP=AFC-ak_bookATT=Learning+SQL%2c+Second+Edition%2c Someone else here might know of some good online resources. I've not seen any, but then I haven't spent a whole lot of time looking. The parts of Learning SQL that I've seen are excellent. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Ben Dunlap wrote: ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. .. and a field describing which table it came from, otherwise you end up with: count | date 5 | 2009-01-01 10| 2009-01-01 and no reference point. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Chris wrote: Ben Dunlap wrote: ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. .. and a field describing which table it came from, otherwise you end up with: count | date 5 | 2009-01-01 10| 2009-01-01 and no reference point. I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Hi all
I'm translating some code from another server-side language into PHP,
and I need something that 'summarizes' results found from a MySQL
SELECT. I.e. -
$foundTrackingRows=mysql_query(SELECT...
while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...
..such that the while loop only loops *ONCE per unique _date_ found
(regardless of the hour/min./sec.)* in my column which is of type
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
PRIMARY KEY
For example, if I have column values like these:
2009-08-01 07:01:00
2009-07-30 18:16:37
2009-07-30 17:49:06
2009-07-27 17:35:52
2009-07-27 17:24:21
2009-07-27 17:23:03
..then my while { loop would only fire 3 times.
I do my RTFM; can someone just give me a good point in the right
direction.
Thanks!
John Butler/(Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote:
Hi all
I'm translating some code from another server-side language into PHP,
and I need something that 'summarizes' results found from a MySQL
SELECT. I.e. -
$foundTrackingRows=mysql_query(SELECT...
while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...
..such that the while loop only loops *ONCE per unique _date_ found
(regardless of the hour/min./sec.)* in my column which is of type
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY
For example, if I have column values like these:
2009-08-01 07:01:00
2009-07-30 18:16:37
2009-07-30 17:49:06
2009-07-27 17:35:52
2009-07-27 17:24:21
2009-07-27 17:23:03
..then my while { loop would only fire 3 times.
I do my RTFM; can someone just give me a good point in the right direction.
Thanks!
You need to do this on the mysql side, not in php - php can't summarize
the data before processing it, so you need to use something like the
date() function in mysql on your timestamp column.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date
without knowing your original query it's hard to give an example, but:
select distinct date(timestamp_column) from table;
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 8:02 PM, Govindagovinda.webdnat...@gmail.com wrote:
You need to do this on the mysql side, not in php - php can't summarize
the data before processing it, so you need to use something like the date()
function in mysql on your timestamp column.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date
without knowing your original query it's hard to give an example, but:
select distinct date(timestamp_column) from table;
Thanks Chris,
I am getting closer, but so far it is not iterating only once per unique
'date part of the datetime expression', it is returning all the rows in the
table, including those with the very same date but different time in the
value of the 'solarLandingDateTime' column. There is not alot of discussion
in the mysql docs that I saw about how to work with DISTINCT. I need to
grab data out of the 3 columns: solarLandingIP, solarLandingDir,
solarLandingDateTime (this part of my SELECT is working).
This is what I have:
$foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable.
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query
failed: .mysql_error());
-Govinda
try
foundTrackingRows=mysql_query(SELECT DISTINCT
DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable.
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query
failed: .mysql_error());
--
Bastien
Cat, the other other white meat
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 8:02 PM,
Govindagovinda.webdnat...@gmail.com wrote:
You need to do this on the mysql side, not in php - php can't
summarize
the data before processing it, so you need to use something like
the date()
function in mysql on your timestamp column.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date
without knowing your original query it's hard to give an example,
but:
select distinct date(timestamp_column) from table;
Thanks Chris,
I am getting closer, but so far it is not iterating only once per
unique
'date part of the datetime expression', it is returning all the
rows in the
table, including those with the very same date but different time
in the
value of the 'solarLandingDateTime' column. There is not alot of
discussion
in the mysql docs that I saw about how to work with DISTINCT. I
need to
grab data out of the 3 columns: solarLandingIP, solarLandingDir,
solarLandingDateTime (this part of my SELECT is working).
This is what I have:
$foundTrackingRows=mysql_query(SELECT DISTINCT
DATE(solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM .
$whichTable.
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or
die(query
failed: .mysql_error());
-Govinda
try
foundTrackingRows=mysql_query(SELECT DISTINCT
DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM .
$whichTable.
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query
failed: .mysql_error());
Bastien,
I just did try that. I also tried this, (thinking there was a typo in
your version, in the DATE_FORMAT formatting string), :
$foundTrackingRows=mysql_query(SELECT DISTINCT DATE_FORMAT('%Y-%m-
%d',solarLandingDateTime) solarLandingIP, solarLandingDir,
solarLandingDateTime FROM .$whichTable. ORDER BY
solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed:
.mysql_error());
still no luck.
-Govinda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
What does the resultset look like? I am getting every record in the table back
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 11:13 PM, Govindagovinda.webdnat...@gmail.com wrote: What does the resultset look like? I am getting every record in the table back my bad, i reversed the mysql and php date function formats try SELECT distinct( DATE_FORMAT( fieldname, '%Y-%m-%d' ) ) FROM table ORDER BY field DESC -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda There is no comma between DATE(solarLandingDateTime) and solarLandingIP which means the DATE column will use the alias 'solarLandingIP'. Is this your intention? Or is the solarLandingIP another column from the table. If the latter, you may want to do something like this: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: . mysql_error()); If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda There is no comma between DATE(solarLandingDateTime) and solarLandingIP which means the DATE column will use the alias 'solarLandingIP'. Is this your intention? Or is the solarLandingIP another column from the table. If the latter, you may want to do something like this: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: . mysql_error()); Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
The distinct can only work as you want it to work when you only select the date column and only the date part (not the time part). Unfortunately I'm more an oracle DBA where the date functions are more clear to me so can not help with exact syntax. I think what you should be doing is returning the entire set of records (all required columns) in sorted by date order and loop through all of them. keep track of the last date processed and if it is the same as the current record, process nothing and go get the next record. BUT I am not quite sure what you are trying to achieve so my advise may be completely flawed. Jack 2009/8/3 Govinda govinda.webdnat...@gmail.com Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
The distinct can only work as you want it to work when you only select the date column and only the date part (not the time part). Unfortunately I'm more an oracle DBA where the date functions are more clear to me so can not help with exact syntax. I think what you should be doing is returning the entire set of records (all required columns) in sorted by date order and loop through all of them. keep track of the last date processed and if it is the same as the current record, process nothing and go get the next record. BUT I am not quite sure what you are trying to achieve so my advise may be completely flawed. Jack 2009/8/3 Govinda govinda.webdnat...@gmail.com Doh, should have realised this before, but it's after 4 AM here. Jack is absolutely correct here, you are getting all the rows back because that is what the query asks for. Try this as your first step SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate FROM . $whichTable . ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: . mysql_error()); This should get your unique rows by date. The previous queries used ALL of the column names to form a unique row, and that made all the rows DISTINCT. Give us an idea of your table structure, if possible post the CREATE statement for it. Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
