php函数，用于获取连接具有公共字段名的多个表的数据并显示输出

Question

我有一个PHP函数来获取用户数据，但连接的表共享常见的字段名，如id、name等。我在这里使用SELECT *。如何显示数据。

function userData(){
global $pdo;
$stmt = $pdo->prepare("SELECT * FROM users
LEFT JOIN cities ON users.city = cities.id
LEFT JOIN states ON cities.state_id = states.id
LEFT JOIN countries ON states.country_id = countries.id
WHERE users.id = :id");
$stmt-> bindValue(':id', sessionUser());
$stmt-> execute();
return $stmt->fetch();
}
$user = userData();

我想要一些类似的东西：

$user['cities.name']
$user['states.name']

但这行不通。该怎么办？

Answer 1

您需要为SQL列提供别名。

SELECT
`cities`.`name` as 'cities.name',
`states`.`name` as 'states.name',
(etc)

否则(我相信(PHP会误解并覆盖键(列名(相同的值。

Answer 2

您需要添加想要返回的字段，即

$stmt = $pdo->prepare("SELECT * FROM users,
cities.name as cityName
states.name as stateName
LEFT JOIN cities ON users.city = cities.id
LEFT JOIN states ON cities.state_id = states.id
LEFT JOIN countries ON states.country_id = countries.id
WHERE users.id = :id");

然后使用：

$user['cityName']
$user['stateName']