Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ben Dunlap wrote:
 > ...which will echo:
 "<tr><td>#records in 't7solar_landing' matching the given (iterating)
date  (in the 'solarLandingDateTime' column)</td><td>#records in
'aw_7solar_confirm' matching the given (iterating) date (in the
'solarAwConfDateTime' column)</td><td>#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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux