Search DB using multiple search criteria on web form ( AND )

[chrisduff]

Hello there !

 

I am having a bit of a headeach with the following.

 

I have a mysql database with a table with fields ( surname, company, speciality etc..)

I created a simple form so people can search using criterias ( a search field which query all fields , a drop down with the list of surname, drop down with the list of speciality, drop down with a list of countries ).

 

My system works perfectly well as long as I use only one criteria, However if I select "Plumber" as the speciality and "Scotland" as country, I will get all the entries for Scotland ( even if the speciality is not "plumber") and I will get all the entries for "Plumber" ( even if the coutry is not "Scotland").

 

I used Dreamweaver and I coded my bit of PHP here is the section of code:

$colname_RS_search_country = "-1";

if (isset($_GET['textfieldSearch'])) {

  $colname_RS_search_country = $_GET['textfieldSearch'];

}

$colname2_RS_search_country = "-2";

if (isset($_GET['textfieldSearch'])) {

  $colname2_RS_search_country = $_GET['textfieldSearch'];

}

$colname3_RS_search_country = "-3";

if (isset($_GET['textfieldSearch'])) {

  $colname3_RS_search_country = $_GET['textfieldSearch'];

}

$colname4_RS_search_country = "-4";

if (isset($_GET['textfieldSearch'])) {

  $colname4_RS_search_country = $_GET['textfieldSearch'];

}

$colname5_RS_search_country = "-5";

if (isset($_GET['textfieldSearch'])) {

  $colname5_RS_search_country = $_GET['textfieldSearch'];

}

$colname7_RS_search_country = "-7";

if (isset($_GET ['select_speciality'])) {

  $colname7_RS_search_country = $_GET ['select_speciality'];

}

$colname6_RS_search_country = "-6";

if (isset($_GET['select_country'])) {

  $colname6_RS_search_country = $_GET['select_country'];

}

$colname8_RS_search_country = "-8";

if (isset($_GET ['select_company'])) {

  $colname8_RS_search_country = $_GET ['select_company'];

}

$colname9_RS_search_country = "-9";

if (isset($_GET['select_surname'])) {

  $colname9_RS_search_country = $_GET['select_surname'];

}

mysql_select_db($database_my_connection, $my_connection);

$query_RS_search_country = sprintf("SELECT * FROM users_info WHERE user_name LIKE CONCAT('%%', %s, '%%') OR email_address LIKE CONCAT('%%', %s, '%%') OR telephone LIKE CONCAT('%%', %s, '%%') OR user_surname LIKE CONCAT('%%', %s, '%%') OR speciality LIKE CONCAT('%%', %s, '%%') OR user_country = %s OR speciality = %s OR firm_company = %s OR  user_surname = %s ORDER BY user_name ASC", GetSQLValueString($colname_RS_search_country, "text"),GetSQLValueString($colname2_RS_search_country, "text"),GetSQLValueString($colname3_RS_search_country, "text"),GetSQLValueString($colname4_RS_search_country, "text"),GetSQLValueString($colname5_RS_search_country, "text"),GetSQLValueString($colname6_RS_search_country, "text"),GetSQLValueString($colname7_RS_search_country, "text"),GetSQLValueString($colname8_RS_search_country, "text"),GetSQLValueString($colname9_RS_search_country, "text"));

$RS_search_country = mysql_query($query_RS_search_country, $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 tried to change the OR by AND but it doesn't work I just have no result if I do so.. Any input would be welcomed

 

Cheers

 

Christophe

[otuatail]

you need to supply better code and SQL

 

SELECT * FROM user where speciality = "Plumber" AND coutry = "Scotland"

 

why the LIKE CONCAT

 

Caution if you use the LIKE as youare doing here, put the kettle on and watch a movie.

wild cards take time especialy in this case.

 

Desmond.

[chrisduff]

This now works, for info, her is the code:

<?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 .= 'user_country 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 ';
}

// 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';


// Perform the search
mysql_select_db($database_my_connection, $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);
?>

Thank you