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]