stupid COUNT question.

am i missing something...

[tonynoriega]

i just want to show how many records have an entry for the column "last_visit"....

$sql = 'SELECT COUNT(last_visit) AS visit FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time';

$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$misc->log_error($sql);
}
$display .='<table>
<tr><td>Summary</td>
<td rowspan="3">Notes</td></tr>
<tr><td>' . $reg_visit . '</td></tr>
<tr><td>&nbsp;</td></tr>';
while (!$recordSet->EOF) {
$reg_visit = $misc->make_db_unsafe ($recordSet->fields['last_visit']);

$display .= '';

$recordSet->MoveNext();

}

$display .='</table>';

[phranque]

this query might be what you want:

SELECT COUNT(*) AS visit FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time and last_visit IS NOT NULL

or perhaps if it's not a NULL default column:

SELECT COUNT(*) AS visit FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time and STRCMP(last_visit,'')

[Habtom]

Try by putting the last_visit as just visit

$reg_visit = $misc->make_db_unsafe ($recordSet->fields['last_visit']);

In the query you have mentioned the count value to be in "visit"

$sql = 'SELECT COUNT(last_visit) AS visit ...

Habtom

[tonynoriega]

Ok, got it to work...with this below....BUT i think i screwed myself becuase it counts the correct amount of records over the past 7 days which is 5...BUT...my "entry_time" column populates with 0000-00-00 if the user does not make an entry, SO, there are 3 records with actual entries, and two with all Zeros....which are included in the count...how can i solve that?

$sql = 'SELECT COUNT(last_visit) AS last_visit FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time AND last_visit IS NOT NULL';
$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$misc->log_error($sql);
}

$display .='<table width="100%" border="1" style=border-collapse:collapse; border-style:solid;>
<tr><td>Summary</td>
<td rowspan="3">Notes</td></tr>
<tr><td></td></tr>
<tr><td>Total 7 Day Traffic: </td></tr>';

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

$display .= '<tr><td>COUNT='. $reg_last_visit . '</td></tr>';

[phranque]

try this query:

SELECT COUNT(last_visit) AS last_visit FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time AND last_visit!= '0' AND last_visit IS NOT NULL