Re: 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: "<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>"... ...*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/