Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
Then in PHP (which I guess you're using from your example) do something
like:
// Get every record from the database ($result is your MySQL result from
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
$result = Array();
// Run through each field in the row
foreach ($row as $field => $value)
{
// Split the field into 2 segments split by _
$fieldSplit = explode('_', $field, 1);
// $fieldSplit will be, for example, Array(0 => 'person', 1 =>
'first_name')
$result[$fieldSplit[0]][$fieldSplit[1]] = $value;
// Now you should be able to access the person's first name using
$result['person']['first_name']
}
}
This code may not be perfect as I've just typed it out from memory so it
may take a bit of tweaking.
Thanks,
Andy
Jason Pruim wrote:
>
> On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
>
>> I hate when somebody put in Subject line something like I just did
>> but after 15 minutes to try to be specific just with one short
>> sentence - I gave up. So, you can hate me - I understand (though,
>> help with my problem too) :-)
>>
>>
>> I have let say 3 tables people, organization, addresses. and they are
>> linked to each other with column person_id.
>> to select person by specified person_id it's not big deal, really
>> simple. though, I wonder how can I select records from these three
>> tables but somehow marked which table it belongs?
>>
>> e.g.
>>
>> select p.first_name, p.last_name, p.status, p.date_registered,
>> o.org_id, o.org_name, o.org_department, a.addres1, a.address2,
>> a.city, a.state, a.zip
>> from people p, organization o, addresses a
>> where p.person_id=123 and o.person_id=p.person_id and
>> a.person_id=p.person_id
>>
>> I need somehow, together with result data, info which table data belogs?
>>
>> e.g.
>> not exactly this way but something like this:
>> $data = array(
>> 'people' => array('lamp', 'lists', 1, '2008-12-12'),
>> 'organization' => array(56, 'Lamp List', 'web'),
>> 'addresses' => array('123 Main St.', 'Toon Town', 'NY', '12345')
>> }
>>
>> thanks for any help.
>>
>> -afan
>
>
> It may not be the best option, but when I've done that type of stuff
> in the past, I add another field to the database and call it like
> "List" and put "People" in the people database. and then you could
> just query the field List and display it how ever you needed.
>
>
> --
> Jason Pruim
> japruim@stripped
> 616.399.2355
>
>
>
>
