CODEWITHSUNDEEP
phpmysql
Alick Dikan
Alick Dikan

Reputation: 65

Trouble in logic with mysql

I have an online shop of watches. It has a search filter. One criterium of filtering is the material of body of watches. There are lots of unique values of it. For instance: "steel", "steel with gold", "steel with gold plating".

I tried to filter watches by creating an sql LIKE request to filter watches with steel body, gold body. But when searching for watches with golden body it also gives me steel watches with gold plating because it consists the word "gold".

Can you suggest a good solution to solve this problem?

So far I tried this sql statement:

SELECT * FROM `watches` WHERE `material` LIKE "%gold%"

Upvotes: 0

Views: 54

Answers (2)

Layton Everson
Layton Everson

Reputation: 1148

If I were doing this I would create a table called "Material" and add a record for each material available, then store the material id in the watches table instead of the words. (There are a number of reasons this is better.)

Material Table:

id - Material
1 - Gold
2 - Gold Plated
3 - Silver
4 - Steel with Gold

Then in your search you would allow the user to select the materials they are interested in and your sql would look something like this:

Select * from Watches where Material IN (1,4);

This would bring up an watch that is made of Gold or Steel with Gold. And your searches will be faster.

Upvotes: 2

Mollo
Mollo

Reputation: 723

$criterium = "gold";
$query = mysql_query("SELECT * FROM watches WHERE material = '$criterium'");

It should solve your problem. Also check SQL LIKE condition for further queries

Upvotes: 0

Related Questions