Is there a way to put a subquery into a single, comma seperated field?

[tibberous]

I have two tables, users and phoneNumbers.

 

A user can have any amount of phone numbers.

 

I was to select all of a users phone numbers for a user, as a comma separate field. So, if Joe was my users name, and have 3 phone numbers, my recordset would look like:

 

id: 4

name: Joe

phoneNumbers: 555-5555, 567-7890, 754-9000

 

I was thinking something like:

 

select `id`, `name`, (select --- from phoneNumbers where phoneNumbers.owner=users.id) as `phoneNumbers`from users where `name`='Joe'

 

 

[trq]

Why wouldn't you simply store a new record for each phone number?

[tibberous]

I am, I'm trying to combine them into one record when I do the select - Otherwise, I have to do a query, then do another query for each result, to get the phone numbers.

[trq]

I am, I'm trying to combine them into one record when I do the select - Otherwise, I have to do a query, then do another query for each result, to get the phone numbers.

 

No you don't. Just do one query that gets all phone-numbers for a user.

[tibberous]

Yeah, so if I have 100 users, I end up doing 101 queries.

 

$result = mysql_query("select `id`, `name` from users");

while($user = mysql_fetch_assoc($result)){
$sresult = mysql_query("select * from phoneNumbers where owner=".$user['id']); // gets called 100 times
// loop through phone numbers
}

 

Basically what I am trying to do is like a subquery, but where I turn several returned rows into one aggregate result.

[trq]

You need to look into sql JOIN's. You can easily get the data you want in one query.

[fenway]

 

select u.id, u.name from users AS u inner join phoneNumbers as p on ( p.owner = u.id )

[tibberous]

I could use joins, but what I was looking for is a way to get a result set like:

 

id - name - phoneNumbers

1 - Dave - 555-555-5555, 555-555-5555

2 - Bob - 555-555-5555, 555-555-5555, 555-555-5555

 

So basically take and return multiple rows as a single value, almost like the count function.

[emopoops]

i use someting similar for tags. and then i take the field and put them into an erray seperated by in my case  comma space

$tagsuh  = $row[tag];
$seperatetags = explode(", ", $tagsuh);
?>
<i><a href="search.php?tag=<?php echo $seperatetags[0]; ?>"><?php echo $seperatetags[0]; ?></a>,
<a href="search.php?tag=<?php echo $seperatetags[1]; ?>"><?php echo $seperatetags[1]; ?></a>,
<a href="search.php?tag=<?php echo $seperatetags[2]; ?>"><?php echo $seperatetags[2]; ?></a>,
<a href="search.php?tag=<?php echo $seperatetags[3]; ?>"><?php echo $seperatetags[3]; ?></a><?php } ?></i>