php - Mysql dyanmic value as alias -

so following works

$query = "select          a.entity_id,         b.value variable_character,         c.value text,         d.value integer     customer_address_entity      left join customer_address_entity_varchar b          on b.entity_id = a.entity_id     left join customer_address_entity_text c          on c.entity_id = a.entity_id     left join customer_address_entity_int d          on a.entity_id = d.entity_id     order a.entity_id desc     limit 100"; 

however there many values need joined entity in table , right creating own nested array based on new value.

0 =>  array (size=4)   'entity_id' => string '597424' (length=6)   'variable_character' => string 'dave' (length=4)   'text' => string '45 haven rd' (length=11)   'intiger' => string '43' (length=2) 1 =>  array (size=4)   'entity_id' => string '597424' (length=6)   'variable_character' => null   'text' => string '45 haven rd' (length=11)   'intiger' => string '43' (length=2) 2 =>  array (size=4)   'entity_id' => string '597424' (length=6)   'variable_character' => string 'danielson' (length=9)   'text' => string '45 haven rd' (length=11)   'integer' => string '43' (length=2) 3 => etc ... 

im thinking due same key name trying joined , therefore use more dynamic such value such value of b.attribute_type_id. array

array (size=7)   'entity_id' => string '597424' (length=6)   '1' => null   '2' => 'dave'   '3' => 'danielson'   '4' => '45 haven road'   'text' => string '45 haven rd' (length=11)   'intiger' => string '43' (length=2) 

or better: title attributes live in table called eav_attribute , ideal along lines of

$query = "select     a.entity_id,     b.value (select attribute_code eav_attribute attribute_id = b.attribute_id),     c.value text,     ... 

is possible? or going in wrong way?

why using left join instead of inner join? also, if doing dynamic queries. should consider using stored procedures. mount sql statement in variable , execute. here's quick example:

create definer=`yourdbuser`@`localhost` procedure `rtyourprocedurename`(in variable1 int,variable2d varchar(100), sortby varchar(50), startrow int, thissql varchar(5000), out totalrows int)  begin  ###start: mysql dumb , not allow default parameter values, set here:  set @variable1 = ifnull(variable1 ,'0');  set @variable2 = ifnull(variable2 ,'');  set @sortby = ifnull(sortby ,'');  set @startrow = ifnull(startrow ,1);  ###end: mysql dumb , not allow default parameter values    set @htwsql = thissql;    set @htwsql = ' select sql_calc_found_rows straight_join t1.field1 ,t2.field2 ';    if @variable1 = 0  	set @htwsql := concat(@htwsql, ' table1 t1');  else   	set @htwsql := concat(@htwsql,' table2 t2 w inner join table1 t1 ');  end if;    set @htwsql := concat(@htwsql, ' 1 = 1 ');      ### sort order  if rtrim(ltrim(@sortby)) <> ''  	set @htwsql := concat(@htwsql,char(13), ' order ' , cast(@sortby char(50)));  end if;    ### limit records pagination  set @htwsql := concat(@htwsql,char(13), ' limit ', startrow-1 ,',',24);    ### debug generated sql  insert rtlogtmp (log) select concat ('rtyourprocedurename sql = ',@htwsql);      prepare stmt1 @htwsql;     execute stmt1;     deallocate prepare stmt1;    select found_rows() totalrows;    end

p.s.: careful names use alias. may reserved sql syntax.