chrisduff Posted June 28, 2007 Share Posted June 28, 2007 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); ?> Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted June 28, 2007 Share Posted June 28, 2007 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 Quote Link to comment Share on other sites More sharing options...
chrisduff Posted June 28, 2007 Author Share Posted June 28, 2007 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 Quote Link to comment Share on other sites More sharing options...
chrisduff Posted June 28, 2007 Author Share Posted June 28, 2007 Hi Huggie It now works, thanks for your help. ReplacePHP isset() with !empty() e.g. PHP if (!empty($_GET['select_speciality'])) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.