PHP/SQL Builiding
Posted: Mon Feb 06, 2006 11:30 am
I'm builiding a complex sql string in PHP. At present, it fails to pull up records within the specific date range, but, when I take out the other information and query just on the date range criteria, it succeeds. It also succeeds when there's no date range clause at all. Can someone point me to any errors they see with the way I'm combining the leading clause (the various criteria) and the trailing clause (the date range)?
In English, the components of the sql clause are:
"Select records that have statusCompleted equal to 0; where the employee's name shows up in one of the specificied columns; and, if those are satisfied, either where either one of the adjoining columns shows that the employee is required or one of the missing columns is set; also, where the record falls within the date range (if specified).
yields:
Burrito: Please use
In English, the components of the sql clause are:
"Select records that have statusCompleted equal to 0; where the employee's name shows up in one of the specificied columns; and, if those are satisfied, either where either one of the adjoining columns shows that the employee is required or one of the missing columns is set; also, where the record falls within the date range (if specified).
Code: Select all
if (($date01 == "0") && ($date02 == "0")) {
$daterange = "";
} else {
$daterange = " && (((DATE_SUB(CURDATE(),INTERVAL " . $date02 . " DAY)) < serviceDate) && ((DATE_SUB(CURDATE(),INTERVAL " . $date01 . " DAY)) >= serviceDate))";
}
$sql = "SELECT * FROM tbl_inc WHERE ";
$sql .= "((statusCompleted = 0)";
$sql .= " && ";
$sql .= "((employee01Username = \"" . $username . "\") || (employee02Username = \"" . $username . "\") || (employee03Username = \"" . $username . "\") || (pnc01Username = \"" . $username . "\") || (employeeAssistant01Username = \"" . $username . "\") || (employeeAssistant02Username = \"" . $username . "\") || (employeeAssistant03Username = \"" . $username . "\")))";
$sql .= " && ";
$sql .= "(((employee01Username = \"" . $username . "\") && (employee01Required = 1)) || ((employee02Username = \"" . $username . "\") && (employee02Required = 1)) || ((employee03Username = \"" . $username . "\") && (employee03Required = 1)) || ((pnc01Username = \"" . $username . "\") && (pnc01Required = 1)) || ((employeeAssistant01Username = \"" . $username . "\") && (employeeAssistant01Required = 1)) || ((employeeAssistant02Username = \"" . $username . "\") && (employeeAssistant02Required = 1)) || ((employeeAssistant03Username = \"" . $username . "\") && (employeeAssistant03Required = 1)))";
$sql .= " || ";
$sql .= "(((employee01Username = \"" . $username . "\") && (employee01Required = 0)) || ((employee02Username = \"" . $username . "\") && (employee02Required = 0)) || ((employee03Username = \"" . $username . "\") && (employee03Required = 0)) || ((pnc01Username = \"" . $username . "\") && (pnc01Required = 0)) || ((employeeAssistant01Username = \"" . $username . "\") && (employeeAssistant01Required = 0)) || ((employeeAssistant02Username = \"" . $username . "\") && (employeeAssistant02Required = 0)) || ((employeeAssistant03Username = \"" . $username . "\") && (employeeAssistant03Required = 0))) && ((hpiMissing = 1) || (rMissing = 1) || (eMissing = 1) || (diaMissing = 1) || (disMissing = 1) || (otherMissing != 'NULL'))";
$sql .= $daterange;
$sql .= " ORDER BY serviceDate DESC";
echo $sql;Code: Select all
SELECT * FROM tbl_inc WHERE ((statusCompleted = 0) && ((employee01Username = "jsmith") || (employee02Username = "jsmith") || (employee03Username = "jsmith") || (pnc01Username = "jsmith") || (employeeAssistant01Username = "jsmith") || (employeeAssistant02Username = "jsmith") || (employeeAssistant03Username = "jsmith"))) && (((employee01Username = "jsmith") && (employee01Required = 1)) || ((employee02Username = "jsmith") && (employee02Required = 1)) || ((employee03Username = "jsmith") && (employee03Required = 1)) || ((pnc01Username = "jsmith") && (pnc01Required = 1)) || ((employeeAssistant01Username = "jsmith") && (employeeAssistant01Required = 1)) || ((employeeAssistant02Username = "jsmith") && (employeeAssistant02Required = 1)) || ((employeeAssistant03Username = "jsmith") && (employeeAssistant03Required = 1))) || (((employee01Username = "jsmith") && (employee01Required = 0)) || ((employee02Username = "jsmith") && (employee02Required = 0)) || ((employee03Username = "jsmith") && (employee03Required = 0)) || ((pnc01Username = "jsmith") && (pnc01Required = 0)) || ((employeeAssistant01Username = "jsmith") && (employeeAssistant01Required = 0)) || ((employeeAssistant02Username = "jsmith") && (employeeAssistant02Required = 0)) || ((employeeAssistant03Username = "jsmith") && (employeeAssistant03Required = 0))) && ((hMissing = 1) || (rMissing = 1) || (eMissing = 1) || (diaMissing = 1) || (disMissing = 1) || (otherMissing != 'NULL')) && (((DATE_SUB(CURDATE(),INTERVAL 5000 DAY)) < serviceDate) && ((DATE_SUB(CURDATE(),INTERVAL 90 DAY)) >= serviceDate)) ORDER BY serviceDate DESCCode: Select all
tags when [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting PHP Code In The Forums[/url][/size]