NYCPHP Meetup

[Anirudhsinh Zala]

Apart from good answer of Carlos, I would suggest something different. Instead of just finding answer of your queries, you could try to understand nature of the problem that arose due to improper structure of tables. So below points might be helpful to you in solving your problem in proper way.

=> Table "User" has those kind of fields that hold almost same type of values. I assume (from data given by you from table "User_attributes") "id" and "user_id" both are unique and holds only numeric values. So here you can use only 1 filed say "id" which is auto_increment and primary so it serves purpose of storing "user_id".

=> Probably best way is to merge both tables "User" and "User_attributes" as there is no any good advantage of this kind of normalization.

=> In table "User_attributes" values of field "attribute_value" can be fields itself as it is normal practise (unless you have many kind of attribute names.

=> Hence better structure of your tables can be like below.

Table: Users
Fields: id, name, email, active, title ....

So records in above table can be like:

78, Joe, joe at joe.joe, yes, CEO
79, Sal, Sal at Sal.Sal, no, CTO
....
....

So, if you have table structure like above, you can easily build queries to fetch whatever information you want to. Please note that values which are used in join conditions to fetch results can give best results when they are stored as fields itself. Now if you have above table structure, you can easily build your desired queries. Like below:

# SELECT id, active, email, salutation, first_name, last_name FROM Users ORDER BY id
# SELECT * FROM Users WHERE active='yes' ORDER BY id

Thanks,

Anirudh Zala
(Building coding standards.)


On Thu, 02 Mar 2006 09:36:06 +0530, Carlos A Hoyos <cahoyos at us.ibm.com> wrote:

>
> Two very simple options:
> 1- You can join with the same table multiple times just by giving it
> different alias.
> So for example this following query will get all users id,
>
> SELECT user.user_id, at1.attribute_value as name, at2.attribute_value as
> email,
> FROM users AS user, User_Attributes AS at1,  User_Attributes AS at2
> WHERE user.user_id = at1.user_id
> AND user.user_id = at2.user_id
> AND at1.attribute_name = 'last_name'
> AND at2.attribute_name = 'email'
>
> you get the idea...
>
> 2- You can use subqueries, for example to get emails for all active users:
> select at1.attribute_value
> from User_Attributes AS at1
> where at1.attribute_name = 'email'
> and at1.user_id in
>       select (user_id from User_Attributes AS at2 where  at2.attribute_name
> = 'active' and at2.attribute_value = 'yes')
>
> you get the point...
>
> I know it's none of my business, but maybe you should use a query like that
> one to de-normalize the table, I can't think of a good reason for such
> level of normalization.
>
>
>
> Carlos Hoyos, Tools Architect
> Global Production Services - Tools, ibm.com
> 1133 Westchester Ave, # 2e 524, White Plains, NY 10604
> Phone: 914.642.3569 TieLine: 224.3569
> cahoyos at us.ibm.com
>
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> New York PHP Conference and Expo 2006
> http://www.nyphpcon.com
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
>



-- 
-----------------------------------------------------
Anirudh Zala (Production Manager)
ASPL, http://www.aspl.in
Ph: +91 281 245 1894
arzala at gmail.com
-----------------------------------------------------