PRINT ALL RECORDS OF AN INNER JOIN

Question

PRINT ALL RECORDS OF AN INNER JOIN

Navigation

#1 by (0 votes)

0

I have a little problem and I can not correct the error ... I have this PHP code shown below, in which I do an inner join and what I want to do is pull all the results of the records obtained by means of an array to get them printed in a list way

Try it in two ways one is commented but neither of the two ways work correctly ... I hope you can help me ... GREETINGS AND THANK YOU VERY MUCH !!

    $consulta = "SELECT a.numero_proveedor AS NUMERO_PROVEDOR,a.monto_pago AS MONTO_PAGO,a.fecha_archivo AS FECHA_ARCHIVO,pf.nombre AS NOMBRE,pf.apellido_paterno AS APELLIDO_PATERNO,pf.apellido_materno AS APELLIDO_MATERNO,pf.razon_social AS RAZON_SOCIAL,pf.sucursal AS SUCURSAL,pf.cuenta AS CUENTA,pf.clabe AS CLABE,tf.descripcion AS TIPO_TRANSFERENCIA,b.nombre AS BANCO,d.descripcion AS ESTATUS
FROM tef.archivo a

INNER JOIN tef.proveedores pf ON a.numero_proveedor = pf.numero_proveedor
INNER JOIN tef.tipo_transferencia tf ON pf.id_tipo_transferencia = tf.id
INNER JOIN tef.bancos b ON pf.id_banco = b.id
INNER JOIN tef.estatus_proveedor d ON pf.id_estatus = d.id";
// where b.id=1

$rs = mysql_query ( $consulta)or die (mysql_error()); 
$row5 = mysql_fetch_array($rs);

    $monto             = $row5["MONTO_PAGO"];
    $clabe             = $row5["clabe"];
    $cuenta_prov       = $row5["CUENTA"];
    $nombre            = $row5["nombre"];
    $apellido_paterno  = $row5["apellido_paterno"];
    $apellido_materno  = $row5["apellido_materno"];
    $tipo_transferencia= $row5["TIPO_TRANSFERENCIA"];
    $sucursal          = $row5["SUCURSAL"];


// while ($row6 = mysql_fetch_array($rs)){
    // echo $row6[1];
    // echo $row6[2];
    // echo $row6[3];
    // echo $row6[4];
    // echo $row6[5];

    // }

php phpmyadmin

asked by karina reyes 26.06.2018 в 23:53

source

1 answer

Problem with fourth level height of ExpandableListView android studio Error connecting a program with an Oracle database

score 0 · Accepted Answer

Since, as you say, you do not have an alternative to migrate to mysqli or PDO, I propose the following code.

The same will control the flow, collecting the eventual results in a variable called $arrOutput , which you can use at the end to print the error messages or an array with the data.

Notice that I used mysql_fetch_assoc . This function creates an associative arrangement with the results, and in while you will not be forced to name each column explicitly, since fetch_assoc is already responsible for assigning each value its column name.

This would be the code:

$arrOutput=array(); 
$consulta = "SELECT 
                    a.numero_proveedor AS NUMERO_PROVEDOR,
                    a.monto_pago AS MONTO_PAGO,
                    a.fecha_archivo AS FECHA_ARCHIVO,
                    pf.nombre AS NOMBRE,
                    pf.apellido_paterno AS APELLIDO_PATERNO,
                    pf.apellido_materno AS APELLIDO_MATERNO,
                    pf.razon_social AS RAZON_SOCIAL,
                    pf.sucursal AS SUCURSAL,
                    pf.cuenta AS CUENTA,
                    pf.clabe AS CLABE,
                    tf.descripcion AS TIPO_TRANSFERENCIA,
                    b.nombre AS BANCO,
                    d.descripcion AS ESTATUS
             FROM tef.archivo a
             INNER JOIN tef.proveedores pf ON a.numero_proveedor = pf.numero_proveedor
             INNER JOIN tef.tipo_transferencia tf ON pf.id_tipo_transferencia = tf.id
             INNER JOIN tef.bancos b ON pf.id_banco = b.id
             INNER JOIN tef.estatus_proveedor d ON pf.id_estatus = d.id";
// where b.id=1
$rs = mysql_query($consulta);  //Aquí había un error  .... si no funciona, pásale un segundo parámetro con la variable de conexión: $rs = mysql_query($consulta,$variableConexion);

if (!$rs) {
    $arrOutput["error"]="No se pudo ejecutar la consulta. Error: " . mysql_error();
    exit;
}

if (mysql_num_rows($rs) == 0) {
    $arrOutput["error"]="No se encontraron registros";
}else{
    while ($row = mysql_fetch_assoc($rs)) {
        $arrOutput[]=$row;    
    }
    mysql_free_result($rs); 

}

/*
    *Ocurra lo que ocurra, habrá datos en $arrOutput
    *los cuales podrás imprimir convenientemente
    *el print_r es sólo para ver lo que hay dentro
*/
    print_r($arrOutput);