NYCPHP Meetup

[Rolan Yang]

I don't think you'll get that in a one line query, but you can loop 
through and/or dump the values into an array.

quick and dirty way to print it out (assuming every id has a valid email 
& name):

$result=mysql_query("select attribute_value from User_Attributes order 
by user_id,attribute_name");
while (list($email)=mysql_fetch_row($result) {
    list($name)=mysql_fetch_row($result);
    print "$name $email\n";
}

or you could get fancy, use up some more memory and build an array:

$result=mysql_query("select user_id,attribute_name,attribute_value from 
User_Attributes order by user_id,attribute_name");
while (list($id,$attrib,$value)=mysql_fetch_row($result)) {
    $user[$id][$attrib]=$value;   # builds an array with the data
}
print_r($user);


~Rolan

Peter Sawczynec wrote:
> 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
>
> _______________________________________________
> 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
>
>