Pardon my ignorance here; I think I'm still very much a beginner with SQL, and I'm forced to learn fast.
I have a php script which is used to query an MS-SQL database. The query I've written is supposed to find all members in the database who:
* have an email address listed in the database,
* have agreed to receive emails from us (EmailList = 1),
* have a membership which expired MORE than X number of days ago,
* have been sent a renewal notice, and
* and have not renewed their membership.
// Select all the memberships which have expired more than a specified number of days ago.
$cutoff_days = 7; // number of days past
$sql = "SELECT TOP 1000000 Mem_Number, Title_1, Name_1, Surname_1, Email, Date_Sent_Renewal, EmailList, Renewal_Date FROM BTCMembers WHERE (CONVERT(char(10), Renewal_Date, 103) < '".date("d/m/Y", mktime(0,0,0,date("m"),date("d")-$cutoff
$result = odbc_exec($dbc, $sql); // Run the query
$count = 0; // start a counter
print ("<table><thead><tr><th>Member Number</th><th>Email</th><th>Renewal Date</th><th>Renewal Sent</th></tr></thead><tbody>");
while ($temp = odbc_fetch_array($result)) { // fetch the results into an array
$count++; // increment the counter;
print ("<tr><td>".$temp['Mem_Number']."</td><t
}
print ("</tbody></table>");
print ("<p>There are $count members who have:<ul><li>membership which expired more than $cutoff_days days ago,</li><li>been notified and sent a renewal notice, and</li><li>not renewed their membership.</li></ul></p>");
So logically, the number of records found should decrease when I increase the cutoff. But when I execute the script I get the following results:
* when $cutoff = 7, I get 970 records.
* when $cutoff = 30, I get 1398 records.
* when $cutoff = 120, I get 1548 records...
Can anyone spot a glaring error in my code? I'm working with PHP 5.
FIXED:
...by using SQL to do the date comparison, rather than php...
$sql = "SELECT TOP 1000000 Mem_Number, Title_1, Name_1, Surname_1, Email, Date_Sent_Renewal, EmailList, Renewal_Date FROM BTCMembers WHERE (DATEDIFF(d, Renewal_Date, GETDATE()) > ".$cutoff_days.") AND (Email <> '') AND (Date_Sent_Renewal <> '') AND (EmailList = 1) ORDER BY Mem_Number ASC";
I have a php script which is used to query an MS-SQL database. The query I've written is supposed to find all members in the database who:
* have an email address listed in the database,
* have agreed to receive emails from us (EmailList = 1),
* have a membership which expired MORE than X number of days ago,
* have been sent a renewal notice, and
* and have not renewed their membership.
// Select all the memberships which have expired more than a specified number of days ago.
$cutoff_days = 7; // number of days past
$sql = "SELECT TOP 1000000 Mem_Number, Title_1, Name_1, Surname_1, Email, Date_Sent_Renewal, EmailList, Renewal_Date FROM BTCMembers WHERE (CONVERT(char(10), Renewal_Date, 103) < '".date("d/m/Y", mktime(0,0,0,date("m"),date("d")-$cutoff
$result = odbc_exec($dbc, $sql); // Run the query
$count = 0; // start a counter
print ("<table><thead><tr><th>Member Number</th><th>Email</th><th>Renewal Date</th><th>Renewal Sent</th></tr></thead><tbody>");
while ($temp = odbc_fetch_array($result)) { // fetch the results into an array
$count++; // increment the counter;
print ("<tr><td>".$temp['Mem_Number']."</td><t
}
print ("</tbody></table>");
print ("<p>There are $count members who have:<ul><li>membership which expired more than $cutoff_days days ago,</li><li>been notified and sent a renewal notice, and</li><li>not renewed their membership.</li></ul></p>");
So logically, the number of records found should decrease when I increase the cutoff. But when I execute the script I get the following results:
* when $cutoff = 7, I get 970 records.
* when $cutoff = 30, I get 1398 records.
* when $cutoff = 120, I get 1548 records...
Can anyone spot a glaring error in my code? I'm working with PHP 5.
FIXED:
...by using SQL to do the date comparison, rather than php...
$sql = "SELECT TOP 1000000 Mem_Number, Title_1, Name_1, Surname_1, Email, Date_Sent_Renewal, EmailList, Renewal_Date FROM BTCMembers WHERE (DATEDIFF(d, Renewal_Date, GETDATE()) > ".$cutoff_days.") AND (Email <> '') AND (Date_Sent_Renewal <> '') AND (EmailList = 1) ORDER BY Mem_Number ASC";
