help me to extract results from an array by a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

help me to extract results from an array by a query

Post 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
User avatar
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

Post by AbraCadaver »

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

Post 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
User avatar
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

Post 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.
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.
Post Reply