Dealing with multiple rows from JOIN

[eoinoc333]

Hey,

I'm LEFT JOINing two tables.

The tables have a 0-to-many relationship. So a row in Table A may have no, or multiple, related rows in Table B.

If there are many rows in Table B related back to Table A, the result of the JOIN gives a row for each match.

For example:

Table A: Person.
A_id
A_name

Table B: Person's related addresses.
B_id
B_foreign_key_A_id
B_city

If John has multiple addresses, the data returned is on multiple rows, with all the data from Table A being repeated on each row for each match:

A_id...A_name...B_id...B_person_id...B_city 
1......John.....1......1.............Washington 
1......John.....2......1.............Boston

Is there a standard way of dealing with this kind of result in PHP? I'll be reading each row one after the other. Do I have to keep track of what A_id I have already processed to see if I have already dealt with that person in the result set?... I don't want to have objects created for one person. I want the person object to have an array of the cities.

I hope I've explained it ok.

[supermanjnk]

Sounds like you need to store the information in a multi-dimensional array
basically you could do something like this:

$contacts = array();
while ($c = mysql_fetch_object($sqldata)) {
$contacts[$c->A_id][$c->B_city] = $c->B_city;
}

you could then do a something like:

foreach ($contacts as $cid->$cities) {
foreach($cities as $city) {
echo $city;
}
}

[eoinoc333]

Thanks.

I basically ended up doing what you suggested. I check if the contact_id has already been added to my array, and if not, I just add all that info. Then I loop through all the cities and add them to the array $arr[$contact_id][] = $city.