PHP/SQL Builiding

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cfytable
Forum Commoner
Posts: 29
Joined: Thu May 12, 2005 3:36 pm

PHP/SQL Builiding

Post 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]
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Post Reply