[Resolved] Keyword Lookups

[Kudose]

hello all.

I basically have a loop that inserts products based on brand name and keywords. My problem is that it isnt picking everything up right.

Let say I have this item:

Brand: Sony
Desc: 15.1" LCD Monitor

I need to be able to enter 15 and LCD and it pick it up.

Here is my current query.

Any help is great!

$brand = $_POST['brand'];
$keyword = $_POST['keyword'];
$sql_wholesale = mysql_query("SELECT * FROM ".$prefix."store_temp WHERE brand='$brand' AND (item_name LIKE '%$keyword%' OR item_name LIKE '$keyword%' OR item_name LIKE '%$keyword')") or die(mysql_error());

[onion2k]

item_name LIKE '%$keyword%' OR item_name LIKE '$keyword%' OR item_name LIKE '%$keyword'

Theres no point in the second two LIKE statements. The first one will match anything like $keyword.

There doesn't appear to be anything else wrong with that code. What does the rest of the script look like?

[Kudose]

Here are the main snippets...

if($_POST['submit']){
	$brand = $_POST['brand'];
	$keyword = $_POST['keyword'];
	if($brand !== ""){
		$sql_wholesale = mysql_query("SELECT * FROM ".$prefix."store_temp WHERE brand='$brand' AND (item_name LIKE '%$keyword%' OR item_name LIKE '$keyword%' OR item_name LIKE '%$keyword')") or die(mysql_error());
		while($s_f = mysql_fetch_array($sql_wholesale)){
			$product = $s_f['product_id'];
	$instock = $s_f['qty_avail'];
	$instock -= 3;
	$description = $s_f['description'];
	$description = mysql_escape_string($description);
	$brand = $s_f['brand'];

//some stuff here

mysql_query("INSERT INTO ".$prefix."store_inventory (product, quantity, description, image, price, title, cat_id, popularity, sale_price, ship_price, instock, brand, msrp, other_cat) VALUES ('$product', '1', '$description', '$image', '$price', '$title', '$cat_id', '$popularity', '$sale_price', '$ship_price', '$instock', '$brand', '$msrp', '$sec_cat_value')");

if(!mysql_error()){
	echo "<center><b>".$title." was entered into the database. ";
	echo "</b></center><br />";
	} else { echo "<center><b>MySQL said: ".mysql_error()."</b></center><br />"; }
  }//end if brand !== ""
}//end while
}

//there is just a form down here with a few input fields.

[Kudose]

My form input fields are just textfields.

So I would enter

Brand: Sony
Keyword: 15 LCD

It picks up nothing.

If I enter

Brand: Sony
Keyword: LCD

It picks up everything like it is supposed too.

Thanks.

🙂

[onion2k]

Originally posted by Kudose
So I would enter

Brand: Sony
Keyword: 15 LCD

It picks up nothing.

If I enter

Brand: Sony
Keyword: LCD

It picks up everything like it is supposed too.

Ahh.. now its clear what the problem is.

When you put "15 LCD" in it does to the database and look for exactly that.. "15 space LCD".. which isn't in the data anywhere, hence no results. You need to search for each word seperately..

$keywords = explode(" ",$keyword);
foreach ($keywords as $k) {
  $i[] = "item_name LIKE '%".$k."%'";
}

$sql = "SELECT * FROM ".$prefix."store_temp WHERE brand='$brand' AND (";
$sql .= implode(" or ",$i);
$sql .= ")";

$sql_wholesale = mysql_query($sql)  or die(mysql_error());

[Kudose]

I knew it had to be something like that, but wasnt sure just what it was.

Thanks a million.

🙂

[Kudose]

One more question.

Now when I do it, everything that has

15 || " || LCD

gets entered. So instead of all 15 " LCD, it enters all 15, all ", all LCD.

How can I fix this so that the string must contain all key words in the array.

Thanks.

$brand = $_POST['brand'];
	$keyword = $_POST['keyword'];
	if($brand !== ""){
		$keywords = explode(" ",$keyword);
		foreach ($keywords as $k) {
 			 $i[] = "item_name LIKE '%".$k."%'";
		}

	$sql = "SELECT * FROM ".$prefix."store_temp WHERE brand='$brand' AND (";
	$sql .= implode(" or ",$i);
	$sql .= ")";

	$sql_wholesale = mysql_query($sql)  or die(mysql_error());

[onion2k]

Change "$sql .= implode(" or ",$i);" to "$sql .= implode(" and ",$i);"