PHP / MYSQL search feature issue

[chrisduff]

Hi There

 

I have a form which enables to search for users in a database ( kind of directory )

 

* text field where user can enter any keywords ( the field is called: textfieldSearch )

 

* a drop down to select the user's country : ( select_country )

 

* a drop down to select the user's speciality : ( select_speciality )

 

* a drop down to select the user's surname: ( select_surname )

 

* a drop down with the user's company: (select_company )

 

I want the user to be able to use one and any other criteria, or just one criteria.

 

At the moment the AND feature works but I run into problems when using only one criteria.

 

If I search for a surname only , and if the user I am searching for doesn't have a speciality recorded in the database, the result is blank.

 

Basically the search works as long as all the fields in the record are filled with data for a particular user, if a user has one field with no data (ie: no speciality) this user won't be found.

 

I hope my description make sens !

 

 

For information, here is my code ( I am aware of the SQL injection problem but. but this is for a small closed network )

 

Any advised would be welcome, Thanks for looking

 

Chris

 

 

<?php

//  Start  the  SQL  string
$sql  =  'SELECT  *  FROM  users_info  WHERE  ';

//  Add  each  parameter  if  it's  needed
//  General  search
if  (isset($_GET['textfieldSearch']))  {
$sql  .=  '(user_name  LIKE  \'%'.  $_GET['textfieldSearch']  .'%\'  OR  ';
$sql  .=  'email_address  LIKE  \'%'.  $_GET['textfieldSearch']  .'%\'  OR  ';
$sql  .=  'telephone  LIKE  \'%'.  $_GET['textfieldSearch']  .'%\'  OR  ';
$sql  .=  'user_surname  LIKE  \'%'.  $_GET['textfieldSearch']  .'%\'  OR  ';
$sql  .=  'speciality  LIKE  \'%'.  $_GET['textfieldSearch']  .'%\')  AND  ';
}

if  (isset($_GET  ['select_speciality']))  {
$sql  .=  'speciality  LIKE  \'%'.  $_GET  ['select_speciality']  .'%\'  AND  ';
}


if  (isset($_GET['select_company']))  {
$sql  .=  'firm_company  LIKE  \'%'.  $_GET['select_company']  .'%\'  AND  ';
}

if  (isset($_GET['select_surname']))  {
$sql  .=  'user_surname  LIKE  \'%'.  $_GET['select_surname']  .'%\'  AND  ';
}

if  (isset($_GET['select_country']))  {
$sql  .=  'user_country  LIKE  \'%'.  $_GET['select_country']  .'%\'  AND  ';
}


//  Finish  the  SQL  -  1.  Remove  any  ending  AND  or  WHERE
if  (substr($sql,  strlen($sql)  -  strlen('WHERE  '))  ==  'WHERE  ')  {
$sql  =  substr($sql,  0,  strlen($sql)  -  strlen('WHERE  '));
}
if  (substr($sql,  strlen($sql)  -  strlen('AND  '))  ==  'AND  ')  {
$sql  =  substr($sql,  0,  strlen($sql)  -  strlen('AND  '));
}


//  Finish  the  SQL  -  2.  Add  the  order  by
$sql  .=  '  ORDER  BY  user_name  ASC';

print_r($sql);


//  Perform  the  search
mysql_select_db(  $my_connection);
$RS_search_country  =  mysql_query($sql,  $my_connection)  or  die(mysql_error());
$row_RS_search_country  =  mysql_fetch_assoc($RS_search_country);
$totalRows_RS_search_country  =  mysql_num_rows($RS_search_country);
?> 

[HuggieBear]

Give this a try...

 

<?php

//  Start  the  SQL  string
$sql = "SELECT * FROM users_info WHERE 1=1";

//  Add  each  parameter  if  it's  needed
if (isset($_GET['textfieldSearch'])){
   $sql .= " AND ";
   $sql .=  "(user_name LIKE '%" . $_GET['textfieldSearch'] . "%' OR ";
   $sql .=  "email_address LIKE '%" . $_GET['textfieldSearch'] . "%' OR ";
   $sql .=  "telephone LIKE '%" . $_GET['textfieldSearch'] . "%' OR ";
   $sql .=  "user_surname LIKE '%" . $_GET['textfieldSearch'] . "%' OR ";
   $sql .=  "speciality LIKE '%" . $_GET['textfieldSearch'] . "%')";
}

if (isset($_GET['select_speciality'])){
   $sql .= " AND ";
   $sql .= "speciality LIKE '%" . $_GET['select_speciality'] . "%'";
}

if (isset($_GET['select_company'])){
   $sql .= " AND ";
   $sql .= "firm_company LIKE '%" . $_GET['select_company'] . "%'";
}

if (isset($_GET['select_surname'])){
   $sql .= " AND ";
   $sql .= "user_surname LIKE '%" . $_GET['select_surname'] ."%'";
}

if (isset($_GET['select_country'])){
   $sql .= " AND ";
   $sql .=  "user_country LIKE '%" . $_GET['select_country'] . "%'";
}

/*
   ***  NONE OF THIS SHOULD BE NEEDED  ***

//  Finish  the  SQL  -  1.  Remove  any  ending  AND  or  WHERE
if  (substr($sql,  strlen($sql)  -  strlen('WHERE  '))  ==  'WHERE  ')  {
$sql  =  substr($sql,  0,  strlen($sql)  -  strlen('WHERE  '));
}
if  (substr($sql,  strlen($sql)  -  strlen('AND  '))  ==  'AND  ')  {
$sql  =  substr($sql,  0,  strlen($sql)  -  strlen('AND  '));
}

*/

//  Finish  the  SQL  -  2.  Add  the  order  by
$sql .= " ORDER BY user_name ASC";

print_r($sql);

//  Perform  the  search
mysql_select_db($my_connection);
$RS_search_country = mysql_query($sql, $my_connection) or die(mysql_error());
$row_RS_search_country = mysql_fetch_assoc($RS_search_country);
$totalRows_RS_search_country = mysql_num_rows($RS_search_country);

?>

 

I added WHERE 1=1 into the initial SQL, this helps by only needing an " AND " each time, and also removing the need to tidy up the end of the query with all that strlen()/substr() rubbish.

 

Regards

Huggie

[chrisduff]

Hi Huggie

 

I tried what you suggested but it didn't work.

 

Don't know if that helps but, here is a query which doesn't return any results: I selected the user name Cafagna.

 

This user exist on the database, but has the nothing in the data field Speciality. If I was to add data in this field, the search would work.

 

SELECT * FROM users_info WHERE (user_name LIKE '%%' OR email_address LIKE '%%' OR telephone LIKE '%%' OR user_surname LIKE '%%' OR speciality LIKE '%%') AND speciality LIKE '%%' AND firm_company LIKE '%%' AND user_surname LIKE '%Cafagna%' AND user_country LIKE '%%' ORDER BY user_name ASC

 

Cheers

 

Chris

[chrisduff]

Hi Huggie

 

It now works, thanks for your help.

 

ReplacePHP

isset()

with

 

!empty()

e.g.

PHP

if  (!empty($_GET['select_speciality']))