php function to fetch data joining multiple tables with common field names and displaying output [duplicate]

Question

I have a PHP function to fetch users data but the joined tables share common field names like id, name etc. I am using SELECT * here. How can I display the data.

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

I want something like:

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

But that doesn't work. What to do?

Answer 1

You will need to give your SQL columns aliases.

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

Otherwise (I believe) PHP will misunderstand and overwrite values where the keys (column names) are the same.

Answer 2

You need to add in the fields that you want returned i.e.

$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");

Then use:

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