[SOLVED] Search mysql Problem

[graham23s]

Hi Guys,

 

i have made a search on my site using <option>value</option> to select your search criteria then press search, i did:

 

male or female

from country

then state

 

but when i added another category smoking it doesn't search any results any more, can anyone see any coding problems:

 

   // The GETS...////////////////////////////////////////////////////////////////////////
   $gender = $_GET['gender']; 
   $min_age = $_GET['min_age'];
   $max_age = $_GET['max_age'];
   $country = $_GET['country']; 
   $state = $_GET['state'];
   $smoking = $_GET['smoking'];
   $drinking = $_GET['drinking'];
   
   // The sort code...///////////////////////////////////////////////////////////////////
   $sort = ""; 

   if($_GET['Sort'] == "Ascending") { 
   
      $sort = "ASC"; 
      
   } else { 
   
      $sort = "DESC"; 
      
   }
         
   // Begin gender search.../////////////////////////////////////////////////////////////    
   if(!isset($_GET['gender']) || ($_GET['gender'] == "N/A")) { 
   
      echo "<br /><p>Sorry, You Never Selected A <font color=\"red\">Gender</font></p>"; 
      exit;
      
   } 
   
   // Begin country search...////////////////////////////////////////////////////////////
   if(!isset($_GET['country']) || ($_GET['country'] == "N/A")) { 
   
      echo "<br /><p>Sorry, You Never Selected A <font color=\"red\">Country</font></p>"; 
      exit; 
      
   } 
      
   // Compile the sql query...///////////////////////////////////////////////////////////  
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR)";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "AND `state`='" . $state . "' "; 
   $sql .=   "AND `smoking`='" . $smoking . "' "; 
   $sql .=   "ORDER BY `id` " . $sort; 
   
   echo $sql;

 

i echoed out the $sql it looks ok:

 

SELECT * FROM `membership` WHERE `gender`='Female' AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL 46 YEAR) AND YEAR(CURDATE() - INTERVAL 18 YEAR)AND `country`='United States' AND `state`='Alabama' AND `smoking`='Non Smoker' ORDER BY `id` DESC

 

Thanks guys

 

Graham

[igor berger]

Do an echo here to see that you have the right input that matched hte input in MySql record

$smoking = $_GET['smoking'];

echo $smoking;

[trq]

The only thing I can see is one missing space. Try...

 

$sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR) ";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "AND `state`='" . $state . "' "; 
   $sql .=   "AND `smoking`='" . $smoking . "' "; 
   $sql .=   "ORDER BY `id` " . $sort;

 

Are you getting any errors when you execute this query? Can we see where you execute the query?

[graham23s]

Hi Guys,

 

Thanks a lot for that, i think that space did the trick, 1 thing though i was wondering, say for example someone didn't want to specify a search i.e

 

Don't Mind

Smoker

Non-Smoker

 

am i right in saying this is ok:

 

<select name="smoking">
                                          <option value="">Don't Mind</option>
                                          <option value="Non Smoker">Non Smoker</option>
                                          <option value="Smoker">Occasionally</option>
                                          </select>

 

if they selected "Don't Mind", because i have left the value="" blank does that mean it wont pass anything through the GET so it SHOULD search all queries smoker and non smoker?

 

thanks guys

 

Graham

[igor berger]

No, it will search with smoker=""

 

To fix this do a if statement on your get.

 

If smoker="" do this MySql query else do this,

[graham23s]

Hi Igor,

 

like this would you say:

 

   // If statement for blank entries...//////////////////////////////////////////////////
   if($_GET['smoker'] == '') {
   
   $sql .=   "AND `smoking`='' ";
   
   } else {
   
   $sql .=   "AND `smoking`='" . $smoking . "' ";
   
   }

 

cheers

 

Graham

[igor berger]

If you do not want to search by this category, you do not need to include it in MySql search.

 

That is if the searcher does not care if the person smokes or not, do not use add it to the MySql query.

 

SELECT * FROM `membership` WHERE `gender`='Female' AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL 46 YEAR) AND YEAR(CURDATE() - INTERVAL 18 YEAR)AND `country`='United States' AND `state`='Alabama' ORDER BY `id` DESC

 

[graham23s]

Hi Igor,

 

ah i see, so instead of concatenating it in the $sql just put it like this:

 

   // If statement for blank entries...//////////////////////////////////////////////////
   if($_GET['smoker'] == '') {
   
   $sql .=   "AND `smoking`='' ";
   
   } else {
   
   $sql .=   "AND `smoking`='" . $smoking . "' ";
   
   }
      
   // Compile the sql query...///////////////////////////////////////////////////////////  
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR) ";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "AND `state`='" . $state . "' "; 
   $sql .=   "ORDER BY `id` " . $sort;

 

and basically just do the same for every other category would that be right?

 

cheers mate

 

Graham

[igor berger]

If you do not care if the person is a smoker or not.

 

if($_GET['smoker'] == '') {

 

do nothing

 

  }

 

else{

 

$sql .=  "AND `smoking`='" . $smoking . "' ";

 

 

}

 

You do not need if else!

 

if($_GET['smoker'] != '') {

 

$sql .=  "AND `smoking`='" . $smoking . "' ";

 

  }

 

 

[graham23s]

Hi Mate,

 

thanks for your help is a lot clearer now i still need to do this though right:

 

   // Compile the sql query...///////////////////////////////////////////////////////////  
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR) ";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "AND `state`='" . $state . "' ";
   $sql .=   "AND `smoking`='" . $smoking . "' ";
   $sql .=   "ORDER BY `id`" . $sort;

 

i still need to put this "  $sql .=  "AND `smoking`='" . $smoking . "' ";" in the query above? is that right?

 

cheers

 

Graham

[igor berger]

if smoke or no smoke this

 

$sql =    "SELECT * FROM `membership` ";

  $sql .=  "WHERE `gender`='" . $gender . "' ";

  $sql .=  "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR) ";

  $sql .=  "AND `country`='" . $country . "' ";

  $sql .=  "AND `state`='" . $state . "' ";

  $sql .=  "AND `smoking`='" . $smoking . "' ";

  $sql .=  "ORDER BY `id`" . $sort;

 

if you do not care that the person smoke or does not smoke this

 

 

$sql =    "SELECT * FROM `membership` ";

  $sql .=  "WHERE `gender`='" . $gender . "' ";

  $sql .=  "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR) ";

  $sql .=  "AND `country`='" . $country . "' ";

  $sql .=  "AND `state`='" . $state . "' ";

  $sql .=  "ORDER BY `id`" . $sort;

[graham23s]

Sorted, thanks for your help mate:)

 

Graham