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
help me to extract results from an array by a query
Moderator: General Moderators
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: help me to extract results from an array by a query
Your query looks fine. What's wrong?
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: help me to extract results from an array by a query
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
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
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: help me to extract results from an array by a query
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: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
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=3mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.