Multiple Field Search

chowell18

I'm trying to perform a multiple field search, but can't get the format of my statement down.

I have two fields: state & campground

I am using one search box & I want to pull up partial matching results for either field. I assume I need to use an "OR" in my statement, but have had no luck. When I did try it, it switched from only searching in field, to searching in the other...

I'm using Dreamweaver MX for all of my coding, etc..

Can anyone give me a sample statement to try or point me in the right direction for a solution?

Thanks,
Cory

cgraz

You could just build the query dynamically. Your form has two fields, correct? Method set to POST? You don't want to have a name on your submit button either, or else this code needs to be modified. But basically, what you'll want to do is build the query dynamically

$query = "SELECT * FROM table"; // change to your table name

foreach($_POST as $key => $value) {

   if($query == "SELECT * FROM table") { // change to your table name
      $query .= " WHERE $key='$value'";
   } else {
      $query .= " AND $key='$value'";
   }

}

print $query . "<br>"; // useful for error checking until it's working properly

$result = mysql_query($query) or die(mysql_error());

This will allow you to add fields to your form and the search will continue to work properly and give you the search results you want. Make sure that the names you give your form inputs match the field names in your database.

Cgraz

chowell18

I apologize, I should have indicated the way my form was setup...

I'm using a search page on locate.html and my results are on location-results.html. I'm using the "GET" method on the form & taking the variable from the URL on the location-results.html page to display the search results.

Any other ideas?

cgraz

change $POST to $GET in the foreach loop.

Do you have your server set up to parse .html as php files? if not, you'll need to change the extension on location-results.html to .php

Cgraz

chowell18

Cgraz,
Thanks for clarification... I'll try that solution first thing in the morning..

As far as the file naming, I was just providing an example. My results page does have a .php extension. Thanks for checking though. (I'll try to be more accurate in my examples in the future)

Thanks

datona

cgraz, awsome code! i was looking for a solution to this exact problem and your code did it BUT now I am at a deadend as I have tried to implement this in a search page that has PREVIOUS and NEXT but have not been able to fix this.
Here is my complete code. I hope that someone could guide me in the right direction.


<?php
session_start(); 
session_register(); 
include 'config/db_connect.php';
include("config/config.php");
include("templates/top.htm");
include("templates/left.htm");

$submit='';
$year = date("Y");
if ($birth_year) { 
$birth_year = ($year - $birth_year);
} else {
$birth_year='';
}
$to = $to;
if($to == "") { $to = "%%"; }
if($state == "NONE") { $state = "%%"; }
//if($to == "") { $to = "%%"; }

$age_min = "$age_min";
$age_max = "$age_max";

$pagelimit = "3";

$now = getdate();
$nmonth = $now['month'];
$nday   = $now['mday'];
$nyear  = $now['year'];

$sql = "SELECT * FROM clients"; // change to your table name

foreach($_POST as $key => $value) {

   if($sql == "SELECT * FROM clients") { // change to your table name
      $sql .= " WHERE $key like '%$value%'";
   } else {
      $sql .= " AND $key like '%$value%'";
   }

}

print $sql . "<br>"; // useful for error checking until it's working properly




$query = mysql_query("$sql") or die(mysql_error());

$totalrows = mysql_num_rows($query);

$pagenums = ceil ($totalrows/$pagelimit);
// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
   $pagenums = ceil ($totalrows/$pagelimit);

// if no value for page, page = 1
if ($page==''){ $page='1'; }

// create a start value
$start = ($page-1) * $pagelimit;

// blank matches found
echo "
<body bgcolor=\"#CBD3FA\" leftmargin=\"0\" topmargin=\"0\" marginwidth=\"0\" marginheight=\"0\">
<table width=\"100%\" border=\"0\" cellspacing=\"10\" cellpadding=\"0\" bgcolor=\"#CBD3FA\">
<tr bgcolor=\"#CBD3FA\"> 
<td width=\"74%\" valign=\"top\">\n";

// Showing Results 1 to 1 (or if you're page limit were 5) 1 to 5, etc.
$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}

// create dynamic next, previous, and page links

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}
$sql = "SELECT * FROM clients"; // change to your table name

foreach($_POST as $key => $value) {

   if($sql == "SELECT * FROM clients") { // change to your table name
      $sql .= " WHERE $key like '%$value%'";
   } else {
      $sql .= " AND $key like '%$value%'";
   }

}
//$result = mysql_query("$sql LIMIT $start,$pagelimit") or die(mysql_error());
//REMOVE THIS

if (mysql_num_rows($result) > 0) {
echo "
<table width=\"100%\" border=\"0\" cellspacing=\"10\" cellpadding=\"0\" bgcolor=\"#CBD3FA\">
<tr> 
<td colspan=\"2\" height=\"9\" valign=\"top\"> 
<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"5\" bordercolor=\"#000066\" bgcolor=\"#ECEEFF\">
<tr> 
<td width=\"23%\"> 
<div align=\"center\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">
";
// previous link 


if ($page>1) {
        echo "<a href='fsearch.php?state=$state&to=$to&".($page-1)."' class=searchlinks>Previous</a>";
    } else { 
echo "Previous";
}
echo "
</font></div>
</td>
<td width=\"54%\">

<div align=\"center\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">Goto 
Page ";

// dynamic page number links 
    for ($i=1; $i<=$pagenums; $i++) {
        if ($i!=$page) {
            echo " <a href='fsearch.php?state=$state&to=$to&page=$i'>$i</a>";
        }
        else {
            echo " <b>$i</b>";
        }
    }
echo "</font></div>
</td>
<td width=\"23%\"> 
<div align=\"center\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">
";
if ($page<$pagenums) { 
echo "<a href='fsearch.php?state=$state&to=" . $to . "&page=".($page+1)."'>Next Page</a>";
    } else { 
echo "Next Page";
}
echo "</font></div>
</td>
</tr>
</table>
</td>
</tr>
<tr> 
<td colspan=\"2\" height=\"103\"> 

";

$sql = "SELECT * FROM clients"; // change to your table name

foreach($_POST as $key => $value) {

   if($sql == "SELECT * FROM clients") { // change to your table name
      $sql .= " WHERE $key like '%$value%'";
   } else {
      $sql .= " AND $key like '%$value%'";
   }
}


$sql .= " LIMIT $start,$pagelimit";

$result = mysql_query("$sql") or die(mysql_error());
$totalrows = mysql_num_rows($result);
while ($r = mysql_fetch_array($result)) {

$ethnic = $r['ethnic'];
$bodyType = $r['bodyType'];
$m = $r['birth_month'];
$d = $r['birth_day'];
$y = $r['birth_year'];
$feet = $r['feet'];
$inches = $r['inches'];
$cm = $r['cm'];
$state = $r['state'];
$state = $r['state'];

switch ($ethnic) {
case '1': $ethnic = "caucasian (white)"; break;
case '2': $ethnic = "african american (black)"; break;
case '3': $ethnic = "asian"; break;
case '4': $ethnic = "hispanic"; break;
case '5': $ethnic = "first nations"; break;
case '6': $ethnic = "east indian"; break;
case '7': $ethnic = "middle eastern"; break;
case '8': $ethnic = "other"; break;
}
switch ($bodyType) {
case 'a': $bodyType = "Slim"; break;
case 'b': $bodyType = "Fit"; break;
case 'c': $bodyType = "Muscular"; break;
case 'd': $bodyType = "Average"; break;
case 'e': $bodyType = "Few extra pounds"; break;
case 'f': $bodyType = "Prefer not to say"; break;
}
$query= mysql_query("SELECT states_name FROM states WHERE states_value=$state") or die (mysql_error()); 
while($row = mysql_fetch_array($query)){ 
foreach( $row AS $key => $val ){ 
$$key = stripslashes( $val ); 
} }

$now = getdate();
$nmonth = $now['month'];
$nday   = $now['mday'];
$nyear  = $now['year'];

if ($nmonth < $m OR $nmonth = $m AND $nday < $d) {
    $userage = $nyear - $y - 1;
} else {
    $userage = $nyear - $y;
}



$client_id = $r["client_id"];
$thumb = "/client_images/nopicture.jpg";
$query= mysql_query("SELECT * FROM client_images where client_id='$client_id'") or die (mysql_error()); 
while($row = mysql_fetch_array($query)){ 
      foreach( $row AS $key => $val ){ 
   $$key = stripslashes( $val ); 
      } 
}
$sizzle = "SELECT average FROM imagetable WHERE client_id = $client_id";
$sizzle = mysql_query($sizzle);
$sizzle = mysql_fetch_array($sizzle);

echo "
<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"5\" bordercolor=\"#000066\" bgcolor=\"#ECEEFF\">
<tr> 
<td> 
<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
<tr> 
<td width=\"26%\" valign=\"top\" height=\"112\"> 
<div align=\"center\"> 
<p>&nbsp;</p>
<p><a href=/profile.php?id=$client_id><img src=$thumb></a>
<br>
</p>
</div>
<p align=\"center\"><b><font color=\"#CC0000\">sizzle or fizzle </font><font color=\"#FF3300\">" . $sizzle[average] . "</font></b></p>
<div align=\"center\"></div>
</td>
<td width=\"74%\" height=\"112\"> 
<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\" bordercolor=\"#000066\">
<tr valign=\"top\"> 
<td colspan=\"2\"> 
<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"5\" bordercolor=\"#000066\" bgcolor=\"#FFFFFF\">
<tr> 
<td> 
<p>" . $r['nickname'] . " " . $r['client_id'] . "</p>
<p>" . $r['openingLine'] . "</p>
</td>
</tr>
</table>
<br>
<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"4\" bordercolor=\"#000066\" bgcolor=\"#FFFFFF\">
<tr> 
<td colspan=\"1\" width=\"49%\"><font face=\"Arial, Helvetica, sans-serif\" size=\"2\"><b><font color=\"#000066\">
$userage
</font></b><font face=\"Verdana, Arial, Helvetica, sans-serif\">years 
old</font></font></td>
<td colspan=\"4\" width=\"51%\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">
";
if(!$r['cm']) {

echo "$feet Feet, $inches Inches";
} else { 
echo "$cm cm";
}
echo "</font></td>
</tr>
<tr> 
<td colspan=\"1\" width=\"49%\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Ethnicity is <font color=\"#000066\"><b>$ethnic</b></font></font></td>
<td colspan=\"4\" width=\"51%\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><b><font color=\"#000066\">$bodyType</font></b> body</font></td>
</tr>
<tr> 
<td colspan=\"1\" width=\"49%\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Located 
in <font color=\"#000066\"><b>" . $r['city'] . ", $states_name</b></font> 
</font></td>
<td colspan=\"4\" width=\"51%\">
<font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Last Online </font></td>
</tr>
</table>
</td></tr></table>
</td></tr></table>
</td></tr></table>
<BR>
";
}
} else {
   echo "No data.";
}
echo "
</td>
</tr>
</table>
</td>
";

include("templates/right.php");
include("templates/bottom.htm");
mysql_free_result($result);
?>

cgraz

wow, even that next/previous code looks familiar. Glad it came to good use.

To solve your problem, i'd make hidden inputs and submit the form when they click previous or next using javascript. Something like this:

<form method="post" name="theForm">

<?

foreach($_POST as $key => $value) {
   echo "<input type=\"hidden\" name=\"" . $key . "\" value=\"" . $value . "\">\n";
}

// put your link here for next and/or previous

<a href="bla bla" onClick="theForm.submit();">

</form>

Cgraz

datona

Cgraz, That code seems to work fine to pull the code up into the search page BUT when I click on the next, it does not pass ANY of the hidden variables. I did not want to post the whole code again as it is quite long.

This is the code I added:

<form method=\"post\" name=\"theForm\">
<input type=\"hidden\" name=\"page\" value=\"".($page+1)."\">
";

if ($page<$pagenums) { 
foreach($_POST as $key => $value) {
   echo "<input type=\"hidden\" name=\"" . $key . "\" value=\"" . $value . "\">\n";
}
echo "<a href='full-search.php' onClick=\"theForm.submit();\">Next Page</a></form>";

And this is what it outputs when I submit to the form the first time BUT when I hit next, none of the variable get passed

<form method="post" name="theForm">
<input type="hidden" name="page" value="2">
<input type="hidden" name="nickname" value="">
<input type="hidden" name="birth_year" value="">
<input type="hidden" name="state" value="0002">
<input type="hidden" name="fun1" value="">
<input type="hidden" name="talkabout1" value="">
<input type="hidden" name="friendsdescribe1" value="">
<input type="hidden" name="moreaboutme" value="">
<input type="hidden" name="bodyType" value="">
<input type="hidden" name="ethnic" value="">
<input type="hidden" name="smoke" value="">
<input type="hidden" name="religion" value="">
<input type="hidden" name="drink" value="">
<input type="hidden" name="education" value="">
<a href='full-search.php' onClick="theForm.submit();">Next Page</a></form>

cgraz

ah i'm sorry. I forgot it won't pass the variables unless you set an action. So change your form tag and a href to look like this

Now, your a href no longer has an href, so it will now look like just regular text. You can underline it or make it blue or whatever to match the style of your links so that it appears to be a link although it has no href. It's sole purpose is to submit the form.

Cgraz

datona

Geeze, now I have another problem.
How can I pass the page number using that because I since I am using the code that you provided to search for all variables, I get this:
Unknown column 'page' in 'where clause'
It is attempting to search for the 'page' variable.
I now, I am just a newbe but I am getting frustrated here.

echo "</font></div>
</td>
<td width=\"23%\"> 
<div align=\"center\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">
<form method=\"post\" name=\"theForm\" action=\"full-search.php\"> 
<input type=\"hidden\" name=\"page\" value=\"".($page+1)."\">
";

if ($page<$pagenums) { 
foreach($_POST as $key => $value) {
   echo "<input type=\"hidden\" name=\"" . $key . "\" value=\"" . $value . "\">\n";
}
echo "<a onClick=\"theForm.submit();\">Next Page</a></form>
";
    } else { 
echo "Next Page";
}
[\PHP]

cgraz

Yeah, that's casue you're adding it to the $_POST array. I'd pass it through the url.

Cgraz

matt_4013

I've created a function for someone else that builds a query, like this, but excludes any variable names that you pass to the function as arguments. DISCLAIMER: I edited it a little before I posted it and didn't test it, so if it doesn't work gimme a yell and I'll fix it 🙂

function build_query($tablename, $method) {

/* This function builds a query from your sent variables.
It will exclude any variable names you add after the 3 required 
parameters. 

Usage: 

$array = array("foo"=>"foo1", "bar"=>"bar1", "pie"=>"mmm, pie");

$sql = build_query("table", "POST", "bar");

The resulting query will look like this:

"SELECT * FROM table WHERE foo='foo1' AND pie='mmm, pie'"

Created By Matt Magin.

Feel free to use this wherever you like, be it a commercial 
or non-commercial product, open source or closed, whatever, I don't care,
but I would like it if you kept my name on it!

*/

// Set the method variable to the proper string
switch(strtoupper($method)) {

	case ('GET' || '_GET'):

		$method = "_GET";
		break;

	case ('REQUEST' || '_REQUEST'):

		$method = "_REQUEST";
		break;

	case ('SESSION' || '_SESSION'):

		$method = "_SESSION";
		break;

	default: 

		$method = "_POST";
		break;
}

// Get the number of function arguments
$num_args = func_num_args();

// Get the actual values of said arguments
$args = func_get_args();

// Loop through all keys in the chosen method array
foreach($$method as $k=>$v) {

	// Unset the arg_exists boolean
	unset($arg_exists);

	/* Loop through the extra arguments, if it matches the value
	set the arg_exists boolean and break out of the loop to decrease 
	the amount of processing */
	for($i = 2; $i < $num_args; $i++) {

		if($k == $args[$i]) {

			$arg_exists = true;
			break;
		}
	}

	if(!isset($arg_exists)) {

		/* If the argument didn't exist check to see if the where string is 
		empty, if it is, then append the first value, else append the second */
		$where_string .= ($where_string == "") ? " WHERE ($k='$v')" : " AND ($k='$v')";
	}
}

// Build the whole query
$sql = "SELECT * FROM $tablename $where_string";

// Return the completed sql
return $sql;
}

datona

cgraz,

I was able to get this going by using:

$new_page = ($page+1);
if ($page<$pagenums) { 
echo "
<form method=\"post\" name=\"theForm\" action=\"full-search.php?page=$new_page\">
";
foreach($_POST as $key => $value) {
echo "<input type=\"hidden\" name=\"" . $key . "\" value=\"" . $value . "\">\n";
}
echo "<a href=# onClick=\"theForm.submit();\">Next</a></form>";
    } else { 
echo "Next";
}
[\PHP]
And everything is working great now except for I am attempting to have a search field being birth_year (This is what it is called in the database) but in the search form, the person would enter in say.....20 which would mean it would have to do something like:

[code=php]
$year = date("Y");
if ($birth_year) { 
$birth_year = ($year - $birth_year);
echo "$birth_year<BR>";
} else {
$birth_year='';
}
[\PHP]

This would search for anyone who is 20 years old (2003 minus 20 = 1983) It would check the database field 'birth_year' being 1983

Can this be done with your formula?