Select Multiple Columns With Same Name in MySQLi (MySQL, PHP)

This article explains how to handle selecting columns with the same name from multiple tables in MySQLi (PHP) and still produce a HashMap (associative array) with the column names as key. We will use the table names as a prefix to achieve that.

Let's assume that both `table1` and `table2` in the example below have a column called `description`. If you select all columns from both tables in your MySQL query:

SELECT T1.*, T2.*
  FROM `table1` as T1
  LEFT JOIN `table2` as T2 ON T1.table2ID = T2.ID
  LIMIT 1;

and in PHP you use mysqli_num_rows($result):

$result = $oMySQLi->get_result();
$hmReturn = array();
if(($result !== false) && !(mysqli_num_rows($result) == 0))
    {
    while ($row = mysqli_fetch_assoc($result))
        {
        $hmReturn = $row;
        }
    }
return $hmReturn;

you will only have the `description` of `table2` in the returned HashMap. The `description` of `table1` has been overwritten by the description in `table2`, since both columns share the same name and hence the same HashMap key in PHP.

To prevent that from happening, you have to use mysqli_fetch_array($result,MYSQLI_NUM). But then you do not get a HashMap with the column names as the keys any more, but an array with a numerical index. And you do not know any more directly, which field in the array belongs to which column name in the database.

The following PHP code deals with that by adding the table as a prefix into the keys of the PHP associative array (HashMap). In addition, it also deals with the case that you join the same table multiple times by adding yet another prefix if necessary.

SQL query:

SELECT T1.*, T2.*, secondT2.*
  FROM `table1` as T1
  LEFT JOIN `table2` as T2 ON T1.table2ID = T2.ID
  LEFT JOIN `table2` as secondT2 ON T1.secondTable2ID = T2.ID;

PHP code:

//create the mysqli_stmt object as described elsewhere
$strQuery = 'SELECT XYZ FROM.... the above query';
$oMysqliStmt->prepare($strQuery);
$oMysqliStmt->execute();
$result = $oMysqliStmt->get_result();

$arHmReturn = array();
if(($result !== false) && !(mysqli_num_rows($result) == 0))
    { 	
    $aroField = mysqli_fetch_fields($result);
    while ($row = mysqli_fetch_array($result,MYSQLI_NUM))
        {
        $i = 0;
        $hmReturn = array();
        foreach($row as $value)
            {
            if($aroField[$i]->table == 'secondT2')
                {
                $keyTable = 'second.table2';
                }
                else
                {
                $keyTable = $aroField[$i]->orgtable;	
                }
            $keyField = $aroField[$i]->name;
            $key =  $keyTable . '.' . $keyField; 
            $hmReturn[$key] = $value;
            $i = $i + 1;
            }
        $arHmReturn[] = $hmReturn;
        }
    }
return $arHmReturn;

Published: September 5, 2019
Last modification: September 5, 2019