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);
?>