Page 1 of 1

PHP/SQL Builiding

Posted: Mon Feb 06, 2006 11:30 am
by cfytable
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).

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;
yields:

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 DESC
Burrito: Please use

Code: Select all

tags when [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting PHP Code In The Forums[/url][/size]

Posted: Tue Feb 07, 2006 6:59 am
by BadgerC82
Hi cfytable,

Ok firstly I rearranged your code:

Code: Select all

SELECT * FROM tbl_inc WHERE 

	(
		(statusCompleted = 0) && 

		(
			(employee01Username = "jsmith" && (employee01Required = 1 || employee01Required = 0 )) ||
			(employee02Username = "jsmith" && (employee02Required = 1 || employee02Required = 0 )) ||
			(employee03Username = "jsmith" && (employee03Required = 1 || employee03Required = 0 )) || 
			(pnc01Username = "jsmith" && (pnc01Required = 1 || pnc01Required = 0)) || 
			(employeeAssistant01Username = "jsmith" && (employeeAssistant01Required = 1 || employeeAssistant01Required = 0)) || 
			(employeeAssistant02Username = "jsmith" && (employeeAssistant02Required = 1 || employeeAssistant02Required = 0)) || 
			(employeeAssistant03Username = "jsmith" && (employeeAssistant03Required = 1 || 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 DESC
This make you query a little smaller and faster :)

Without seeing your table structure its very hard to see what is wrong. Are you running phpmyadmin or another mysql tool that can give you feedback?

Posted: Tue Feb 07, 2006 9:05 am
by feyd
The choice of sticking multiple fields of the same dataforms in them is unfortunate. If the OP normalized the structure, it would be more simple.

Info on normalization: http://en.wikipedia.org/wiki/Database_normalization