CODEWITHSUNDEEP
phpsearchfull-text-searchfaceted-search
Nithish
Nithish

Reputation: 253

php faceted search

i have table question(mysql with php) which contains question,category(multiple) and subcat(multiple) its belong. for simplification while saving each question i saved the category in comma separated as below

qid    question      catid    subcat
2     question1        2,3,4   5,7

like this i have 1000 of question right now in DB

while coming with faceted search i want to filter out for each category and its corresponding subcat like tree format below

 cat1(5)
   subcat1(3)
   .........
 cat2(24)
 ..........

For this i just implemented in_array while searching for particular cat for each question in for each loop for that question table.The page getting slow now . along with text search also.

is it really good idea or suggest me some right way .

whether i wanna seperate table for category ?

Thanxs, Gobi

Upvotes: 2

Views: 1450

Answers (2)

Konstantin
Konstantin

Reputation: 1

Right way to store relations information in a separate table. So, may be you'll find this structure useful:

question table, contains question info:

CREATE TABLE `question` (`id` int, `question` text);

Table containing category info:

CREATE TABLE `category` (`id` int, `parent_id` int, `category` tinytext);

table, contains relations:

CREATE TABLE `question_category` (`question_id` int, `category_id` int);

With this structure you`ll have big categories depth and big relations number easy.

Upvotes: 0

codaddict
codaddict

Reputation: 455350

Yes the right way to do this is to have separate tables for both category and sub-category as:

Table Category
--------------
qid   cat-id


Table Sub_category
------------------
qid   sub_cat_id

This way your question table reduces to:

Table Question
---------------
id    Question

Upvotes: 1

Related Questions