Page 1 of 1

Alias, Join problem... Thanks!

Posted: Sun Oct 03, 2010 11:49 pm
by sentnel
Hello everyone!

Im new to php mysql programing and Im stuck with something that may be simple for you guys but I cant find the way to obtain a "name value" of a column that stores a "int" that points to another table.

This query does what I need:

Code: Select all

SELECT
    `jos_polnac_armastipo`.`arma`
FROM
    `polnac_db`.`jos_polnac_partediario`
    INNER JOIN `polnac_db`.`jos_polnac_armastipo` 
        ON (`jos_polnac_partediario`.`arma_utilizada` = `jos_polnac_armastipo`.`id`);


returns this:

ARMA DE FUEGO
ARMA DE FUEGO
ARMA BLANCA
OBJETO CONTUNDENTE
ARMA BLANCA
CUCHILLO


Everything fine up to here. The problem comes with the following select, returns this:

2
2
1
6
1
4

Where each number (the #2 is the reference to ARMA DE FUEGO value in the "arma_utilizada" table). I can't get the below select to display the "ARMA DE FUEGO" value instead of the numbers.

Here is the "big" select, where I can only get the "numbers":

Code: Select all

<?php
$SQL = "SELECT
    `jos_polnac_partediario`.`id` as id
    , `jos_polnac_partediario`.`fecha_partediario` as fechaparte
    , `jos_polnac_partediario`.`hora_partediario` as horaparte
    , `jos_polnac_partediario`.`titulo_partediario` as tituloparte
    , `jos_polnac_partediario`.`region_pn` as regionpn
    , `jos_polnac_partediario`.`departamento_pn` as deptopn
    , `jos_polnac_partediario`.`fecha_hecho` as fechahecho
    , `jos_polnac_partediario`.`hora_hecho` as horahecho
    , `jos_polnac_partediario`.`semana_hecho` as semanahecho
    , `jos_polnac_partediario`.`delito` as delitohecho
    , `jos_polnac_partediario`.`direccion_delito` as direcciondelitohecho
    , `jos_polnac_partediario`.`sector_delito` as sectordelitohecho
    , `jos_polnac_partediario`.`arma_utilizada` as armautilizadahecho
    , `jos_polnac_partediario`.`desplazamiento` as desplazamientohecho
    , `jos_polnac_partediario`.`placa_desplazam` as placadesplazahecho
    , `jos_polnac_partediario`.`numero_autores` as numeroautores
    , `jos_polnac_partediario`.`her_arm_fuego` heridasarmfuego
    , `jos_polnac_partediario`.`fallec_arm_fuego` as fallecarmfuego
    , `jos_polnac_partediario`.`her_arm_blanca` as heridasarmblanca
    , `jos_polnac_partediario`.`fallec_arm_blanca` as fallecarmblanca
    , `jos_polnac_partediario`.`her_obj_contun` as heridasobjcontu
    , `jos_polnac_partediario`.`fallec_obj_contun` as fallecobjcontu
    , `jos_polnac_partediario`.`her_acc_trans` as heridasacctrans
    , `jos_polnac_partediario`.`fallec_acc_trans` as fallecacctrans
    , `jos_polnac_partediario`.`vehic_robado_tipo` as vehicrobadotipo
    , `jos_polnac_partediario`.`placa_veh_robado` as placavehicrobado
    , `jos_polnac_partediario`.`chasis_veh_robado` as chasisvehicrobado
    , `jos_polnac_partediario`.`marca_veh_robado` as marcavehicrobado
    , `jos_polnac_partediario`.`color_veh_robado` as colorvehicrobado
    , `jos_polnac_partediario`.`afectado_nombre` as afectadonombre
    , `jos_polnac_partediario`.`afectado_apodo` as afectadoapodo
    , `jos_polnac_partediario`.`afectado_direccion` as afectadodireccion
    , `jos_polnac_partediario`.`afectado_genero` as afectadogenero
    , `jos_polnac_partediario`.`afectado_fecha_nac` as afectadofechanac
    , `jos_polnac_partediario`.`afectado_edad` as afectadoedad
    , `jos_polnac_partediario`.`afectado_cedula` as afectadocedula
    , `jos_polnac_partediario`.`afectado_ocupacion` as afectadoocupacion
    , `jos_polnac_partediario`.`tel_cel_personal` as afectadotelcelpers
    , `jos_polnac_partediario`.`tel_cel_flota` as afectadotelcelflota
    , `jos_polnac_partediario`.`tel_cel_casa` as afectadotelcelcasa
    , `jos_polnac_partediario`.`tel_cel_ofi` as afectadoteltrabajo
    , `jos_polnac_partediario`.`autor_nombre` as autornombre
    , `jos_polnac_partediario`.`autor_apodo` as autorapodo
    , `jos_polnac_partediario`.`autor_direccion` as autordireccion
    , `jos_polnac_partediario`.`autor_genero` as autorgenero
    , `jos_polnac_partediario`.`autor_fecha_nac` as autorfechanac
    , `jos_polnac_partediario`.`autor_edad` as autoredad
    , `jos_polnac_partediario`.`autor_cedula` as autorcedula
    , `jos_polnac_partediario`.`autor_ocupacion` as autorocupacion
    , `jos_polnac_partediario`.`autor_tel_cel_pers` as autortelcelpers
    , `jos_polnac_partediario`.`autor_tel_flota` as autortelflota
    , `jos_polnac_partediario`.`autor_tel_casa` autortelcasa
    , `jos_polnac_partediario`.`autor_tel_cel_ofi` autorteltrabajo
    , `jos_polnac_partediario`.`partediario` as partediario
    , `jos_polnac_partediario`.`observaciones` as partediarioobserva
    , `jos_polnac_partediario`.`preparadopor` as preparadopor
    , `jos_polnac_partediario`.`estatus_caso` as estatuscaso
    , `jos_polnac_partediario`.`cerrado_por` as cerradopor
    , `jos_polnac_partediario`.`fechahora_cerrado` as fechahoracerrado
    , `jos_polnac_partediario`.`supervisoria_zonas` as partesupervizona
    , `jos_polnac_partediario`.`fuente` as fuente
    , `jos_polnac_regionales`.`regional` as regionalesregionalpn
    , `jos_polnac_departamentos`.`departamento` as departamentosdptospn
    , `jos_polnac_supervisorias`.`supervisoria` as supervisupervizona
    , `jos_polnac_delitos`.`delito` as delitosdelito
    , `jos_polnac_sectores`.`sector` as sectoressector
    , `jos_polnac_armastipo`.`arma` as armatipoarma
    , `jos_polnac_desplazamiento`.`desplazamiento` as desplazadesplaza
    , `jos_polnac_genero`.`genero` as generogenero
    , `jos_polnac_fuente`.`fuente` as fuentefuente
FROM
    `polnac_db`.`jos_polnac_partediario`
    LEFT JOIN `polnac_db`.`jos_polnac_regionales` 
        ON (`jos_polnac_partediario`.`region_pn` = `jos_polnac_regionales`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_departamentos` 
        ON (`jos_polnac_partediario`.`departamento_pn` = `jos_polnac_departamentos`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_supervisorias` 
        ON (`jos_polnac_partediario`.`supervisoria_zonas` = 

`jos_polnac_supervisorias`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_delitos` 
        ON (`jos_polnac_partediario`.`delito` = `jos_polnac_delitos`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_sectores` 
        ON (`jos_polnac_partediario`.`sector_delito` = `jos_polnac_sectores`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_armastipo` 
        ON (`jos_polnac_partediario`.`arma_utilizada` = `jos_polnac_armastipo`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_desplazamiento` 
        ON (`jos_polnac_partediario`.`desplazamiento` = `jos_polnac_desplazamiento`.`id`)
    LEFT JOIN `polnac_db`.`jos_polnac_genero` 
        ON (`jos_polnac_partediario`.`afectado_genero` = `jos_polnac_genero`.`id`) AND 

(`jos_polnac_partediario`.`autor_genero` = `jos_polnac_genero`.`id`)
    INNER JOIN `polnac_db`.`jos_polnac_fuente` 
        ON (`jos_polnac_partediario`.`fuente` = `jos_polnac_fuente`.`id`)";
		?>
        
        
     <?php
	 
	$result=mysql_query($SQL); 
	while($fila = mysql_fetch_assoc($result)) {
	echo $fila['armautilizadahecho'];  
	 echo "<br />"; 
	} 
	
	?>


Thanks in advanced!

Re: Alias, Join problem... Thanks!

Posted: Mon Oct 04, 2010 1:51 am
by rahulzatakia
Hello, please try the following code which will display the name. If you still have any query let me know

Code: Select all

    $SQL = "SELECT
        `jos_polnac_partediario`.`id` as id
        , `jos_polnac_partediario`.`fecha_partediario` as fechaparte
        , `jos_polnac_partediario`.`hora_partediario` as horaparte
        , `jos_polnac_partediario`.`titulo_partediario` as tituloparte
        , `jos_polnac_partediario`.`region_pn` as regionpn
        , `jos_polnac_partediario`.`departamento_pn` as deptopn
        , `jos_polnac_partediario`.`fecha_hecho` as fechahecho
        , `jos_polnac_partediario`.`hora_hecho` as horahecho
        , `jos_polnac_partediario`.`semana_hecho` as semanahecho
        , `jos_polnac_partediario`.`delito` as delitohecho
        , `jos_polnac_partediario`.`direccion_delito` as direcciondelitohecho
        , `jos_polnac_partediario`.`sector_delito` as sectordelitohecho
        , `jos_polnac_partediario`.`arma_utilizada` as armautilizadahecho
        , `jos_polnac_partediario`.`desplazamiento` as desplazamientohecho
        , `jos_polnac_partediario`.`placa_desplazam` as placadesplazahecho
        , `jos_polnac_partediario`.`numero_autores` as numeroautores
        , `jos_polnac_partediario`.`her_arm_fuego` heridasarmfuego
        , `jos_polnac_partediario`.`fallec_arm_fuego` as fallecarmfuego
        , `jos_polnac_partediario`.`her_arm_blanca` as heridasarmblanca
        , `jos_polnac_partediario`.`fallec_arm_blanca` as fallecarmblanca
        , `jos_polnac_partediario`.`her_obj_contun` as heridasobjcontu
        , `jos_polnac_partediario`.`fallec_obj_contun` as fallecobjcontu
        , `jos_polnac_partediario`.`her_acc_trans` as heridasacctrans
        , `jos_polnac_partediario`.`fallec_acc_trans` as fallecacctrans
        , `jos_polnac_partediario`.`vehic_robado_tipo` as vehicrobadotipo
        , `jos_polnac_partediario`.`placa_veh_robado` as placavehicrobado
        , `jos_polnac_partediario`.`chasis_veh_robado` as chasisvehicrobado
        , `jos_polnac_partediario`.`marca_veh_robado` as marcavehicrobado
        , `jos_polnac_partediario`.`color_veh_robado` as colorvehicrobado
        , `jos_polnac_partediario`.`afectado_nombre` as afectadonombre
        , `jos_polnac_partediario`.`afectado_apodo` as afectadoapodo
        , `jos_polnac_partediario`.`afectado_direccion` as afectadodireccion
        , `jos_polnac_partediario`.`afectado_genero` as afectadogenero
        , `jos_polnac_partediario`.`afectado_fecha_nac` as afectadofechanac
        , `jos_polnac_partediario`.`afectado_edad` as afectadoedad
        , `jos_polnac_partediario`.`afectado_cedula` as afectadocedula
        , `jos_polnac_partediario`.`afectado_ocupacion` as afectadoocupacion
        , `jos_polnac_partediario`.`tel_cel_personal` as afectadotelcelpers
        , `jos_polnac_partediario`.`tel_cel_flota` as afectadotelcelflota
        , `jos_polnac_partediario`.`tel_cel_casa` as afectadotelcelcasa
        , `jos_polnac_partediario`.`tel_cel_ofi` as afectadoteltrabajo
        , `jos_polnac_partediario`.`autor_nombre` as autornombre
        , `jos_polnac_partediario`.`autor_apodo` as autorapodo
        , `jos_polnac_partediario`.`autor_direccion` as autordireccion
        , `jos_polnac_partediario`.`autor_genero` as autorgenero
        , `jos_polnac_partediario`.`autor_fecha_nac` as autorfechanac
        , `jos_polnac_partediario`.`autor_edad` as autoredad
        , `jos_polnac_partediario`.`autor_cedula` as autorcedula
        , `jos_polnac_partediario`.`autor_ocupacion` as autorocupacion
        , `jos_polnac_partediario`.`autor_tel_cel_pers` as autortelcelpers
        , `jos_polnac_partediario`.`autor_tel_flota` as autortelflota
        , `jos_polnac_partediario`.`autor_tel_casa` autortelcasa
        , `jos_polnac_partediario`.`autor_tel_cel_ofi` autorteltrabajo
        , `jos_polnac_partediario`.`partediario` as partediario
        , `jos_polnac_partediario`.`observaciones` as partediarioobserva
        , `jos_polnac_partediario`.`preparadopor` as preparadopor
        , `jos_polnac_partediario`.`estatus_caso` as estatuscaso
        , `jos_polnac_partediario`.`cerrado_por` as cerradopor
        , `jos_polnac_partediario`.`fechahora_cerrado` as fechahoracerrado
        , `jos_polnac_partediario`.`supervisoria_zonas` as partesupervizona
        , `jos_polnac_partediario`.`fuente` as fuente
        , `jos_polnac_regionales`.`regional` as regionalesregionalpn
        , `jos_polnac_departamentos`.`departamento` as departamentosdptospn
        , `jos_polnac_supervisorias`.`supervisoria` as supervisupervizona
        , `jos_polnac_delitos`.`delito` as delitosdelito
        , `jos_polnac_sectores`.`sector` as sectoressector
        , `jos_polnac_armastipo`.`arma` as armatipoarma
        , `jos_polnac_desplazamiento`.`desplazamiento` as desplazadesplaza
        , `jos_polnac_genero`.`genero` as generogenero
        , `jos_polnac_fuente`.`fuente` as fuentefuente
    FROM
        `polnac_db`.`jos_polnac_partediario`
        LEFT JOIN `polnac_db`.`jos_polnac_regionales`
            ON (`jos_polnac_partediario`.`region_pn` = `jos_polnac_regionales`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_departamentos`
            ON (`jos_polnac_partediario`.`departamento_pn` = `jos_polnac_departamentos`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_supervisorias`
            ON (`jos_polnac_partediario`.`supervisoria_zonas` =

    `jos_polnac_supervisorias`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_delitos`
            ON (`jos_polnac_partediario`.`delito` = `jos_polnac_delitos`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_sectores`
            ON (`jos_polnac_partediario`.`sector_delito` = `jos_polnac_sectores`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_armastipo`
            ON (`jos_polnac_partediario`.`arma_utilizada` = `jos_polnac_armastipo`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_desplazamiento`
            ON (`jos_polnac_partediario`.`desplazamiento` = `jos_polnac_desplazamiento`.`id`)
        LEFT JOIN `polnac_db`.`jos_polnac_genero`
            ON (`jos_polnac_partediario`.`afectado_genero` = `jos_polnac_genero`.`id`) AND

    (`jos_polnac_partediario`.`autor_genero` = `jos_polnac_genero`.`id`)
        INNER JOIN `polnac_db`.`jos_polnac_fuente`
            ON (`jos_polnac_partediario`.`fuente` = `jos_polnac_fuente`.`id`)";
          ?>
           
           
         <?php
       
       $result=mysql_query($SQL);
       while($fila = mysql_fetch_assoc($result)) {
       echo $fila['armatipoarma']; 
        echo "
    ";
       }