如何使用带有左连接和重复列的表别名返回 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屋!