List:General Discussion« Previous MessageNext Message »
From:Andy Shellam Date:December 17 2008 8:29pm
Subject:Re: need help with query...
View as plain text  
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
>
>
>
>
Thread
MySQL 5.0.67 on SMPxufeng16 Dec
  • Re: MySQL 5.0.67 on SMPDan Nelson16 Dec
    • RE: MySQL 5.0.67 on SMPxufeng17 Dec
  • Re: MySQL 5.0.67 on SMPewen fortune16 Dec
  • need help with query...Lamp Lists17 Dec
    • Re: need help with query...Jason Pruim17 Dec
      • Re: need help with query...Andy Shellam17 Dec
        • RE: need help with query...Jerry Schwartz17 Dec
          • Re: need help with query...Andy Shellam17 Dec
    • RE: need help with query...Jerry Schwartz17 Dec
  • Re: need help with query...Lamp Lists17 Dec
    • Re: need help with query...Andy Shellam17 Dec
  • Re: need help with query...Lamp Lists17 Dec
RE: MySQL 5.0.67 on SMPxufeng16 Dec
  • Re: MySQL 5.0.67 on SMPJoerg Bruehe16 Dec