List:General Discussion« Previous MessageNext Message »
From:Andy Shellam Date:December 17 2008 8:48pm
Subject:Re: need help with query...
View as plain text  
Hi,

>
> 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"

So just tag "AS table_field_name" to each field when you're building 
your list of $selected_fields - e.g.

$selected_fields = "p.first_name AS person_first_name, p.last_name AS 
person_last_name, o.org_name AS organization_org_name"

You don't have to use the full table name either - for example in the 
following statement, you would then access the data using 
$result['p']['first_name'];

$selected_fields = "p.first_name AS p_first_name, p.last_name AS 
p_last_name, o.org_name AS o_org_name"

This approach is actually easier if you're creating the query 
dynamically, because you don't have to manually type a load of "AS xxx" 
statements after every field.

I've recently done something similar in one of my applications to wrap 
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.

Andy
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