如何使用带有左连接和重复列的表别名返回 mysql 数据 [英] How to return mysql data using table aliases with left join and duplicate columns

问题描述

我正在尝试做一个简单的左连接，同时使用表别名，以这种格式返回两个表中的所有值:

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

     )

这是我非常简单的 mysqli_query:

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();

问题:打印出来的数据如下:

 [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
        )

其他信息:我最初尝试使用 mysql_fetch_assoc($result) 获取数据...但重复的列根本不返回.

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

推荐答案

代替:

$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.

您的查询应该类似于

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.

这篇关于如何使用带有左连接和重复列的表别名返回 mysql 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！