Searh help

[steviez]

Hi,

 

On my site i have a search form that allows users to search for people on the site. I have several search options (eg. age, eye color, hair color)

 

How would i build a search query to search the database with the given posted data from the search form?

 

 

Thanks

[Psycho]

It would help tremendously if you posted some of details such as the form fields and the database fields, but here is an example:

 

<?php

if (isset($_POST)) {

  $whereClauses = array();
  if ($_POST['eye_color']) { $whereClauses[] = "eye_color='{$_POST['eye_color']}'";
  if ($_POST['hair_color']) { $whereClauses[] = "hair_color='{$_POST['hair_color']}'";
  if ($_POST['age']) { $whereClauses[] = "age='{$_POST['age']}'";

  $query = "SELECT * FROM users WHERE " . implode(' AND ', $whereClauses);

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

}

?>

[steviez]

i cant get this to work

 

here is my code:

 

<?php
if (isset($_POST['Submit'])) {

  $whereClauses = array();
  if ($_POST['eye_color']) { $whereClauses[] = "eye_color='{$_POST['eye_color']}'"; }
  if ($_POST['hair_color']) { $whereClauses[] = "hair_color='{$_POST['hair_color']}'"; }
  if ($_POST['age']) { $whereClauses[] = "age='{$_POST['age']}'"; }

  $query = "SELECT username, profile_picture FROM members WHERE " . implode(' AND ', $whereClauses);

  #$result = mysql_query($query);
  
  $results = 1;

}

<?php if($results == 1 || $no_results == 1){ ?>
<!-- Begin Search Results -->
<div class="box">
<table width="100%" border="0">
  <tr>
    <td><img src="./images/search_results.gif" alt="Search Results" width="265" height="30" /></td>
  </tr>
</table>
<table width="100%" border="0">
  <tr>
    <td align="center">
<?php
if(!isset($_GET['page']))
{ 
$page = 1; 
}else{ 
$page = $_GET['page'];
}
$max_results = 28;
$from = (($page * $max_results) - $max_results);
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members WHERE user_privacy_search = '0'"),0);
$total_pages = ceil($total_results / $max_results); 

if($page > 1)
{
$prev = ($page - 1); 
    echo "<input type=\"button\" class=\"prevbutton\" onClick=\"parent.location='".site_url."/index.php?gtaction=members.Search&page=$prev&token=".rand_token()."'\" title=\"Previous\" />  ";
}
	  
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<input type=\"button\" class=\"nextbutton\" onClick=\"parent.location='".members_url."/index.php?gtaction=members.Search&page=$next&token=".rand_token()."'\" title=\"Next\" />";  
    }
?>
</td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td align="center">
   <?php
	  define ("NUMCOLS",7);
	  $res = mysql_query($query);
	  $count = 0;
	  echo "<table width=\"100%\" border=\"0\">\n";
	  while(list($col1, $col2) = mysql_fetch_row($res)){
	  if($count % NUMCOLS == 0) echo "<tr>\n";  # new row
	  if($col2 == "")
	  {
	  $pic = "../images/nophoto.gif";
	  }else{
	  $pic = $col2;
	  }
	  echo "<td class=\"sc_text\" width=\"120\">$col1<br /><a href=\"".site_url."/$col1\"><img src=\"".$pic."\" border=\"0\" alt=\"\" width=\"100\" height=\"100\" class=\"photo\" /></a></td>\n";
	  $count++;
	  if($count % NUMCOLS == 0) echo "</tr>\n";  # end row
	  }# end row if not already ended

	  if($count % NUMCOLS != 0){
	  while($count++ % NUMCOLS) echo "<td> </td>";
	  echo "</tr>\n";
	  }
	  echo "</table>";
      ?>
</td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td align="center">
<?php

if($page > 1)
{
$prev = ($page - 1); 
    echo "<input type=\"button\" class=\"prevbutton\" onClick=\"parent.location='".site_url."/index.php?gtaction=members.Search&page=$prev&token=".rand_token()."'\" title=\"Previous\" />  ";
}
	  
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<input type=\"button\" class=\"nextbutton\" onClick=\"parent.location='".members_url."/index.php?gtaction=members.Search&page=$next&token=".rand_token()."'\" title=\"Next\" />";  
    }

?>
</td>
  </tr>
</table>
</div>
<!-- End Search Results -->
<?php } ?>

?>

[br0ken]

SELECT username, profile_picture FROM members WHERE eye_colour = '$eyeColour' AND hair_colour = '$hairColour';

 

This should work, just add on all the extra WHERE clauses you need.

 

Personally, when doing searches rather than do one long search like this I will search for individual parameter and return the ID to the record. After searching for each parameter I would display the results ordered by the amount of times that ID was found. By doing it this way you get a weighted results giving you the records at the top that matched the results the best.