Using an array to put into where condition MySQL query

Question

I am using a wordNet database for my website and I was looking for the synonym.

The scenario is

The website is an e-commerce website. There is a search box for the user. Once the user key in the keyword to search, the system will directly find a synonym based on the user input and match it with my own database and show results. So, for example, in my database, there is just item with name microwave. But after I get the synonym for the microwave, there is another synonym based on the microwave, such as oven.

So, what I want is, the system will display the result of microwave even though the user type in "oven", because basically these two things are the same.

I was thinking to put into an array for the first synonym result from the wordNet database. But, the problem is how can I display the result based on the synonym?

i was thinking about query like this. This query is to get the data from my own database and use the list of data retrieved from the wordnet database

Select the information needed
from the database table
where category.name LIKE (the array of result from wordnet database)

Am I in right way?

Please note that I have succeed retrieved the synonym from the wordnet database. The problem is how can I use this list of synonyms retrieved from the wordnet database to produce data that is from my database?

Can somebody please guide me?

Any help given is highly appreciated. Thanks

We are going to need more details on the table structure of the wordNet database and your own product database. What columns do they have? Please edit your question to include that. Edit yes, you are thinking in the right direction but instead of LIKE, use IN followed by a list of synonymous category names. — nicolaus-hee
– nicolaus-hee, Commented Apr 26, 2015 at 12:30
SELECT fields FROM products_table WHERE category IN ("ovens","microwaves","kitchen equipment"). This of course requires that your products_table contains a column called category. If you don't have that in place, yet, you shouldn't create it that way but rather have a dedicated "categories" table with a category_id, category_name and another table connecting category_ids with product_ids so that one product can be part of more than one category. — nicolaus-hee
– nicolaus-hee, Commented Apr 26, 2015 at 12:37
yes sir. basically I have category table that consist of categoryID and category name. There is also another item table that consist of categoryID. so Basically If I want to retrieve the category from particular item. I need to join these 2 tables first. I am in right way right sir? — Foster
– Foster, Commented Apr 26, 2015 at 12:39
Yes, you are. Your SELECT query should be along these lines: SELECT * FROM products, categories, product_categories WHERE products.product_id = product_categories.product_id AND product_categories.category_id = categories.category_id AND categories.category_name IN ("category1","category2","category3") -- the category1, 2, 3 being what you retrieved from the wordNet database. — nicolaus-hee
– nicolaus-hee, Commented Apr 26, 2015 at 12:43

nicolaus-hee · Accepted Answer · 2015-04-26 13:10:18Z

1

Let's say that $array contains the synonymous categories from wordNet. To turn that into a string which you can use in your query, do this:

$category_string = '"' . implode('","',$array) . '"';

This will turn your array into a string and separate the elements by ",". The string will have a " at the end and the beginning, too, so it can be inserted into the SQL IN statement.

Your SQL command needs to JOIN the three tables (products, categories and product_categories) together and set a WHERE filter for the desired category names. The SELECT command should look similar to this:

SELECT * FROM products, categories, product_categories WHERE products.product_id = product_categories.product_id AND product_categories.category_id = categories.category_id AND categories.category_name IN (INSERT CATEGORY NAMES)

If your SELECT command was to be run with PHP, it would have to look like this:

$sql = 'SELECT * FROM products, categories, product_categories WHERE products.product_id = product_categories.product_id AND product_categories.category_id = categories.category_id AND categories.category_name IN (' . $category_string . ')';

answered Apr 26, 2015 at 13:10

nicolaus-hee

7871 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Foster Over a year ago

Thanks for you clearest explanation and guide. I really appreciate your help Sir! It is really helpful and descriptive on each step. Thanks!

Andrew Larsen · Accepted Answer · 2015-04-26 12:39:44Z

You could create two tables.

One containing categories and one containing sub-categories and you make a relation between them in your query.

Example

We have a table called "category" with fields: id (int, primary key), name (varchar)

In this table we have a row with id = 1, name = oven

We have a second table called "subcategories" with fields: id (int, primary key), name (varchar), category (int)

In this table we have a row with the id = 1, name = microwave, category = 1

You can then do a query like this:
SELECT category.name as catname, subcategories.name as subcatname FROM category, subcategories WHERE category.id = subcategories.id AND subcategories.name LIKE '%crowav%';

This would return the following:
catname: oven subcatname: microwave

Collectives™ on Stack Overflow

Using an array to put into where condition MySQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related