________________________________
From: Andy Shellam <andy-li...@networkmail.eu>
To: Lamp Lists <lamp.li...@yahoo.com>
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...
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
Hi Andy,
the reason I can't use this because fields (columns) in select statement
(p.first_name, p.last_name,...) are actually dynamically created. In my project
different client will select different fields to be shown. 99% will select
first_name, and last_name, but some don't care about date_registered, some will
need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = "p.first_name, p.last_name, o.org_name"
or
$selected_fields = "p.first_name, p.last_name, o.org_name, a.address, a.city,
a.state, a.zip"
or
$selected_fields = "o.org_name, a.address, a.city, a.state, a.zip"
I hope I'm more clear now?
Though, I can do something as you suggested while creating $selected_fields
:-)
Thanks
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
>
>
>
>
