mysqli - How to return mysql data using table aliases with left join and duplicate columns

Question

I am trying to do a simple left join, while using table aliases, to return all of the values from both tables in this format:

[Alias1] => Array
    (
        [id] => 1
        [city] => bay area
        [state] => CA
    )

[Alias2] => Array
    (
        [id] => 1
        [city] => san francisco
        [baseball_team] => giants

     )

Here is my very simple mysqli_query:

$result = mysqli_query($sql, SELECT  Alias1.id, Alias1.city, Alias1.state, Alias2.id, Alias2.city, Alias2.baseball_team FROM database.table1 AS Alias1 LEFT JOIN database.table2 AS Alias2 ON Alias1.id = Alias2.id)

To get the results... I use:

while($row = mysqli_fetch_array($result))
{
     $data[] = $row;
}
print_r($data);die();

Problem: The data prints out as such:

 [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => bay area
            [city] => bay area
            [2] => CA
            [state] => CA
            [3] => 1
            [4] => san francisco
            [5] => giants
            [baseball_team] => giants
        )

Other Info: I originally tried to get the data using mysql_fetch_assoc($result)... But the duplicate columns don't return at all.

Answer 1

Instead of:

$data[] = $row;

Use this to form the data array that you desire:

$data['Alias1'] = array(
    "id"=>$row['id'],
    "city"=>$row['1_city'],
    "state"=>$row['state'],
);
$data['Alias2'] = array(
    "id"=>$row['id'],
    "city"=>$row['2_city'],
    "baseball_team"=>$row['baseball_team'],
);

OR, if you expect more than one row you need to use something like:

$data[] = array(
    "Alias1" => array(
        "id"=>$row['id'],
        "city"=>$row['1_city'],
        "state"=>$row['state'],
    ),
    "Alias2" => array(
        "id"=>$row['id'],
        "city"=>$row['2_city'],
        "baseball_team"=>$row['baseball_team'],
    ),
);

Note: If your city values are different between your two tables then you should use an alias so that you can pull the other one differently. If they are the same, I suggest you not have them listed twice in the database as this is duplicating data and leaves your database denormalized.

Edit: Your query should be something like

SELECT  Alias1.id, Alias1.city AS 1_city, Alias1.state, Alias2.id, Alias2.city AS 2_city, Alias2.baseball_team FROM database.table1 AS Alias1 LEFT JOIN database.table2 AS Alias2 ON Alias1.id = Alias2.id

See edited answers above for these aliases.