Prevent Column Name Collision

Question

I have been working on a series of posts about a library to connect to databases but most of the scenarios I have to resort to some "JOIN" the problem arose when two or more tables had some columns with the same name causing the last one to prevail.

I understand that the problem may lie in a bad database design, but I cannot allow myself to say that it should be redesigned right now, I don't know how much impact a redesign has at this time ... then you have to cover the gap and make sure the patch is good, better than microsoft and windows...

As I have always told you, I am not an expert, but I like to learn and improve continuously, that's why I bring my code to a review on the site.

The code tries to work with queries where it is sought to extract information from tables with JOIN and these do not have any specific parameter, but it avoids the collision of columns with identical names.

I would like to know if this piece of code can have improvements, in its structure and if I have forgotten something (security or standard).

I've gotten to this point:


    // 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
    ],
];

once we have this array / structure, we don't lose the names of the columns or data that usually happens with FETCH_ASSOC.

UPDATE

New Script with last changes:

    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){
                $table = $resultset->getColumnMeta($key)['table'];
                $column = $resultset->getColumnMeta($key)['name'];
                if(!isset($outPut[$rowc][$table])){
                    $outPut[$rowc][$table]=[];
                }
                $outPut[$rowc][$table][$column]=$value;
            }
            $rowc++;
       }
       return $outPut;
    }

Query Example:

SELECT MasterProducts.*, Prod_Profile.*, Prod_Det_Info.*, Prod_Det_Cost.*, Prod_Det_Price.*, Prod_PriceP.*, Prod_Cost_Prom_Men.*, Prod_Price_Net_Hist.* FROM MasterProducts
                    LEFT JOIN (SELECT Prod_Profile.* FROM Prod_Profile) AS Prod_Profile ON (Prod_Profile.Prod_Code = MasterProducts.Prod_Code) AND (Prod_Profile.Prod_PF = MasterProducts.Prod_PF)
                    LEFT JOIN (SELECT Prod_Det_Info.* FROM Prod_Det_Info) AS Prod_Det_Info ON (Prod_Det_Info.Prod_Code = MasterProducts.Prod_Code) AND (Prod_Det_Info.Prod_PF = MasterProducts.Prod_PF)
                    LEFT JOIN (SELECT Prod_Det_Cost.* FROM Prod_Det_Cost) AS Prod_Det_Cost ON (Prod_Det_Cost.Prod_Code = MasterProducts.Prod_Code) AND (Prod_Det_Cost.Prod_PF = MasterProducts.Prod_PF)
                    LEFT JOIN (SELECT Prod_Det_Price.* FROM Prod_Det_Price) AS Prod_Det_Price ON (Prod_Det_Price.Prod_Code = MasterProducts.Prod_Code) AND (Prod_Det_Price.Prod_PF = MasterProducts.Prod_PF)
                    LEFT JOIN (SELECT Prod_PriceP.* FROM Prod_PriceP) AS Prod_PriceP ON (Prod_PriceP.Prod_Code = MasterProducts.Prod_Code) AND (Prod_PriceP.Prod_PF = MasterProducts.Prod_PF)
                    LEFT JOIN (SELECT Prod_Cost_Prom_Men.* FROM Prod_Cost_Prom_Men) AS Prod_Cost_Prom_Men ON (Prod_Cost_Prom_Men.KeyIdT_CP=(SELECT Prod_Cost_Prom_Men.KeyIdT_CP FROM Prod_Cost_Prom_Men WHERE Prod_Cost_Prom_Men.Prod_Code = MasterProducts.Prod_Code AND Prod_Cost_Prom_Men.Prod_PF = MasterProducts.Prod_PF ORDER BY Prod_Cost_Prom_Men.Prod_DateUpd DESC LIMIT 1))
                    LEFT JOIN (SELECT Prod_Price_Net_Hist.* FROM Prod_Price_Net_Hist) AS Prod_Price_Net_Hist ON (Prod_Price_Net_Hist.KeyIdT_CP=(SELECT Prod_Price_Net_Hist.KeyIdT_CP FROM Prod_Price_Net_Hist WHERE Prod_Price_Net_Hist.Prod_Code = MasterProducts.Prod_Code AND Prod_Price_Net_Hist.Prod_PF = MasterProducts.Prod_PF ORDER BY Prod_Price_Net_Hist.P_Year DESC, Prod_Price_Net_Hist.P_Month DESC LIMIT 1))
                    LEFT JOIN (SELECT Prod_Cost_Hist.* FROM Prod_Cost_Hist) AS Prod_Cost_Hist ON (Prod_Cost_Hist.KeyIdT_CP=(SELECT Prod_Cost_Hist.KeyIdT_CP FROM Prod_Cost_Hist WHERE Prod_Cost_Hist.Prod_Code = MasterProducts.Prod_Code AND Prod_Cost_Hist.Prod_PF = MasterProducts.Prod_PF ORDER BY Prod_Cost_Hist.Prod_DateUpd DESC LIMIT 1))
                    WHERE MasterProducts.Prod_Code IN('00277','01081');

Output Example (with var_dump) but i have cut nodes with more that 10 column with repeated names:

array(16) {
  [0]=>
  array(8) {
    ["MasterProducts"]=>
    array(15) {
      ["KeyIdT_MP"]=>
      string(3) "277"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_Mark"]=>
      string(4) "0400"
      ["Prod_Cat"]=>
      string(4) "0005"
      ["Prod_Prov"]=>
      string(5) "00293"
      ["Prod_FT"]=>
      string(9) "FT0000204"
      ["Prod_RS"]=>
      string(9) "RS0000008"
      ["Prod_ST"]=>
      string(1) "1"
      ["Prod_AvalCom"]=>
      string(1) "1"
      ["Prod_OR"]=>
      string(1) "1"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_PF_Price"]=>
      string(1) "1"
      ["Prod_DateGen"]=>
      string(19) "2016-11-07 12:43:25"
      ["Prod_DateUpd"]=>
      string(19) "2021-09-06 11:31:52"
      ["Prod_UpdUser"]=>
      string(5) "00011"
    }
    ["Prod_Profile"]=>
    array(9) {
      ["KeyIdT_MPP"]=>
      string(3) "277"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_CB"]=>
      string(13) "8710449002399"
      ["Prod_ProvCode"]=>
      string(6) "802140"
      ["Prod_Memo"]=>
      string(71) "En esta area puede Dejar Mensajes para tener en cuenta en este Proceso."
      ["Prod_NameS"]=>
      string(11) "Pa Bat 9 5m"
      ["Prod_NameL"]=>
      string(27) "Papa Batata 9 5mm   4/5.5lb"
      ["RRProducts_ProductRelIMAGE"]=>
      string(31) "IPROD00277-2-20210906113152.jpg"
    }
    ["Prod_Det_Info"]=>
    array(45) {
      ["KeyIdT_D"]=>
      string(3) "277"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_D_1"]=>
      string(2) "21"
      ["Prod_D_2"]=>
      string(3) "120"
      ["Prod_D_3"]=>
      string(1) "0"
      ["Prod_D_4"]=>
      string(1) "4"
      ["Prod_D_5"]=>
      string(1) "4"
      ["Prod_D_6"]=>
      string(1) "4"
      ["Prod_D_7"]=>
      string(11) "24840.00000"
      ["Prod_D_8"]=>
      string(1) "M"
      ["Prod_D_9"]=>
      string(8) "10.00000"
      ["Prod_D_10"]=>
      string(2) "Kg" //----> Contains 42 more columns ...
      
    }
    ["Prod_Det_Cost"]=>
    array(37) {
      ["KeyIdT_D"]=>
      string(3) "277"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_CostID"]=>
      NULL
      ["DOC_IDPRProducts"]=>
      NULL
      ["DOC_IDRRProducts"]=>
      NULL
      ["Prod_D_1"]=>
      string(1) "2"
      ["Prod_D_2"]=>
      string(1) "2"
      ["Prod_D_3"]=>
      string(6) "1.0247"
      ["Prod_D_4"]=>
      string(1) "0"
      ["Prod_D_5"]=>
      string(7) "0.01000"
      ["Prod_D_6"]=>
      string(7) "0.01000"
      ["Prod_D_7"]=>
      string(7) "0.00000"
      ["Prod_D_8"]=>
      string(7) "0.00000"
      ["Prod_D_9"]=>
      string(7) "8.20000"
      ["Prod_D_10"]=>
      string(4) "5.00"  //----> Contains 31 more columns ...
    }
    ["Prod_Det_Price"]=>
    array(36) {
      ["KeyIdT_D"]=>
      string(3) "277"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_D_1"]=>
      string(1) "1"
      ["Prod_D_2"]=>
      string(1) "1"
      ["Prod_D_3"]=>
      string(1) "1"
      ["Prod_D_4"]=>
      string(1) "1"
      ["Prod_D_5"]=>
      string(1) "1"
      ["Prod_D_6"]=>
      string(1) "1"
      ["Prod_D_7"]=>
      string(1) "1"
      ["Prod_D_8"]=>
      string(1) "1"
      ["Prod_D_9"]=>
      string(1) "1"
      ["Prod_D_10"]=>
      string(1) "1"
      ["Prod_D_11"]=>
      string(1) "1"  //----> Contains 45 more columns ...
    }
    ["Prod_PriceP"]=>
    array(20) {
      ["KeyIdT_PP"]=>
      string(3) "117"
      ["Prod_DOC_ID_LPIMPT"]=>
      string(12) "LPIMP0000109"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["LP_CjxMs"]=>
      string(7) "1.00000"
      ["LP_UnxCj"]=>
      string(7) "4.00000"
      ["LP_UniUNY"]=>
      string(2) "Kg"
      ["LP_Unidad"]=>
      string(7) "2.50000"
      ["LP_FormFac"]=>
      string(2) "Cj"
      ["LP_FormVen"]=>
      string(2) "Un"
      ["LP_Fact"]=>
      string(8) "10.02000"
      ["LP_Price"]=>
      string(7) "8.01000"
      ["LP_PriceUni"]=>
      string(7) "2.00250"
      ["LP_PriceCj"]=>
      string(7) "8.01000"
      ["LP_DateUpd"]=>
      string(19) "2021-06-23 17:10:28"
      ["Date_Rel"]=>
      string(19) "2021-06-23 00:00:00"
      ["Date_Rel_Ext"]=>
      string(19) "2021-03-20 00:00:00"
      ["Date_Rel_Ext_C"]=>
      string(1) "3"
      ["LP_CreatorUser"]=>
      string(5) "00017"
      ["LP_UpdUser"]=>
      string(5) "00017"
    }
    ["Prod_Cost_Prom_Men"]=>
    array(10) {
      ["KeyIdT_CP"]=>
      string(5) "10282"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_DateUpd"]=>
      string(19) "2021-09-11 12:08:22"
      ["P_Year"]=>
      string(4) "2021"
      ["P_Month"]=>
      string(1) "8"
      ["Prod_Amoun"]=>
      string(8) "17664.00"
      ["Prod_AmounW"]=>
      string(8) "17664.00"
      ["Prod_CostKG"]=>
      string(7) "3.26000"
      ["Prod_CostUN"]=>
      string(7) "3.26000"
    }
    ["Prod_Price_Net_Hist"]=>
    array(7) {
      ["KeyIdT_CP"]=>
      string(5) "20592"
      ["Prod_Code"]=>
      string(5) "00277"
      ["Prod_PF"]=>
      string(1) "1"
      ["Prod_DateUpd"]=>
      string(19) "2021-09-18 11:23:52"
      ["P_Year"]=>
      string(4) "2021"
      ["P_Month"]=>
      string(1) "8"
      ["Prod_Price_Net"]=>
      string(7) "4.33000"
    }
  }
}
```

Could you please expand on how $outPut is used? perhaps edit and show how that variable is used, as well as describe where $dbUsing and $dbquery come from/are defined — Sᴀᴍ Onᴇᴌᴀ, Commented Oct 4, 2021 at 20:14
@SᴀᴍOnᴇᴌᴀ I have updated the post, I hope I have not damaged it XD — user152284, Commented Oct 4, 2021 at 20:27
@mickmackusa if it works properly; I was thinking of any other improvement — user152284, Commented Oct 4, 2021 at 22:38
Thank you for updating the post? Could you possibly expand more? I'm curious which columns from the query results are used (from $result)... Is the data returned in API results, or are only certain parts of the data used for output? — Sᴀᴍ Onᴇᴌᴀ, Commented Oct 5, 2021 at 23:11
@SᴀᴍOnᴇᴌᴀ Well in my case I use 80% of the columns, in theory the information mined is directed to create 4 audit reports and 4 for executives that are printed on dot matrix printers ... these reports are: Master of Products, Price Profiles, Current Inventory, ETC .... this super report was not designed by me, they simply asked me to do so because the management requested it .... I could do it based on another design yes but it would not have the scope of those requested. — user152284, Commented Oct 5, 2021 at 23:16

Sᴀᴍ Onᴇᴌᴀ · Accepted Answer · 2021-10-10 07:37:49Z

Readability - style guide

It is recommended that a style guide be followed for the sake of anyone reading and maintaining the code - including your future self. For PHP the PHP Framework Interop Group maintains style guides - including PSR-1 and PSR-12. The code here does not really follow the style recommendations - most notably:

PSR-1

4.3. Methods

Method names MUST be declared in camelCase().

PSR-12

2.4 Indenting

Code MUST use an indent of 4 spaces for each indent level, and MUST NOT use tabs for indenting.

6.2. Binary operators

All binary arithmetic, comparison, assignment, bitwise, logical, string, and type operators MUST be preceded and followed by at least one space:

if ($a === $b) {
    $foo = $bar ?? $a ?? $b;
} elseif ($a > $b) {
    $foo = $a + $b * $c;
}

Pushing elements onto an array

The function DBQuery2() pushes results from the query into the array like this:

$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++;
}

But with PHP elements can be pushed into an array without specifying an index¹, thus eliminating the need to maintain a counter variable (i.e. $rowc):

while ($row = $resultset->fetch(PDO::FETCH_NUM)) {
    $resultRow = [];
    foreach($row as $key => $value){
        $resultRow[$key]=[
                'ColumnName' => $resultset->getColumnMeta($key)['name'],
                'value' => $value
            ];
        }
        $outPut[] = resultRow;
   }

And similarly:

$rowc = 0;
while ($row = $resultset->fetch(PDO::FETCH_NUM)) {
      $outPut[$rowc]=[];
      foreach($row as $key => $value){
          $table = $resultset->getColumnMeta($key)['table'];
          $column = $resultset->getColumnMeta($key)['name'];
          if(!isset($outPut[$rowc][$table])){
              $outPut[$rowc][$table]=[];
          }
          $outPut[$rowc][$table][$column]=$value;
      }
      $rowc++;
 }

Can be simplified to :

while ($row = $resultset->fetch(PDO::FETCH_NUM)) {
      $resultRow = [];
      foreach($row as $key => $value){
          $table = $resultset->getColumnMeta($key)['table'];
          $column = $resultset->getColumnMeta($key)['name'];
          if(!isset($resultRow[$table])){
              $resultRow[$table] = [];
          }
          $resultRow[$table][$column] = $value;
      }
      $outPut[] = resultRow;

 }

Query Simplification

Correct me if I am wrong but I believe this SQL query can be simplified from:

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)

To this (no need to select a table in a sub-query just to join it - especially with the same alias as the table name):

SELECT 
Master_Producto.*, 
Producto_Estructura.*,
Producto_Proveedor.*,
Producto_Precio.*,
FROM Master_Producto
LEFT JOIN Producto_Estructura ON Master_Producto.Prod_Code = Producto_Estructura.Prod_Code AND Master_Producto.Prod_PF = Producto_Estructura.Prod_PF
LEFT JOIN Producto_Proveedor ON Master_Producto.Prod_Code = Producto_Proveedor.Prod_Code AND Master_Producto.Prod_PF = Producto_Proveedor.Prod_PF
LEFT JOIN Producto_Precio ON Master_Producto.Prod_Code = Producto_Precio.Prod_Code AND Master_Producto.Prod_PF = Producto_Precio.Prod_PF

And since aliases are used on the tables, those could be simplified as well:

SELECT 
mp.*, 
pe.*,
ppro.*,
ppre.*,
FROM Master_Producto mp
LEFT JOIN Producto_Estructura pe ON mp.Prod_Code = pe.Prod_Code AND mp.Prod_PF = pe.Prod_PF
LEFT JOIN Producto_Proveedor ppro ON mp.Prod_Code = ppro.Prod_Code AND mp.Prod_PF = ppro.Prod_PF
LEFT JOIN Producto_Precio ppre ON mp.Prod_Code = ppre.Prod_Code AND mp.Prod_PF = ppre.Prod_PF

Stack Exchange Network

Prevent Column Name Collision

1 Answer 1

Readability - style guide

PSR-1

4.3. Methods

PSR-12

2.4 Indenting

6.2. Binary operators

Pushing elements onto an array

Query Simplification

Hot Network Questions

Prevent Column Name Collision

1 Answer 1

Readability - style guide

PSR-1

4.3. Methods

PSR-12

2.4 Indenting

6.2. Binary operators

Pushing elements onto an array

Query Simplification

Related

Hot Network Questions