Select multiple keywords/wildcards from one field

[xuykin]

Hi Guys,

 

I have the following questions:

 

1. Table - 'Vendor_Products'. Table has the following fields: `IMAGEURL`, `SKU`, `CURRENCY`, `PRICE`, `BUYURL`, `NAME`

 

2. 'NAME' Field contains a string that has product name, model number and it's description and type of the product

 

3. Need to restrict the product only to certain type and subtype (for example, want the query to show only airplane that is powered only by gas/nitro, leave out electric), since the field contains also rows that list: car (gas or electric), trucks (gas or electric), boats (gas or electric), airsoft rifles (gas and electric), airplanes (gas and electric: sub as kit airplanes and trainer), helicopters (gas or electric)

 

4. Problem: results from my query lists all other products beside the restricted to

 

Here is two different versions of query that failed to deliver the desired results:

 

1. $query = 'SELECT DISTINCT `IMAGEURL`, `SKU`, `CURRENCY`, `PRICE`, `BUYURL`, `NAME` FROM 'Vendor_Products' WHERE ((`NAME` LIKE \'%plane%\') OR (`NAME` LIKE \'%gas%\')) AND `PRICE`>35 ORDER BY RAND()

LIMIT 0, 18 ';

 

 

2. $query = 'SELECT DISTINCT `IMAGEURL`, `SKU`, `CURRENCY`, `PRICE`, `BUYURL`, `NAME` FROM 'Vendor_Products' WHERE (`NAME` RLIKE \'plane\' || `NAME` RLIKE \'nitro\')&& `NAME` NOT RLIKE \'electric\' && `NAME` NOT RLIKE \'pistol\' && `NAME` NOT RLIKE \'car\' && `NAME` NOT RLIKE \'racing\'AND `PRICE`>35 ORDER BY RAND()

LIMIT 0, 18 ';

 

 

Please help me to restrict the query results only to products that are airplanes and are powered by gas or nitro

 

 

copy/past from Random list of 'Vendor_Products' 'NAME' field:

 

 

Hydrofoam Kit RC Airplane Boat (Balsa Wood)

 

Wholesale Case (4 Units) VB M14 Spring Gun Sniper Rifle M1 Grand Airsoft Tri-Rail Mount, FPS 390, Ai

 

Clearance Item - Winner Full Function Electric RC Helicopter 3 CH With Double Blades

 

RC Electric Helicopter Shogun 400 V2 ARF Package Deal

 

RC Killer Whale Electric Dual Motor Speed Boat

 

Yakovlev Yak 54 ARF 87 Inch Aerobatic Nitro Gas RC Airplane (****THE QUERY TO PICK ONLY THIS PRODUCT FROM THE LIST****)

PACKAGE 3 Silverlit Gyrotor RC Electric RTF Micro Helicopter Package Deal (Buy 2 Get 1 FREE!)

 

RAH-66 Comanche 3CH Electric RTF RC Helicopter

 

220 Salangane 2CH RTF Electric RC Airplane

 

Syma Bell 206 3CH RTF RC Helicopter

 

Syma S006 Alloy Shark 3CH RTF Electric RC Helicopter

 

UTG Sport Ultimate Tactical Gear 10 Piece Complete Kit Black Airsoft Gun Accessory

 

Monster Jammerz Electric RTR RC Monster Truck

[jdavidbakr]

I'd start by breaking up your query into pieces and gradually adding conditions, i.e. start with the 'plane' and then add 'nitro' and then add the price condition.

 

But you are doing this the hard (and slow) way - you might consider adding some other tables to link against, i.e.

 

Product |-|| Product-To-Attribute ||-| Attribute

 

where Attribute has the attributes you'll want to search against ('plane' and 'nitro') and you'd then run a query joining the product and product-to-attribute tables with the where clause limiting the attribute id.  Doing wildcard searches at the beginning of the string eliminates any usage of indexes and therefore requires the db to examine every row; not a big problem if you have a small number of rows, but if you get a large table it could become very slow.