0

I have 4 tables (75 columns each table approx) but they share columns with the same name:

Table Master_Producto

Master_Product.PD_Prof_21

Table Product_Structure

Product_Structure.PD_Prof_21

I am doing left join; what happens is that when it bring them to the backend (php) they are overwritten ... expected 2 PD_Prof_21 but in php only one is retrive.

based on this solution (which does not solve my scenario, becouse i have around 300 columns) https://stackoverflow.com/a/10530252/4717133

I'm not convinced by the idea of using aliases with more than 300 columns ... I know that the aliases in the columns would be something like:

SELECT Columns as column1 ... // this would be very heavy to do for each column ...

This other option, does not separate the tables in the resultset

SELECT 
Master_Product.*,
Product_Structure.*,
Supplier_Product.*,
Product_Price.*,

or at least in php I have problems, it turns out that of the 300 columns I only receive those that are not repeated, if a column is repeated, it only shows me the last one with the last value.

First Doubt

So I was wondering if there is any method in mysql that allows adding a prefix automatically to all columns in a resultset query, an example:

SELECT PREFIX ('Master_', Master_Product. *), PREFIX ('Structure_', Product_Structure. *) ...

and in PHP access to:

Master_PD_Prof_21
Structure_PD_Prof_21

PHP script used to retrieve the information:

Connections

function Open_Con_DB2($dbUsing) {
        $dblink = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASS, DB_PRE . $dbUsing);
        
        if ($dblink) {
            $dblink->query("SET NAMES 'utf8'"); # Enlace de Coneccion UTF-8
            return $dblink;
        } else {
            #No conviene mostrar errores internos
            #Cámbialos por mensajes personalizados en producción
            die( 'Error de Conexión ('
                 .mysqli_connect_errno(). ') '
                 . mysqli_connect_error()
                );
            return null;
        }
    }

Get Information.

function BDquery2($dbquery, $dbUsing) {
        $dblink = $this->Open_Con_DB2($dbUsing);
        if ($dblink) {
            if ( $datos = mysqli_query($dblink, $dbquery) ) {
                $outPut=array();
                while ($row = mysqli_fetch_assoc($datos)) {
                    $outPut[]=$row;
                }
            } else {
                $outPut['error']=$this->errorDB($dblink);
            }
            $this->Close_Con_DB($dblink);
        } else {
            $outPut['error']='No hay conexión';
        }
        return $outPut;
    }

Query Executed: (sorry but the database is in Spanish XD)

SELECT 
Master_Producto.*, 
Producto_Estructura.*,
Producto_Proveedor.*,
Producto_Precio.*,
FROM Master_Producto
LEFT JOIN(SELECT * FROM Producto_Estructura) AS Producto_Estructura ON ( Master_Producto.Prod_Code = Producto_Estructura.Prod_Code AND Master_Producto.Prod_PF = Producto_Estructura.Prod_PF)
LEFT JOIN(SELECT * FROM Producto_Proveedor) AS Producto_Proveedor ON ( Master_Producto.Prod_Code = Producto_Proveedor.Prod_Code AND Master_Producto.Prod_PF = Producto_Proveedor.Prod_PF)
LEFT JOIN(SELECT * FROM Producto_Precio ORDER BY Prod_DateUpd DESC) AS Producto_Precio ON ( Master_Producto.Prod_Code = Producto_Precio.Prod_Code AND Master_Producto.Prod_PF = Producto_Precio.Prod_PF)

Second Doubt

the strange thing is that PHPmyadmin if it shows me the complete results ... it displays 1 section for each table ... but I can't do it myself in php. This example is the result of executing the same query tube that cropping it in images so that they appreciate the repetition of columns.

enter image description here

3
  • I am currently seeking to answer the second Doubt. Oct 4 at 17:41
  • 300 columns is seriously abnormal. You will have to do the aliasing for your odd query.
    – Rick James
    Oct 5 at 0:14
  • i solve it... and this is not my database design... ok... Oct 5 at 0:48
0

I see no alternative but to create an appropriate solution for the analysis of the result. honestly that the database has a bad design is not an excuse not to give it a solution ... and the best solution is to work with the data in the backend:


    // Conection to database
    function Open_Con_PDO($dbUsing){
        try {
            $conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_PRE . $dbUsing, DB_USERNAME, DB_PASS);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->exec("set names utf8");
            return $conn;
        } catch (PDOException $e) {
            die( $e->getMessage());
        }
    }
    
    // function to excute the query
    function BDquery2($dbquery, $dbUsing) {
       $dblink = $this->Open_Con_PDO($dbUsing);
       $resultset = $dblink->prepare($dbquery);
       $resultset->execute();
       $outPut=[];
       $rowc =0;
       while ($row = $resultset->fetch(PDO::FETCH_NUM)) {
            $outPut[$rowc]=[];
            foreach($row as $key => $value){
                $outPut[$rowc][$key]=[
                    'ColumnName'=>$resultset->getColumnMeta($key)['name'],
                    'value'=>$value
                ];
            }
            $rowc++;
       }
       return $outPut;
    }

    // USage Example:
    $dbquery = "SELECT 
Master_Producto.*, 
Producto_Estructura.*,
Producto_Proveedor.*,
Producto_Precio.*,
FROM Master_Producto
LEFT JOIN(SELECT * FROM Producto_Estructura) AS Producto_Estructura ON ( Master_Producto.Prod_Code = Producto_Estructura.Prod_Code AND Master_Producto.Prod_PF = Producto_Estructura.Prod_PF)
LEFT JOIN(SELECT * FROM Producto_Proveedor) AS Producto_Proveedor ON ( Master_Producto.Prod_Code = Producto_Proveedor.Prod_Code AND Master_Producto.Prod_PF = Producto_Proveedor.Prod_PF)
LEFT JOIN(SELECT * FROM Producto_Precio ORDER BY Prod_DateUpd DESC) AS Producto_Precio ON ( Master_Producto.Prod_Code = Producto_Precio.Prod_Code AND Master_Producto.Prod_PF = Producto_Precio.Prod_PF)";


    $result = BDquery2($dbquery,'database_name');

    var_dump($result);


output example:

$result=[
    10=>[
        'ColumnName'='Prod_D_1',
        'value'=21
    ],
    ...
    24=>[
        'ColumnName'='Prod_D_1',
        'value'=1
    ],
];

There is nothing written in pieda the function that obtains the data offers a structure that may not solve other complex scenarios but it is a base for those who find this post to have a starting point if they face the duplication / collision of column names.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.