[SOLVED] search with without hyphen

[vinpkl]

hi all

 

In the database some product names have hypens in between names like AC-3A, NB-FL.

 

So in my search form if somebody enters keyword like AC3A, NBFL then these products doesnt show in results.

 

I want that these product should get show on both the keywords AC-3A and also AC3A.

 

With string replace i can remove hyphen from the keyword entered but how can i remove hyphen from the product name that is in database because in search query syntax we always write the field name not the product name.

 

$keyword=$_REQUEST['keyword'];
$result = mysql_query ("SELECT * FROM product_table WHERE product_name LIKE '%$keyword%'");

 

vineet

[suresh64633]

Hi,

Try to remove hypens with REPLACE() function in Mysql.

Query will be like this:

"SELECT * FROM product_table WHERE product_name = REPLACE(product_name,'-','')"

 

thanks

[vinpkl]

Hi suresh

 

so will search query be like this

"SELECT * FROM product_table WHERE product_name = REPLACE(product_name,'-','') and product_name LIKE '%$keyword%'"

 

vineet

 

Hi,

Try to remove hypens with REPLACE() function in Mysql.

Query will be like this:

"SELECT * FROM product_table WHERE product_name = REPLACE(product_name,'-','')"

 

thanks

[suresh64633]

Hi,

 

 

"SELECT * FROM product_table WHERE product_name = REPLACE($keyword,'-','') or product_name LIKE '%$keyword%'"

 

thx

 

[vinpkl]

hi suresh

 

this query doesnt show any result

$result = mysql_query ("SELECT * FROM product_table WHERE product_name = REPLACE($keyword,'-','') or product_name LIKE '%$keyword%'");

 

vineet

 

Hi,

 

 

"SELECT * FROM product_table WHERE product_name = REPLACE($keyword,'-','') or product_name LIKE '%$keyword%'"

 

thanks

 

[suresh64633]

Hi,

Its working fine[tested]. Can you give some data and table structure you have.

Thanks

 

[vinpkl]

hi suresh

 

$keyword is the name of the search form input field.

 

<input name="keyword" id="keyword" type="text" value="Enter Product Name" onfocus="this.value=''" class="search" />

 

"product_name" is the name of the field in "product_table" in database

 

This is the code i m using

<?
if(isset($_REQUEST['keyword']))
{			
$keyword=$_REQUEST['keyword'];
$result = mysql_query ("SELECT * FROM product_table WHERE product_name = REPLACE($keyword,'-','') or product_name LIKE '%$keyword%'");
if(mysql_num_rows($result)>0)
{
while($row=mysql_fetch_array($result))
{
$p_name=$row['product_name'];
echo "<tr>";
echo "<td valign=top>". $row['product_name'] ."</td>";
echo "</tr>";
}
}
?>

 

please tell if any other information is required.

 

vineet

 

Hi,

Its working fine[tested]. Can you give some data and table structure you have.

Thanks

 

[suresh64633]

Hi,

 

Query seems to be right.Is query giving any error or returning no values from the table.

 

Thanks

[vinpkl]

hi suresh

 

the query is returning no values from table.

 

means zero result is showing.

 

if i am not using replace function then

$result = mysql_query ("SELECT * FROM product_table WHERE product_name LIKE '%$keyword%'");

 

then results are showing. Is replace function any version dependent of mysql.

 

vineet

 

Hi,

 

Query seems to be right.Is query giving any error or returning no values from the table.

 

Thanks

[vinpkl]

hi suresh

 

this is the error i m getting

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in E:\xampp\htdocs\custm\search.php on line 101

 

vineet

[suresh64633]

Hi,

Replace is not version dependent.

Try to run the query in Mysql.

thanks

[vinpkl]

Hi suresh

 

i ran the query in mysql and recived error

SELECT * FROM product_table WHERE product_name = REPLACE(AC-3A,'-','') or product_name LIKE '%$keyword%';

 

Error i received is #1054 - Unknown column 'AC' in 'where clause'

 

vineet

 

 

 

Hi,

Replace is not version dependent.

Try to run the query in Mysql.

thanks

[suresh64633]

Hi,

  Correct the query like this:

 

SELECT * FROM product_table WHERE product_name = REPLACE('AC-3A','-','') or 
product_name LIKE '%AC-3A%';

 

thanks

[vinpkl]

hi suresh

 

this query showed result corectly without any error.

 

so what do i need to do in php. why it was not showing results in php.

 

vineet

 

Hi,

  Correct the query like this:

 

SELECT * FROM product_table WHERE product_name = REPLACE('AC-3A','-','') or 
product_name LIKE '%AC-3A%';

 

thanks

[suresh64633]

Hi

Can u try with this:

$result = mysql_query ("SELECT * FROM product_table WHERE product_name = REPLACE('".$keyword."','-','') or product_name LIKE '%$keyword%'");

thanks

[vinpkl]

hi suresh

 

with the query i m not getting any error.

 

Its showing result with AC-3A but its not showing result with AC3A.

 

vineet

 

Hi

Can u try with this:

$result = mysql_query ("SELECT * FROM product_table WHERE product_name = REPLACE('".$keyword."','-','') or product_name LIKE '%$keyword%'");

thanks

[Zane]

Why don't you strip the hyphens BEFORE your run the query..

 

you know...run the query with an unhyphenated $keyword...    EDIT-> ALSO

please don't ask me how because there are at least 489 different ways to skin this cat.

[vinpkl]

hi zanus

 

In the starting of my post i have already written that i can strip the hyphens from keyword entered using str_replace.

 

the question i asked was how to remove the hyphen from the product name already in the database while calling query.

 

i dont want to remove the hyphen from the database. it should be there in database but while calling the query it should remove hyphen from product name.

 

 

vineet

 

Why don't you strip the hyphens BEFORE your run the query..

 

you know...run the query with an unhyphenated $keyword...

please don't ask me how because there are at least 489 different ways to skin this cat.

[Zane]

SELECT * FROM product_table WHERE

TRIM("-" FROM `product_name`) LIKE "' . $keyword . ''');

 

maybe?... untested

[vinpkl]

hi zanus

 

this doesnt works. shows no result.

 

vineet

 

SELECT * FROM product_table WHERE

TRIM("-" FROM `product_name`) LIKE "' . $keyword . ''');

 

maybe?... untested

[Zane]

I forgot the percentage signs...but I still doubt it will work...

doesn't hurt to try it anyway though

 

SELECT * FROM product_table WHERE

TRIM("-" FROM `product_name`) LIKE "%' . $keyword . '%'');

 

it should work without them anyway...if that's all you have in that field.

 

Did you try it in something like phpmyadmin or did you just change your php script

[fenway]

Yeesh... echo the actual query you produce.

[vinpkl]

hi fenway and zanus

 

this is the query

$query="SELECT * FROM product_table WHERE TRIM("-" FROM `product_name`) LIKE "%' . $keyword . '%'';

$result=mysql_query($query);

echo $query;

 

This is the error

Warning: Division by zero in E:\xampp\htdocs\custm\search.php on line 105

Warning: Division by zero in E:\xampp\htdocs\custm\search.php on line 105
0
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in E:\xampp\htdocs\custm\search.php on line 116

 

vineet

 

Yeesh... echo the actual query you produce.

[Zane]

as for this

$query="SELECT * FROM product_table WHERE TRIM("-" FROM `product_name`) LIKE "%' . $keyword . '%'';

 

you have the quotes backwards

it should be

 

$query="SELECT * FROM product_table WHERE TRIM('-' FROM `product_name`) LIKE '%" . $keyword . "%'";

 

as for that query itself...I'll just let fenway chop that one up since I suck at that sort of thing anyway

[vinpkl]

HI zanus

 

now the error is gone but the no result is found.

 

this the query echo

SELECT * FROM product_table WHERE TRIM('-' FROM `product_name`) LIKE '%AC3A%'

 

vineet

 

as for this

$query="SELECT * FROM product_table WHERE TRIM("-" FROM `product_name`) LIKE "%' . $keyword . '%'';

 

you have the quotes backwards

it should be

 

$query="SELECT * FROM product_table WHERE TRIM('-' FROM `product_name`) LIKE '%" . $keyword . "%'";

 

as for that query itself...I'll just let fenway chop that one up since I suck at that sort of thing anyway