Need help with a PHP query

[deeciple]

Hi All,

I have created a query in php. The idea is that the user enters search criteria into some (but not necessarily all) text fields on my form and the form gets posted to my PHP page containing this query:

	$query ="SELECT *
			 FROM 	tblequipissues
			 WHERE 	Location LIKE '%$Location%' OR '$Location' IS NULL AND
					LocationNum LIKE '%$LocationNum%' OR '$LocationNum' IS NULL AND
					EquipType LIKE '%$EquipType%' OR '$EquipType' IS NULL AND
					UnitNo LIKE '%$EquipNum%' OR '$EquipNum' IS NULL AND
					IssueDate LIKE '%$IssueDate%' OR '$IssueDate' IS NULL AND
					FixedDate LIKE '%$FixedDate%' OR '$FixedDate' IS NULL AND
					EnteredBy LIKE '%$EnteredBy%' OR '$EnteredBy' IS NULL AND
					FixedBy LIKE '%$Assigned%' OR '$Assigned' IS NULL AND
					CloseTicket LIKE '%$CloseTicket%' OR '$CloseTicket' IS NULL";

I want the search results to be narrowed down more and more, based on the text in each field. For some reason I am getting records returned for fields where there shouldn't be a match. For example if I type "abc" in a field I am getting records containing "xyz" for the field, even though these records should be getting filtered out. Can anyone see what might be wrong with tis query to be causing this? I have been working on this for way too long.

Thanks in advance.

Ken

 

[feherke]

Hi

[ul]
[li]All those [tt]'$Location' IS NULL[/tt] fragments are wrong. Even if the PHP variable is [tt]null[/tt], the generated SQL will be [tt]'' IS NULL[/tt], and empty string will never be [tt]null[/tt].[/li]
[li]Due to Operator Precedence, your expression is equivalent with [tt][highlight #ffc]Location LIKE '%$Location%'[/highlight] OR [highlight #ffc][red]([/red]'$Location' IS NULL AND LocationNum LIKE '%$LocationNum%'[red])[/red][/highlight] OR [highlight #ffc][red]([/red]'$LocationNum' IS NULL AND EquipType LIKE '%$EquipType%'[red])[/red][/highlight] OR [gray](...)[/gray][/tt]. I suppose that is not what you intended to write.[/li]
[/ul]
Better compose the expression dynamically, adding each piece after you tested in PHP whether the related variable is set.


Feherke.
feherke.github.io

 

[deeciple]

Thanks feherke for your help. I modified the query to get rid of the IS NULL:

	$query ="SELECT *
			 FROM 	tblequipissues
			 WHERE 	Location LIKE '%$Location%' OR '$Location' = '' AND
					LocationNum LIKE '%$LocationNum%' OR '$LocationNum' = '' AND
					EquipType LIKE '%$EquipType%' OR '$EquipType' = '' AND
					UnitNo LIKE '%$EquipNum%' OR '$EquipNum' = '' AND
					IssueDate LIKE '%$IssueDate%' OR '$IssueDate' = '' AND
					FixedDate LIKE '%$FixedDate%' OR '$FixedDate' = '' AND
					EnteredBy LIKE '%$EnteredBy%' OR '$EnteredBy' = '' AND
					FixedBy LIKE '%$Assigned%' OR '$Assigned' = '' AND
					CloseTicket LIKE '%$CloseTicket%' OR '$CloseTicket' = ''";

I also tried wrapping my statements in parentheses like (LocationNum LIKE '%$LocationNum%' OR '$LocationNum' = '') but when I did this I got no records returned, even when I placed known matching text into my search fields. Can you suggest another way to address the operator order issue?

Thanks,

Ken

 

[jpadie]

is this what you are looking for?
this assumes you have already brought the posted variables into scope.

$fields = array( 'Location', "LocationNum', 'EquipType', 'EquipNum', 'IssueDate', 'FixedDate', 'EnteredBy',
'Assigned', 'CloseTicket');

$where = array();
foreach($fields as $field):
	if(isset(${$field}):
		$where[] = sprintf("( $field = '' OR $field LIKE '%s' )", '%' . mysql_real_escape_string(${$field}) .'%');
	else:
		//$where[] = "($field = '')"; //assumption is that if the variable does not exist, you don't want it in the query
	endif;
endforeach;	
$where = implode("  AND \n\t\t", $where);

$query = <<<SQL
SELECT 	*
FROM 	tblequipissues
WHERE 	
	$where
SQL;

in any event, if you show us your form code it will help work out how to put together your query.

 

[feherke]

Hi

I would manage to get to something like this :

$field=array(
  'Location'=>$Location,
  'LocationNum'=>$LocationNum,
  'EquipType'=>$EquipType,
  'UnitNo'=>$EquipNum,
  'IssueDate'=>$IssueDate,
  'FixedDate'=>$FixedDate,
  'EnteredBy'=>$EnteredBy,
  'FixedBy'=>$Assigned,
  'CloseTicket'=>$CloseTicket,
);

$where=array();
foreach ($field as $name=>$value)
  if ($value!='') 
    $where[]="$name like '%$value%'";

$query="select * from tblequipissues where ".implode(' and ',$where);

Of course, this is just theory. In practice the $field array would contain only the field names and the values would be taken directly from the $_GET array :

$field=array('Location','LocationNum','EquipType','UnitNo','IssueDate','FixedDate','EnteredBy','FixedBy','CloseTicket');

$where=array();
foreach ($field as $name)
  if ($_GET[$name]!='') 
    $where[]="$name like '%".mysql_real_escape_string($_GET[$name])."%'";

$query="select * from tblequipissues where ".implode(' and ',$where);

But we not know from where your values are coming.


Feherke.
feherke.github.io

 

[feherke]

Hi

Man, how slow I am...

Feherke.
feherke.github.io

 

[jpadie]

love how similar the code solutions are ! hopefully we are both reflecting 'best practice' rather than the same 'bad habits'!

 

[deeciple]

Hi All,

Sorry for the late reply. It's been crazy at the office. I got it working and the query was much simpler than I thought it needed to be. Here is the working code:

	$query ="SELECT *
			 FROM 	tblequipissues
			 WHERE 	Location LIKE '%$Location%' AND
					LocationNum LIKE '%$LocationNum%' AND
					EquipType LIKE '%$EquipType%' AND
					UnitNo LIKE '%$EquipNum%' AND
					IssueDate LIKE '%$IssueDate%' AND
					FixedDate LIKE '%$FixedDate%' AND
					EnteredBy LIKE '%$EnteredBy%' AND
					FixedBy LIKE '%$Assigned%'";

Using this I am now able to echo back to my main search page the results as a clickable table like so:

	$qryresult=mysql_query($query);

	echo "<table width='100%' border='1px' cellpadding='0' cellspacing='0' class='sortable'>";
	echo "<tr>
			<th width='15%'>Issue Date:</th>
			<th width='20%'>Entered by:</th>
			<th width='15%'>Location:</th>
			<th width='15%'>Equipment:</th>
			<th width='20%'>Assigned to:</th>
			<th width='10%'>Fixed Date:</th>
			<th width='5%'>Status:</th>
		</tr>";	
	while($row = mysql_fetch_assoc($qryresult)) {
		echo "<tr onclick=\"location.href='search-ticket-details.php?tblEquipIssuesID=".$row['tblEquipIssuesID']."';\">
				<td>".$row['IssueDate']."</td>
				<td>".$row['EnteredBy']."</td>
				<td>".$row['Location']." ".$row['LocationNum']."</td>
				<td>".$row['EquipType']." ".$row['UnitNo']."</td>
				<td>".$row['FixedBy']."</td>
				<td>".$row['FixedDate']."</td>
				<td>";

				if ($row['CloseTicket']==1){
					echo "Closed";
				}else{
					echo "Open";
				}

				echo "</td></tr>";
	}
	echo "</table>";

Thank you all for your help.

Best regards,

Ken

 

[jpadie]

please look at the code provided by me and feherke and consider why we both posted those solutions and why yours is different.

There are good reasons from a reusability and, more importantly, a security perspective.

to all future readers: NEVER use a variable in a query unless it has been sanitised first.