List:General Discussion« Previous MessageNext Message »
From:Lamp Lists Date:December 17 2008 8:40pm
Subject:Re: need help with query...
View as plain text  




________________________________
From: Andy Shellam <andy-lists@stripped>
To: Lamp Lists <lamp.lists@stripped>
Cc: mysql@stripped
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
> 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