Page 1 of 1

help me to extract results from an array by a query

Posted: Mon Mar 15, 2010 2:03 pm
by iamroming
ihave three tables 1)maincategories 2)subcategories and 3)products and one of the field in product table is subcatid(varchar) in which iam inserting all the subcateogies to which that product belongs to

The design of the product table is below
productid¦¦ productname ¦¦ maincatid ¦¦ subcatid
1¦¦ Dell¦¦ Computers ¦¦ 12,3,5
2¦¦ Hp ¦¦ Computers¦¦ 3,4,40
3¦¦ Nec ¦¦ Netweorking¦¦ 8,5,16
4¦¦ IBM¦¦ GAteways ¦¦ 20,44,9
5¦¦ Mac ¦¦ Computers ¦¦ 1,90,45

My problem is i want to run a query which extacts the records of the product table by giving a where clause in which i will give the sub catid
like below
$sql="select * products where subcatid=3
so that i can extract the two fields
like
productid¦¦ productname ¦¦ maincatid ¦¦ subcatid
1¦¦ Dell¦¦ Computers ¦¦ 12,3,5
2¦¦ Hp ¦¦ Computers¦¦ 3,4,40
Please help me in this

Re: help me to extract results from an array by a query

Posted: Mon Mar 15, 2010 3:24 pm
by AbraCadaver
Your query looks fine. What's wrong?

Re: help me to extract results from an array by a query

Posted: Mon Mar 15, 2010 3:31 pm
by iamroming
sir the query is not fine the subcatid field has array type of elemnents
what i want to extract is if subcatid is 3 it has to see weather 3 is in subcatid(varchar) field and show the product field

Re: help me to extract results from an array by a query

Posted: Mon Mar 15, 2010 3:51 pm
by AbraCadaver
iamroming wrote:sir the query is not fine the subcatid field has array type of elemnents
what i want to extract is if subcatid is 3 it has to see weather 3 is in subcatid(varchar) field and show the product field
I see now. That's a problem with how you've designed your database. Optimally you should have a join table that has a many-to-one relation to products and a many-to-one relation to subcategories:

products
productid|| productname|| maincatid
1|| Dell|| 1
2|| Hp || 1
3|| Nec || 2
4|| IBM|| 3
5|| Mac || 1

products_subcategories
id|| productid|| subcatid
1|| 1|| 12
2|| 1|| 3
3|| 1|| 5

etc...

subcategories
subcatid|| subcatname
12|| something
3|| something else
5|| something 3

etc...

categories
maincatid|| catname
1|| Computers
2|| Netweorking
3|| GAteways

Then something like:

Code: Select all

SELECT * FROM products
LEFT JOIN products_subcategories
ON products.productid=products_subcategories.productid
WHERE products_subcategories.subcatid=3
You will have no end of problems in your current structure.