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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
