Calling two sql statements

...cant figure this out....

[tonynoriega]

First sql statement, i want to count all the registration records that have been entered for the past 7 days. thats it. and get the total amount.

then i want to count how many each agent as entered during the week, and get a total count for each agent.

Can i not call two different sql statements, and have them display in the same table?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
function leadstablereport_display_addon() {
$security_admin = login::loginCheck('Admin', true);
$security_agent = login::loginCheck('Agent', true);
if ($security_admin === true ¦¦ $security_agent === true) {
global $config, $conn;
require_once($config['basepath'] . '/include/misc.inc.php');
$misc = new misc();
$ts = time();
$todays_date = date("F j Y");
$current_date = date("F j");
$seconds = 24 * 60 * 60;

$six_days = 6 * $seconds;
$five_days = 5 * $seconds;
$four_days = 4 * $seconds;
$three_days = 3 * $seconds;
$two_days = 2 * $seconds;
$one_days = 1 * $seconds;

$end_date = date("F j Y", ( $ts - $six_days));
$seven_daysago = date("F j", ( $ts - $six_days));
$six_daysago = date("F j", ( $ts - $five_days));
$five_daysago = date("F j", ( $ts - $four_days));
$four_daysago = date("F j", ( $ts - $three_days));
$three_daysago = date("F j", ( $ts - $two_days));
$two_daysago = date("F j", ( $ts - $one_days));
$one_daysago = date("F j", ( $ts - $one_days));

$sql = 'SELECT *, COUNT(*) AS weekscount FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time';

$recordSet = $conn->Execute($sql);

// CHECK IF ERROR
if ($recordSet === false) {
$misc->log_error($sql);
}
// KEEP READING TILL THE END

while (!$recordSet->EOF) {
$wc = $misc->make_db_unsafe ($recordSet->fields['weekscount']);

// GO GET THE NEXT ONE
$recordSet->MoveNext();
}

$sql = 'SELECT nhm_associate, COUNT(nhm_associate) AS totalcount FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time GROUP BY nhm_associate';
// EXECUTE
$recordSet = $conn->Execute($sql);

// CHECK IF ERROR
if ($recordSet === false) {
$misc->log_error($sql);
}
// KEEP READING TILL THE END

$display .='<table width="100%" border="1" style=border-collapse:collapse; border-style:solid;>'; // start your table before
$display .='<tr><td colspan="9"><h1 align="center">Registration Leads Entered Week of: ' . $end_date . ' to ' . $todays_date . '</h1></td></tr>';
$display .='<tr><td></td><td>' . $seven_daysago . '</td><td>' . $six_daysago . '</td><td>' . $five_daysago . '</td><td>' . $four_daysago . '</td><td>' . $three_daysago . '</td><td>' . $two_daysago . '</td><td>' . $current_date . '</td><td>Weeks Total</td></tr>';

while (!$recordSet->EOF) {
$tc = $misc->make_db_unsafe ($recordSet->fields['totalcount']);
$nhma = $misc->make_db_unsafe ($recordSet->fields['nhm_associate']);

$display .='<tr><td>' . $nhma . '</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>' . $tc . '</td></tr>';

// GO GET THE NEXT ONE
$recordSet->MoveNext();
}

$display .='<tr><td>Total Registrations</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>' . $wc . '</td><tr>';
$display .='</table>'; // close your table after

} else {
// DISPLAY A WARNING TO THE PAGE AS ADMIN/AGENT IS NOT LOGGED
$display .= 'YOU ARE NOT AUTHORIZED TO VIEW THIS PAGE. PLEASE GO TO THE BOTTOM OF THE PAGE AND LOG IN!';
$recordSet->MoveNext();
}
return $display;
}

[syber]

You should be able to combine the two SQL statements into one, which should solve your problem.

SELECT nhm_associate, COUNT(nhm_associate) AS totalcount,  
 (SELECT COUNT(*) FROM registration_table  
 WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time) AS weekscount  
 FROM registration_table  
 WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time  
 GROUP BY nhm_associate 

[tonynoriega]

Sweet, i lean something new everyday.

thanks much.

Now, may i ask....

How would i go about showing how many entries were made for each of the past seven days?

As you might see in the table, there are the past 7 days i need to show how many were entered....

would you be able to point me in that direction?

[syber]

You would need to group by the day of week.

[tonynoriega]

Would that be something like:

SELECT nhm_associate, COUNT(nhm_associate) AS totalcount,
(SELECT COUNT(*) FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time) AS weekscount,
(SELECT COUNT(*) FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <=entry_time) AS day_six
(SELECT COUNT(*) FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <=entry_time) AS day_five
(SELECT COUNT(*) FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <=entry_time) AS day_four
//etc...etc...
FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time
GROUP BY nhm_associate

and then associate those variables of "day_six" "day_five" as fields so that i can just plug them into the <td> $day_six </td>

[tonynoriega]

syber, i have been screwing with this query all freaking day, and i have come to the same issue...

I have tried multiple variations of querying a specific day of the week, then grouping the results by agent, and by date...adn what is happening is that i get the correct value...

say i ad this into the query:

(SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) <= entry_time) AS yesterday

it returns a 7 which is how many entries were entered yesterday...

but it populates all the agents as having 7 entries, and only one agent actually entered 7 that day....

any ideas?

[Habtom]

The subqueries are always responding the same result without considering that the query is supposed to be grouped by nhm_associate.

If you don't get a better solution here, one way to do it is to make another small query, and involve a bit of PHP to loop the results.

$query = "SELECT nhm_associate FROM registration_table";
$result = mysql_query($query);

while ($row = mysql_fetch_array) {
$query_2 = "SELECT nhm_associate, COUNT(nhm_associate) AS totalcount,
(SELECT COUNT(*) FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time AND nhm_associate = ". $nhm_associate .") AS weekscount,
(SELECT COUNT(*) FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <=entry_time AND nhm_associate = ". $nhm_associate .") AS day_six
(SELECT COUNT(*) FROM registration_table
//etc...etc...
FROM registration_table
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time
AND nhm_associate = ". $nhm_associate ."";
$result2 = mysql_query($query_2);

// And use $result2 here, merge all $result2 arrays or use them to display something . . .
}