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
japr...@raoset.com
616.399.2355
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
