Dont understand what is the error in select query

[niths]

hi all,
i am trying a query but i am not getting the result properly..
Is there any error in my query..

$query ="select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_Id,Blood_Group from Employee where";
      
      if($empid!='')
      {
          $query1=$query . " Emp_Id like '".$empid."%' and";
         // echo $query1;
      }
      
      if($empname!='')
      {
          $query1=$query . " First_Name like '%".$empname."%' and";
      }
      
      if($empname!='')
      {
          $query1=$query . " Last_Name like '%".$empname."%' and";
      }
      
      if($mobileno!='')
      {
          $query1=$query . " Mobile_No like '%".$mobileno."%' and ";
         // echo $query1;
      }
      
       if($bloodgroup!='')
      {
          $query1=$query . " Blood_Group like '".$bloodgroup."%'";
      }
      
      
      $query2= mysql_query($query1);
      
      mysql_error();
      $num=mysql_num_rows($query2);
      echo $num;

Thank u.

[Borzoi]

What are your expected and actual results?

[pritaeas]

In some cases your query can end with an 'and', which is invalid.

[Borzoi]

$query ="select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_Id,Blood_Group from Employee where";
      
      if($empid!='')
      {
          $query1=$query . " Emp_Id like '".$empid."%'";
         // echo $query1;

          if($empname!='')
          {
              $query1=$query . " and First_Name like '%".$empname."%'";
          }
      
          if($empname!='')
          {
              $query1=$query . " and Last_Name like '%".$empname."%'";
          }
      
          if($mobileno!='')
          {
              $query1=$query . " and Mobile_No like '%".$mobileno."%'";
              // echo $query1;
          }
      
          if($bloodgroup!='')
          {
              $query1=$query . " and Blood_Group like '".$bloodgroup."%'";
          }
      }
      else if($empname!='')
      {
          $query1=$query . " First_Name like '%".$empname."%'";

          if($empname!='')
          {
              $query1=$query . " and Last_Name like '%".$empname."%'";
          }
      
          if($mobileno!='')
          {
              $query1=$query . " and Mobile_No like '%".$mobileno."%'";
              // echo $query1;
          }
      
          if($bloodgroup!='')
          {
              $query1=$query . " and Blood_Group like '".$bloodgroup."%'";
          }
      }
      else if($empname!='')
      {
          $query1=$query . " Last_Name like '%".$empname."%'";

          if($mobileno!='')
          {
              $query1=$query . " and Mobile_No like '%".$mobileno."%'";
              // echo $query1;
          }
      
          if($bloodgroup!='')
          {
              $query1=$query . " and Blood_Group like '".$bloodgroup."%'";
          }
      }
      else if($mobileno!='')
      {
          $query1=$query . " Mobile_No like '%".$mobileno."%'";
         // echo $query1;

          if($bloodgroup!='')
          {
              $query1=$query . " and Blood_Group like '".$bloodgroup."%'";
          }
      }
      else if($bloodgroup!='')
      {
          $query1=$query . " Blood_Group like '".$bloodgroup."%'";
      }
      
      
      $query2= mysql_query($query1);
      
      mysql_error();
      $num=mysql_num_rows($query2);
      echo $num;

I know it's a bit long winded but it will eliminated any erroneous "and" at the end or beginning.

[pritaeas]

<?php
$query = "select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_Id,Blood_Group from Employee where ";       
$where = array ();

if(!empty($empid))      
  $where[] = "Emp_Id like '%$empid%'";
  
if(!empty($empname)) {
  $where[] = "First_Name like '%$empname%'";
  $where[] = "Last_Name like '%$empname%'";
}
  
if(!empty($mobileno))
  $where[] = "Mobile_No like '%$mobileno%'";
  
if(!empty($bloodgroup))
  $where[] = "Blood_Group like '%$bloodgroup%'";

$query .= implode(' and ', $where);

$result = mysql_query($query) or die(mysql_error());
$num = mysql_num_rows($result);
echo $num;
?>

[rch1231]

Hello,

For one thing you need spaces after the commas in you select:

select Emp_Id, First_Name, Last_Name, Gender, Mobile_No, Email_Id, Blood_Group from Employee where

[pritaeas]

For one thing you need spaces after the commas in you select:

That's optional, not required.

[niths]

thanks for ur response..

@pritaeas : In some cases your query can end with an 'and', which is invalid.

i echo the query and the query ends with 'and' which results an error and in some cases with 'where' also.. so how can i remove the last three characters if it is 'and' and last 4 characters if it is where...

[pritaeas]

$query = 'select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_Id,Blood_Group from Employee ';
$where = array ();

if(!empty($empid))      
  $where[] = "Emp_Id like '%$empid%'";
  
if(!empty($empname)) {
  $where[] = "First_Name like '%$empname%'";
  $where[] = "Last_Name like '%$empname%'";
}
  
if(!empty($mobileno))
  $where[] = "Mobile_No like '%$mobileno%'";
  
if(!empty($bloodgroup))
  $where[] = "Blood_Group like '%$bloodgroup%'";

if (count($where) > 0)
  $query .= 'where ' . implode(' and ', $where);

$result = mysql_query($query) or die(mysql_error());
$num = mysql_num_rows($result);
echo $num;

[niths]

if the person enters both the empid and empname values then it should check the both empid and empname in the query but your query checks only emp name..

[pritaeas]

Did you test this ? Because if I enter values for $empid and $empname, the query is generated properly.

[niths]

hushhhh......!! at last i go it. wat i had done is

$query ="select Emp_No, Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_Id,Blood_Group from Employee where";
      
      if($empid!='')
      {
          $query=$query . " Emp_Id like '%".$empid."%' and";
         
      }
      
      if($empname!='')
      {
          $query=$query . " First_Name like '%".$empname."%' and";
          
      }     
      
      if($mobileno!='')
      {
          $query=$query . " Mobile_No like '%".$mobileno."%' and";
          
          //echo $query2;
      }
      
       if($bloodgroup!='')
      {
          $query=$query . " Blood_Group like '".$bloodgroup."%' and";
          //echo $query1;
      }
      
       $txt=$query; 
       $out=preg_split('/\s+/',trim($txt)); 
       if($out[count($out)-1]=='and')
       {
           $query2=substr("$query", 0, -3);
         //  echo $query2;
       }
       if($out[count($out)-1]=='where')
       {
           $query2=substr("$query", 0, -5);
           //echo $query2;
       }