• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Mutl-Field Search PHP

I have a multi-field search and trying to return results based on what was chosen.  Right now, my fields are not being handled properly on the results page and the query isn't running with the conditions.

Search Page:
<?php

$programid = "2036";

require_once($_SERVER['DOCUMENT_ROOT']."/includes/verifyaccess.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/mainfunctions.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/header.php");

?>

<span class="sectitle">Contact Management Search Form</span><br /><br />

<form action="searchresults.php" method="POST">
	<table width="100%" cellspacing="0" cellpadding="0" border="0">
		<tr>
			<td class="td_leftCol3" width="175px">Account Code:</td>
			<td class="td_rightCol"><input type="text" name="kdaccount" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Company Name:</td>
			<td class="td_rightCol"><input type="text" name="name" class="formFields"></td>
		</tr>	
		<tr>
			<td class="td_leftCol3" width="175px">Company Phone:</td>
			<td class="td_rightCol"><input type="text" name="phone" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Location:</td>
			<td class="td_rightCol"><input type="text" name="town" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Contact Name:</td>
			<td class="td_rightCol"><input type="text" name="contactname" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Contact Phone:</td>
			<td class="td_rightCol"><input type="text" name="contactphone" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">State:</td>
			<td class="td_rightCol">
				<select name="state" class="formFields">
					<option value="">Select a State...</option>
					<?php 
					$sql = mysql_query("SELECT stateid, state FROM dw_states ORDER BY state");
					
					if(mysql_num_rows($sql) > 0)
						{
						while($stateData = mysql_fetch_array($sql))
							{
							echo '<option value = "'.$stateData['stateid'].'">'.$stateData['state'].'</option>';
							}
						}
					?>
			</td>
		</tr>
		<?php if($groupid == '901' || $groupid == '910' || $groupid == '929' || $groupid == '999')
			{
			echo '
			<tr>
				<td class="td_leftCol3" width="175px">SIC Code:</td>
				<td class="td_rightCol">
					<select name="sic" class="formFields">
						<option value="">Select a SIC Code...</option>';
						
						$sql2 = mysql_query("SELECT sic4, sic4desc FROM dw_names_sic GROUP BY sic4 ORDER BY sic4 ASC");
						
						if(mysql_num_rows($sql2) > 0)
							{
							while($sicData = mysql_fetch_array($sql2))
								{
								echo '<option value = "'.$sicData['sic4'].'">'.$sicData['sic4desc'].'</option>';
								}
							}
				echo '
					</select>
				</td>
			</tr>';
			}
		?>
		<tr>
			<td class="td_leftCol3" width="175px">Referred By:</td>
			<td class="td_rightCol">
				<select name="referral" class="formFields">
					<option value="">Select Referral...</option>
					<?php
					$sql3 = mysql_query("SELECT refid, referredby FROM pr_referral ORDER BY referredby");
					
					if(mysql_num_rows($sql2) > 0)
						{
						while($referralData = mysql_fetch_array($sql3))
							{
							echo '<option value = "'.$referralData['refid'].'">'.$referralData['referredby'].'</option>';
							}
						}
					echo '
				</select>
			</td>	
		</tr>';
		?>
		
		<tr>
			<td class="td_leftCol3" width="175px">Result/Prospect/All:</td>
			<td class="td_rightCol">
				<select name="comptype" class="formFields">
					<option value="1">All</option>
					<option value="2">Prospect</option>
					<option value="3">Result</option>
				</select>
			</td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Status:</td>
			<td class="td_rightCol">
				<select name="kstatus" class="formFields">
					<option value="1">All</option>
					<option value='2'>Active</option>
					<option value='3'>Deactivated</option>
					<option value='4'>On Hold</option>			
				</select>
			</td>
		</tr>
		<?php if($groupid == '901' || $groupid == '910' || $groupid == '929' || $groupid == '999')
			{
			echo '
			<tr>
				<td class="td_leftCol3" width="175px">Sales Rep:</td>
				<td class="td_rightCol">
					<select name="nlrep" class="formFields">
						<option value="">Select a Rep...</option>';
						
						$sql4 = mysql_query("SELECT krep, ckname FROM pr_resp WHERE hidden = '0' and itresp = '0' and (responsibility = '2' OR responsibility = '4') ORDER BY ckname");
						
						if(mysql_num_rows($sql4) > 0)
							{
							while($repData = mysql_fetch_array($sql4))
								{
								echo '<option value="'.$repData['krep'].'">'.$repData['ckname'].'</option>';
								}
							}
						echo '
					</select>
				</td>
			</tr>';
			}
		?>
		<tr>
			<td colspan="2">&nbsp;</td>
		</tr>
		<tr>
			<td colspan="2" align="center"><input type="submit" name="submit" value = "Search!"></td>
		</tr>
	</table>
</form>

Open in new window



Result Page:

<?php

$programid = "2036";

require_once($_SERVER['DOCUMENT_ROOT']."/includes/verifyaccess.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/mainfunctions.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/expFunctions.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/header.php");

//Make sure search for was submitted properly

if(isset($_POST['submit'])) 
	{
    //define the list of fields
    $fields = array('kdaccount', 'name', 'phone', 'town', 'state', 'sic', 'referral', 'kstatus', 'nlrep');
	
	//put conditionals in an array
    $conditions = array();
	
	foreach($fields as $field)
		{
        // if the field is set and not empty
        if(isset($_POST[$field]) && $_POST['field'] != '') 
			{
			// create a new condition while escaping the value inputed by the user (SQL Injection)
			$conditions[] = "`$field` LIKE '%" . mysql_real_escape_string($_POST[$field]) . "%'";
			}
		}

	// builds the query
	$query = "SELECT name, kdaccount FROM dw_names ";
		
	// if there are conditions defined
	if(count($conditions) > 0) 
		{
		// append the conditions
		$query .= "WHERE " . implode (' AND ', $conditions);
		}
	
	$result = mysql_query($query);
	echo $query . '<br />';
	}

Open in new window

<?php

$programid = "2036";

require_once($_SERVER['DOCUMENT_ROOT']."/includes/verifyaccess.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/mainfunctions.php");
require_once($_SERVER['DOCUMENT_ROOT']."/includes/header.php");

?>

<span class="sectitle">Contact Management Search Form</span><br /><br />

<form action="searchresults.php" method="POST">
	<table width="100%" cellspacing="0" cellpadding="0" border="0">
		<tr>
			<td class="td_leftCol3" width="175px">Account Code:</td>
			<td class="td_rightCol"><input type="text" name="kdaccount" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Company Name:</td>
			<td class="td_rightCol"><input type="text" name="name" class="formFields"></td>
		</tr>	
		<tr>
			<td class="td_leftCol3" width="175px">Company Phone:</td>
			<td class="td_rightCol"><input type="text" name="phone" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Location:</td>
			<td class="td_rightCol"><input type="text" name="town" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Contact Name:</td>
			<td class="td_rightCol"><input type="text" name="contactname" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Contact Phone:</td>
			<td class="td_rightCol"><input type="text" name="contactphone" class="formFields"></td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">State:</td>
			<td class="td_rightCol">
				<select name="state" class="formFields">
					<option value="">Select a State...</option>
					<?php 
					$sql = mysql_query("SELECT stateid, state FROM dw_states ORDER BY state");
					
					if(mysql_num_rows($sql) > 0)
						{
						while($stateData = mysql_fetch_array($sql))
							{
							echo '<option value = "'.$stateData['stateid'].'">'.$stateData['state'].'</option>';
							}
						}
					?>
			</td>
		</tr>
		<?php if($groupid == '901' || $groupid == '910' || $groupid == '929' || $groupid == '999')
			{
			echo '
			<tr>
				<td class="td_leftCol3" width="175px">SIC Code:</td>
				<td class="td_rightCol">
					<select name="sic" class="formFields">
						<option value="">Select a SIC Code...</option>';
						
						$sql2 = mysql_query("SELECT sic4, sic4desc FROM dw_names_sic GROUP BY sic4 ORDER BY sic4 ASC");
						
						if(mysql_num_rows($sql2) > 0)
							{
							while($sicData = mysql_fetch_array($sql2))
								{
								echo '<option value = "'.$sicData['sic4'].'">'.$sicData['sic4desc'].'</option>';
								}
							}
				echo '
					</select>
				</td>
			</tr>';
			}
		?>
		<tr>
			<td class="td_leftCol3" width="175px">Referred By:</td>
			<td class="td_rightCol">
				<select name="referral" class="formFields">
					<option value="">Select Referral...</option>
					<?php
					$sql3 = mysql_query("SELECT refid, referredby FROM pr_referral ORDER BY referredby");
					
					if(mysql_num_rows($sql2) > 0)
						{
						while($referralData = mysql_fetch_array($sql3))
							{
							echo '<option value = "'.$referralData['refid'].'">'.$referralData['referredby'].'</option>';
							}
						}
					echo '
				</select>
			</td>	
		</tr>';
		?>
		
		<tr>
			<td class="td_leftCol3" width="175px">Result/Prospect/All:</td>
			<td class="td_rightCol">
				<select name="comptype" class="formFields">
					<option value="1">All</option>
					<option value="2">Prospect</option>
					<option value="3">Result</option>
				</select>
			</td>
		</tr>
		<tr>
			<td class="td_leftCol3" width="175px">Status:</td>
			<td class="td_rightCol">
				<select name="kstatus" class="formFields">
					<option value="1">All</option>
					<option value='2'>Active</option>
					<option value='3'>Deactivated</option>
					<option value='4'>On Hold</option>			
				</select>
			</td>
		</tr>
		<?php if($groupid == '901' || $groupid == '910' || $groupid == '929' || $groupid == '999')
			{
			echo '
			<tr>
				<td class="td_leftCol3" width="175px">Sales Rep:</td>
				<td class="td_rightCol">
					<select name="nlrep" class="formFields">
						<option value="">Select a Rep...</option>';
						
						$sql4 = mysql_query("SELECT krep, ckname FROM pr_resp WHERE hidden = '0' and itresp = '0' and (responsibility = '2' OR responsibility = '4') ORDER BY ckname");
						
						if(mysql_num_rows($sql4) > 0)
							{
							while($repData = mysql_fetch_array($sql4))
								{
								echo '<option value="'.$repData['krep'].'">'.$repData['ckname'].'</option>';
								}
							}
						echo '
					</select>
				</td>
			</tr>';
			}
		?>
		<tr>
			<td colspan="2">&nbsp;</td>
		</tr>
		<tr>
			<td colspan="2" align="center"><input type="submit" name="submit" value = "Search!"></td>
		</tr>
	</table>
</form>

Open in new window

0
t3chguy
Asked:
t3chguy
  • 2
  • 2
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
At line 39 on the Results page, I would echo the $query to make sure I was getting what I wanted.
0
 
t3chguyAuthor Commented:
I did a few lines down but the query is nit what I want...its excluding the where clause
0
 
Dave BaldwinFixer of ProblemsCommented:
You had $_POST['field'] instead of $_POST[$field] on line 23 above.  Here is a simple test file showing it works.  Save as a PHP file and try it.  I have a bunch of things commented out so I could test the functions without a database.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>PHP Multi-Field Search</title>
</head>
<body>
<h1>PHP Multi-Field Search</h1>
<?php

$programid = "2036";

//require_once($_SERVER['DOCUMENT_ROOT']."/includes/verifyaccess.php");
//require_once($_SERVER['DOCUMENT_ROOT']."/includes/mainfunctions.php");
//require_once($_SERVER['DOCUMENT_ROOT']."/includes/expFunctions.php");
//require_once($_SERVER['DOCUMENT_ROOT']."/includes/header.php");

//Make sure search for was submitted properly

if(isset($_POST['submit'])) 
	{
		echo "<pre>";
    //define the list of fields
    $fields = array('kdaccount', 'name', 'phone', 'town', 'state', 'sic', 'referral', 'kstatus', 'nlrep');
		var_dump($fields);
		echo "</pre>";
	//put conditionals in an array
    $conditions = array();
		
	foreach($fields as $field)
		{
        // if the field is set and not empty
        //if(isset($_POST[$field]) && $_POST['field'] != '') 
        if(isset($_POST[$field]) && $_POST[$field] != '') 
			{
			// create a new condition while escaping the value inputed by the user (SQL Injection)
			//$conditions[] = "`$field` LIKE '%" . mysql_real_escape_string($_POST[$field]) . "%'";
			$conditions[] = "`$field` LIKE '%" . $_POST[$field] . "%'";
			}
		}

	// builds the query
	$query = "SELECT name, kdaccount FROM dw_names ";
		
	// if there are conditions defined
	if(count($conditions) > 0) 
		{
		// append the conditions
		$query .= "WHERE " . implode (' AND ', $conditions);
		}
	
	//$result = mysql_query($query);
	echo $query . '<br />';
	}
?>
<form action="" method="post">
<input type="text" name="kdaccount" value="kdaccount" /><br>
<input type="text" name="name" value="name" /><br>
<input type="text" name="phone" value="phone" /><br>
<input type="text" name="town" value="town" /><br>
<input type="text" name="state" value="state" /><br>
<input type="text" name="sic" value="sic" /><br>
<input type="text" name="referral" value="referral" /><br>
<input type="text" name="kstatus" value="kstatus" /><br>
<input type="text" name="nlrep" value="nlrep" /><br>
<input type="submit" name="submit" /><br>
</form>
</body>
</html>

Open in new window

0
 
t3chguyAuthor Commented:
That did it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now