Page 1 of 1

help with PHP Mysql search engine

Posted: Tue Jul 21, 2009 12:03 am
by anshuverma1989
hey guys i made this search engine.. it is actually working but not giving the proper result..
can u please tell me my mistake..

Code: Select all

<
form method="get" action="person_search_result.php">
First Name: <input type="text" name="first_name" id="first_name">
Last Name: <input type="text" name="last_name" id="last_name">
State: <input type="text" name="state" id="state">
City / Area / Postal Code: <input type="text" name="city" id="city">
 
 <input class="button" src="images/button_eng.gif" value="Search" tabindex="3" alt="Find" type="image">
</form>
 
thats my form code..

Code: Select all

 
$fname = $_GET['first_name'];
$lname = $_GET['last_name'];
$state = $_GET['state'];
$city = $_GET['city'];
 
if($fname=="" AND $lname=="" AND $state=="" AND $city=="")
{
   print "<script>";
    print "self.location='index.php';"; // Comment this line if you don't want to redirect
    print "</script>";
}   
else if($fname=="")
{
   $sql = mysql_query("SELECT * FROM person where last_name LIKE '%$lname%' AND state LIKE '%$state%' AND (address LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where last_name LIKE '%$lname%' AND state LIKE '%$state%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "Last Name: ".$_GET['last_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;City / Area / Postal Code: ".$_GET['city'];
}
 
else if($lname=="")
{
   $sql = mysql_query("SELECT * FROM person WHERE first_name LIKE '%$fname%' AND state LIKE '%$state%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person WHERE first_name LIKE '%$fname%' AND state LIKE '%$state%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "First Name: ".$_GET['first_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;City / Area / Postal Code: ".$_GET['city'];
}
else if($state=="")
{
   $sql = mysql_query("SELECT * FROM person where first_name LIKE '%$fname%' AND last_name LIKE '%$lname%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name LIKE '%$fname%' AND last_name LIKE '%$lname%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "First Name: ".$_GET['first_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Last Name: ".$_GET['last_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;City / Area / Postal Code: ".$_GET['city'];
}
else if($city=="")
{
   $sql = mysql_query("SELECT * FROM person where first_name LIKE '%$fname%' AND last_name LIKE '%$lname%' AND state LIKE '%$state%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name LIKE '%$fname%' AND last_name LIKE '%$lname%' AND state LIKE '%$state%' AND active =1"),0);
   $search_term = "First Name: ".$_GET['first_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Last Name: ".$_GET['last_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state'];
}
else if($fname=="" AND $lname=="")
{
   $sql = mysql_query("SELECT * FROM person where state LIKE '%$state%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where state LIKE '%$state%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "City / Area / Postal Code: ".$_GET['city']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state'];
}
else if($fname=="" AND $state=="")
{
   $sql = mysql_query("SELECT * FROM person where last_name LIKE '%$lname%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where last_name LIKE '%$lname%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "Last Name: ".$_GET['last_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;City / Area / Postal Code: ".$_GET['city'];
}
else if($fname=="" AND $city=="")
{
   $sql = mysql_query("SELECT * FROM person where last_name LIKE '%$lname%' AND state  LIKE '%$state%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where last_name LIKE '%$lname%' AND state  LIKE '%$state%' AND active =1"),0);
   $search_term = "Last Name: ".$_GET['last_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state'];
}
else if($lname=="" AND $state=="")
{
   $sql = mysql_query("SELECT * FROM person where first_name LIKE '%$fname%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name LIKE '%$fname%' AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "First Name: ".$_GET['first_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;City / Area / Postal Code: ".$_GET['city'];
}
else if($lname=="" AND $city=="")
{
   $sql = mysql_query("SELECT * FROM person where first_name LIKE '%$fname%' AND state  LIKE '%$state%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name LIKE '%$fname%' AND state  LIKE '%$state%' AND active =1"),0);
   $search_term = "First Name: ".$_GET['first_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state'];
}
else if($state=="" AND $city=="")
{
   $sql = mysql_query("SELECT * FROM person where first_name LIKE '%$fname%' AND last_name  LIKE '%$lname%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name LIKE '%$fname%' AND last_name  LIKE '%$lname%' AND active =1"),0);
   $search_term = "First Name: ".$_GET['first_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Last Name: ".$_GET['last_name'];
}
else if($fname=="" AND $lname=="" AND $state=="")
{
   $sql = mysql_query("SELECT * FROM person where active =1 AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where active =1 AND (address  LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%')"),0);
   $search_term = "City / Area / Postal Code: " .$_GET['city'];
}
else if($fname=="" AND $lname=="" AND $city=="")
{
   $sql = mysql_query("SELECT * FROM person where state  LIKE '%$state%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where state  LIKE '%$state%' AND active =1"),0);
   $search_term = "State: " .$_GET['state'];
 
}
else if($fname=="" AND $state=="" AND $city=="")
{
   $sql = mysql_query("SELECT * FROM person where last_name  LIKE '%$lname%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where last_name  LIKE '%$lname%' AND active =1"),0);
   $search_term = "Last Name: " .$_GET['last_name'];
}
else if($lname=="" AND $state=="" AND $city=="")
{
   $sql = mysql_query("SELECT * FROM person where first_name  LIKE '%$fname%' AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name  LIKE '%$fname%' AND active =1"),0);
   $search_term = "First Name: " .$_GET['first_name'];
}
else
{
   $sql = mysql_query("SELECT * FROM person where first_name LIKE '%$fname%' AND last_name LIKE '%$lname%' AND state LIKE '%$state%' AND (address LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1 ORDER BY 'hits' DESC LIMIT $from, $max_results ")
or die("Could not select data because ".mysql_error());
 
   $total_results  = mysql_result(mysql_query("SELECT  COUNT(*) as Num FROM person where first_name LIKE '%$fname%' AND last_name LIKE '%$lname%' AND state LIKE '%$state%' AND (address LIKE '%$city%' OR city LIKE '%$city%' OR pin_code LIKE '%$city%') AND active =1"),0);
   $search_term = "Last Name: ".$_GET['last_name']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State: ".$_GET['state']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;City / Area / Postal Code: ".$_GET['city'];
}
 
As u can see it has many if condition so when i enter only the first name, it goes to some other if condition which is not matching. For Example If i enter only one or two words, it should go to condition where other three fields or 2 fields are blank .. but it goes to some other condition.. can you please help..

and tell me where am i going wrong?? please

Like I entered my first name, it went to the following code

Re: help with PHP Mysql search engine

Posted: Tue Jul 21, 2009 1:57 am
by DaveTheAve
Your code is very difficult to read and very very long and it doesn't need to be. I get the feeling your new to the language so if you don't mind I'd love to show you something I've learned to love - check this out:

Code: Select all

 
$query = 'SELECT * FROM person where ';
 
if(!empty($lname))
   $query += "last_name LIKE '%$lname%' ";
 
if(!empty($fname))
   $query += "first_name LIKE '%$fname%' ";
 
if(!empty($state))
   $query += "state LIKE '%$state%'";
 
$sql = mysql_query($query) or die("Could not select data because ".mysql_error());
 
Do this and although, yes there still is a lot of whitespace and it's still long. It's one heck of a lot easier to follow and a lot less redundant code. :P