search form query problem

[vinpkl]

hi all

i m working on search form.
i have a product name "Nokia n97" in database. I m using the below query to search database and show result

$qry="select * from product_table where product_name LIKE '%$model%' "; 

In this query $model is the name of the search box.

The problem is when i type "Nokia n97" in seach box then the result is shown perfectly but when i type "n97 nokia" then no result is found.

Similarly i have "samsung omnia i900 8gb" in the database. if i type "samsung omnia" then the result is shown perfectly but if i type "samsung i900" then no result is found.

what should i write in the query so if any of words match the "product name" in database the result can be shown.

vineet

[bike5]

You could split what the user types in by spaces and have it match at least one of the words.

[vinpkl]

bike5;10918332 wrote:

You could split what the user types in by spaces and have it match at least one of the words.

hi bike5

can you provide me some code help.

vineet

[Bjom]

is this part of a web-application where you have access to the interface part and thus can do processing of the user-input using PHP, or does that have to be strictly SQL?

[vinpkl]

Bjom;10918348 wrote:

is this part of a web-application where you have access to the interface part and thus can do processing of the user-input using PHP, or does that have to be strictly SQL?

hi Bjom

its an online shopping site created in php/mysql.
In the search input box the user enters the product name like nokia n95 or samsung omnia and if the product is in database then the result is displayed.

vineet

[bike5]

Something like this maybe

$where = '';
$search_terms = explode(' ', $_POST['search']);
$x=0;
foreach($search_terms as $term)
{
$term = function_to_clean_value_for_sql($term);
$where .= strlen($term)>2?($x==0?'':' OR ')."product_name LIKE '%{$term}%'":'';
$x++;
}
$qry="select * from product_table where product_name {$where}";

[vinpkl]

bike5;10918371 wrote:

Something like this maybe

$where = '';
$search_terms = explode(' ', $_POST['search']);
$x=0;
foreach($search_terms as $term)
{
$term = function_to_clean_value_for_sql($term);
$where .= strlen($term)>2?($x==0?'':' OR ')."product_name LIKE '%{$term}%'":'';
$x++;
}
$qry="select * from product_table where product_name {$where}";

hi bike5

on using this code i m getting this error

Fatal error: Call to undefined function function_to_clean_value_for_sql() 

vineet

[djjjozsi]

replace the function_to_clean_value_for_sql into mysql_real_escape_string

and try not copy paste the codes, becouse most of the examples are untested.

The previous example is not correct in 100 %.

This might be a better way to build search terms:

<?php

function stripslashes2( $string ) {
    if(get_magic_quotes_gpc()) {
        return stripslashes($string);
    } else {
        return $string;
    }
}


function mres($var)
{
return mysql_real_escape_string(stripslashes2($var));
}


if ( isset( $_POST['search'] ) ) {
    $mainterm = str_replace( array( '\\' , "," , "." , "-" , "+" , "-" , "/" , "'" , '"' , "(" , ")"
            ) , " " , $_POST['search'] );
    $search_terms = explode( ' ', $mainterm );

foreach( $search_terms as $term ) {
    if ( strlen( trim( $term ) ) > 2 )
        $where[] = sprintf( " product_name LIKE '%%%s%%' " , mres( $term ) );
}
}
$qry = "select * from product_table";
if ( !empty( $where ) )
    $qry .= " WHERE " . implode( " OR " , $where );
print $qry;  // test in phpmyadmin
?>

[bike5]

that was a dummy function name, you need to change that name with one you are using. If you are not using one you should be, and for now just try replacing that name with "mysql_real_escape_string"

Edit: ah, posted at the same time as djjjozsi. His example is a lot more indepth to handle all the situations that come up with escaping special characters and such. What I gave you was something simple so you can easily figure out how it was working and the logic behind it.

[vinpkl]

hi djjjozsi

i tested this code and entered my product name in the search box but on printing the qry i get

select * from product_table

as u have inserted the condition that if no name is entered only then it should display all products. but if i m entering the product name then also its showing all the products. Its not concatinating the where condition.

vineet

djjjozsi;10918388 wrote:

replace the function_to_clean_value_for_sql into mysql_real_escape_string

and try not copy paste the codes, becouse most of the examples are untested.

The previous example is not correct in 100 %.

This might be a better way to build search terms:

<?php

function stripslashes2( $string ) {
    if(get_magic_quotes_gpc()) {
        return stripslashes($string);
    } else {
        return $string;
    }
}


function mres($var)
{
return mysql_real_escape_string(stripslashes2($var));
}


if ( isset( $_POST['search'] ) ) {
    $mainterm = str_replace( array( '\\' , "," , "." , "-" , "+" , "-" , "/" , "'" , '"' , "(" , ")"
            ) , " " , $_POST['search'] );
    $search_terms = explode( ' ', $mainterm );

foreach( $search_terms as $term ) {
    if ( strlen( trim( $term ) ) > 2 )
        $where[] = sprintf( " product_name LIKE '%%%s%%' " , mres( $term ) );
}
}
$qry = "select * from product_table";
if ( !empty( $where ) )
    $qry .= " WHERE " . implode( " OR " , $where );
print $qry;  // test in phpmyadmin
?>

[djjjozsi]

$_POST['search'] comes from your form, and you need to rename the search word's textfield name.

(you don't need to leave my previous post in your's.)

i tested with this form:

<form action="" method="post" name="Form">
<input name="search" id="search" type="textfield" value="<?php echo (isset($_POST["search"])?htmlspecialchars($_POST["search"]):"" ) ?>">
<input name="go" type="submit" value="go">
</form>

[vinpkl]

bike5;10918389 wrote:

that was a dummy function name, you need to change that name with one you are using. If you are not using one you should be, and for now just try replacing that name with "mysql_real_escape_string"

Edit: ah, posted at the same time as djjjozsi. His example is a lot more indepth to handle all the situations that come up with escaping special characters and such. What I gave you was something simple so you can easily figure out how it was working and the logic behind it.

hi bike5

as u said i changed it but its showing no result no product.

$where = ''; 
$search_terms = explode(' ', $_POST['model']); 
$x=0; 
foreach($search_terms as $term) 
{ 
$term = mysql_real_escape_string($term); 
$where .= strlen($term)>2?($x==0?'':' OR ')."product_name LIKE '%{$term}%'":''; 
$x++; 
} 
$qry="select * from product_table where product_name {$where}"; 
$result=mysql_query($qry);

have i done something wrong

vineet

[djjjozsi]

just a little basics:

if you see $_POST["model"] in a code,

your textfield in a form looks like:

<input name="model" id="model" type="textfield" value="<?php echo (isset($_POST["model"])?htmlspecialchars($_POST["model"]):"" ) ?>">

[vinpkl]

hi djjjozsi

earlier i was using

<input name="model" id="model" type="text" value="Enter Product Name" onfocus="this.value=''" class="search" style="width:140px; border:1px solid #A1B22D; color:#969696" />

and tested your code, and it was showing all products.

now i used ur <input> code

<input name="model" id="model" type="textfield" value="<?php echo (isset($_POST["model"])?htmlspecialchars($_POST["model"]):"" ) ?>">

and with this also i get the same qry output and its showing all products

select * from product_table

vineet

[djjjozsi]

did you change the model in the rest of the code?

if ( isset( $_POST['model'] ) ) {
    $mainterm = str_replace( array( '\\' , "," , "." , "-" , "+" , "-" , "/" , "'" , '"' , "(" , ")"
            ) , " " , $_POST['model'] );

in my suggestion i've used textfield as the textfield's type, use type="text"

in your form action are you using method="post" ?

[vinpkl]

djjjozsi

oh! i was using method "get". now its showing qry correct.

but the problem is that if i have "nokia n97" in the database and i entered the same "nokia n97" or "n97 nokia" in the search box then "nokia n97" product is not displayed on top. its displayed in between the results.

is it possible to show it on the top as the name matches complete with the product name in database.

or do i have to play with lot of conditions and queries

vineet

[djjjozsi]

you should store the brand and models in two separated fields.

1th: search the inserted word from the table with the original term.
if its results, then list them.

2th if there is no results, use the AND operator in the query.

Run another query to print another results, called: related contents...

Or,
1th step: determine the inserted brand or allow to the user to filter the results with the brands table.

[vinpkl]

hi djjjozsi

as lot of product have already been added in the database so its not possible to again edit the database and add model number separately.

i didnt had any idea that this thing can be too hard in the future while creating the website.

if i type only "n97" then only "nokia n97" product is display that is fine but why it doesnt show the same result on entering "nokia n97" because we are accepting "nokia n97" as two different keyword.

I think i will have to mix 2-3 queries or conditions otherwise the only option left is to change the database structure.

thanks for all the help.

vineet

[Bjom]

Here's a different approach using prepared statement and mysqli:

<?php

$host = '';
$user = '';
$pwd = '';
$db = '';

$userInput = 'n97 nokia';

$searchParams = explode(' ',$userInput);
for ($ic = 0, $ub = count($searchParams); $ic < $ub; $ic++) {
	$searchParams[$ic] = '%' . $searchParams[$ic] . '%';
}
echo $searchParams[0], '<br />';

$sql = 'SELECT myField1, myField2, product_name FROM product_table WHERE product_name LIKE ?';
$type = 's';
for ($ic = 0, $ub = count($searchParams) - 1; $ic < $ub; $ic++) {
$sql .= ' AND product_name LIKE ?';
$type .= 's'; 
}

echo 'sql=' . $sql . '<br />' . 'type=' . $type;
$conn = new mysqli($host, $user, $pwd, $db);
$stmt = $conn->prepare($sql);
// couldn't get the following to run with object-oriented style. Nice mixture ain't it? Any suggestions, anyone?
call_user_func_array('mysqli_stmt_bind_param', array_merge(array($stmt, $type), $searchParams));

$stmt->execute();
$stmt->bind_result($fld1, $fld2, $fld3);

while($stmt->fetch()){
	echo '<br />' . 'myField1: ' . $fld1 . ', myField2: ' . $fld2. ', product_name: ' . $fld3;
} 

$stmt->close();
$conn->close();

?>

[djjjozsi]

I was working with mobiles and their capabilities databases. Its not a big deal to divide one field into two, and build a model / brand fields. You can make a program to help the human resource's wor. If you're making a person database you don't use one field to store the address and postcode. it is called database normilization.

did you try the AND operator in the query builder ?