PHP / MYSQL search feature issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cgoasduff
Forum Newbie
Posts: 6
Joined: Thu Jun 28, 2007 3:21 am

PHP / MYSQL search feature issue

Post by cgoasduff »

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

Code: Select all

<?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);
?>
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

replace

Code: Select all

isset()
in your if-clauses with

Code: Select all

!empty()
e.g.

Code: Select all

if  (!empty($_GET['select_speciality']))
Also would be helpful to see what the query looks like after you've generated it.
cgoasduff
Forum Newbie
Posts: 6
Joined: Thu Jun 28, 2007 3:21 am

Post by cgoasduff »

Hi Patrick

I'll try what you suggested


To answer your question, 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.


Code: Select all

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
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

cgoasduff wrote:

Code: Select all

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
That's what I thought. Try what I suggested above and see if that works for you.
cgoasduff
Forum Newbie
Posts: 6
Joined: Thu Jun 28, 2007 3:21 am

Post by cgoasduff »

Hi Patrick

Thank you so much, it does works fine.

If you are around Edinburgh one day, ping me an email and I;ll buy you a beer ( or two !)

Regards

Chris
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Thanks, appreciated :)
Post Reply