0

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?

Mr. Crypto
  • 39
  • 5
  • Column aliases are the only way. You should avoid use of `SELECT *` whenever possible anyway; this is just further incentive to do so! – miken32 May 20 '21 at 16:21
  • 1
    You could try [PDO::FETCH_NAMED](https://phpdelusions.net/pdo/fetch_modes#FETCH_NAMED) – Nigel Ren May 20 '21 at 16:22
  • @NigelRen I had not heard of that before, thanks! (I upvoted YCS' recent answer on the linked duplicate as well.) – miken32 May 20 '21 at 16:24
  • If you don't want to alias your fields I think the only options are to use the ordinal value that you get with PDO::FETCH_BOTH or try PDO::FETCH_NAMED which groups the values by the column name. Either way you'll be referencing an index, ie `$user[0]` or `$user['name'][0]`. – cOle2 May 20 '21 at 16:27
  • @NigelRen and cOle2 If yours was the answer I would have accepted it. Thanks both of you. That did the task. :) – Mr. Crypto May 20 '21 at 18:48
  • You can give an upvote to https://stackoverflow.com/a/60692300/1213708. – Nigel Ren May 20 '21 at 18:54
  • Hey your other question got deleted. Not sure why. I figured out what you were after, It's not a css issue. Just add 'checked' to the input field as an attribute. – Daniel Duong May 21 '21 at 02:57

2 Answers2

0

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.

dearsina
  • 4,774
  • 2
  • 28
  • 34
  • This is a regular approach and there are a lot of fields. I can't go name them each. I need a universal solution for this. – Mr. Crypto May 20 '21 at 16:11
  • I don't think there is a (straightforward) way of achieving what you want. I had the same issue as you, and I wrote a complex custom class to pull in all column names (with aliases). It's one of the limitations of PHP and mySQL. – dearsina May 20 '21 at 16:17
0

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']
  • This is a regular approach and there are a lot of fields. I can't go name them each. I need a universal solution for this. – Mr. Crypto May 20 '21 at 16:12
  • Unless you include the fields within the select statement then they wont be returned. –  May 21 '21 at 02:51