NYCPHP Meetup

[Peter Sawczynec]

Sorry, another MySQL question on the PHP list, but there are so many great
users here...

I need to get all the active users email and name for personalization.

I need the correct SELECT query addressing two tables that have a one to
many relationship linked by "user_id" like the relationship shown below:

Table: Users
Fields: id   user_id 


Table: User_Attributes
Fields: id   user_id   attribute_name   attribute_value

So in User_Attributes table you see data like so:
89 78 email joe at joe.com
90 78 name joe
91 78 active yes
92 78 title CEO
93 79 email sal at sal.com
94 79 name sal
...
...


I need to get the email and name of all the users where:
attribute_name "active"  = attribute_value "yes"


I'm using this query so far:

SELECT a.user_id, b.attribute_name, b.attribute_value 
FROM users AS a LEFT JOIN User_Attributes AS b 
ON a.user_id = b.user_id 
WHERE b.attribute_name 
IN ('active', 'email', 'salutation', 'first_name', 'last_name') 
ORDER BY a.user_id 


...but this returns a rowset where each user has four rows with an attribute
value in each row.

I need all four attributes from each user to be returned in one neat row.

Additionally, I could use all the non-active users filtered out by the SQL.

Any help here?

Warmest regards,

Peter Sawczynec,
Technology Director
PSWebcode
_Design & Interface
_Ecommerce
_Database Management
ps at pswebcode.com
718.796.1951
www.pswebcode.com