trouble using multiple search fields

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
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

trouble using multiple search fields

Post by someguyhere »

I'm probably taking the wrong approach here, but I've got a form that has multiple fields that visitors can search by - the problem is that it returns every row in the DB. I think there might be something other than isset that would work the way I intend, but don't know what it would be.

Can someone point me in the right direction here?

Code: Select all

if(isset($_POST[company])){

		$mysqli = new mysqli("localhost", "xxx", "xxx", "xxx");
			$query = "SELECT * FROM wp_network_members WHERE company LIKE '%" . mysql_real_escape_string($_POST[company]) . "%'";
			$result = mysqli_query($mysqli, $query) or die(mysqli_error());
			while($row = mysqli_fetch_assoc($result)){
				$search_results[] = $row;
			}

	foreach ($search_results as &$value) {
		echo "<div class=\"networksearchitem\">" . "\n";
		echo "<a href=\"/" . strtolower($value[f_name]) . "-" . strtolower($value[l_name]) . "/\"><img src=\"" . $value[headshot] . "\" class=\"networksearchimage\" /></a>" . "\n";
		echo "<a href=\"/" . strtolower($value[f_name]) . "-" . strtolower($value[l_name]) . "/\"><img src=\"" . $value[logo] . "\" class=\"networksearchlogo\" /></a>" . "\n";
		echo "<h3><a href=\"/" . strtolower($value[f_name]) . "-" . strtolower($value[l_name]) . "/\">" . $value[f_name] . " " . $value[l_name] . ", " . $value[company] . "</a></h3>" . "\n";
		echo "<hr class=\"floatfixer\" />" . "\n";
		echo "</div>" . "\n";
	}

}

if(isset($_POST[l_name])){

		$mysqli = new mysqli("localhost", "xxx", "xxx", "xxx");
			$query = "SELECT * FROM wp_network_members WHERE l_name LIKE '%" . mysql_real_escape_string($_POST[l_name]) . "%'";
			$result = mysqli_query($mysqli, $query) or die(mysqli_error());
			while($row = mysqli_fetch_assoc($result)){
				$search_results[] = $row;
			}

	foreach ($search_results as &$value) {
		echo "<div class=\"networksearchitem\">" . "\n";
		echo "<a href=\"/" . strtolower($value[f_name]) . "-" . strtolower($value[l_name]) . "/\"><img src=\"" . $value[headshot] . "\" class=\"networksearchimage\" /></a>" . "\n";
		echo "<a href=\"/" . strtolower($value[f_name]) . "-" . strtolower($value[l_name]) . "/\"><img src=\"" . $value[logo] . "\" class=\"networksearchlogo\" /></a>" . "\n";
		echo "<h3><a href=\"/" . strtolower($value[f_name]) . "-" . strtolower($value[l_name]) . "/\">" . $value[f_name] . " " . $value[l_name] . ", " . $value[company] . "</a></h3>" . "\n";
		echo "<hr class=\"floatfixer\" />" . "\n";
		echo "</div>" . "\n";
	}
}

if (empty($_POST)){

	echo '<form enctype="multipart/form-data" action="" method="post">';
	echo '<input type="text" name="l_name" /><br />';
	echo '<input type="text" name="company" /><br />';
	echo '<input type="text" name="zip" /><br />';
	echo '<input type="submit" value="Search" />';
	echo '</form>';

}

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: trouble using multiple search fields

Post by John Cartwright »

If you leave either of those POST values blank, you will basically end up with a double wildcard,. WHERE foo = '%%' will match everything.

But why are you performing seperate queries for each column?

Perhaps try something like this to build your query:

Code: Select all

$searchSql = array('1=1'); //So we have atleast 1 WHERE condition to avoid parse error

if (!empty($_POST['company'])) {
   $searchSql[] = "`company` LIKE '%". mysql_real_escape_string($_POST['company']) ."%'";  
}

if (!empty($_POST['l_name'])) {
   $searchSql[] = "`l_name` LIKE '%". mysql_real_escape_string($_POST['l_name']) ."%'";  
}

$query = "SELECT * FROM wp_network_members WHERE ". implode(' AND ', $searchSql);
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: trouble using multiple search fields

Post by someguyhere »

John Cartwright wrote:But why are you performing seperate queries for each column?
Because I'm still a newbie at php and couldn't figure out a better way :mrgreen:

Thanks. I'll try your solution out and see if I can get it to work.
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: trouble using multiple search fields

Post by someguyhere »

Worked like a charm!

Thanks!
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: trouble using multiple search fields

Post by someguyhere »

By the way, what is the purpose of the ` character? I've not seen it used like this before.
John Cartwright wrote:

Code: Select all

$searchSql[] = "`company` LIKE '%". mysql_real_escape_string($_POST['company']) ."%'";
Post Reply