return row in row value using php PDO and MySQL

Question

I am currently using two tables that look like:

tasks(user1, user2)

users(id, name)

I am performing the following query:

SELECT * FROM tasks 
LEFT JOIN users u1 ON tasks.user1 = u1.id 
LEFT JOIN users u2 ON tasks.user2 = u2.id

I am using PHP PDO to execute the query and returning an array which I then encode to json like so:

$query = $con->prepare($SQL);
$query -> execute();
$result = $query->fetchAll(PDO::FETCH_ASSOC);

header('Content-Type: application/json');
echo json_encode($result);

My problem is that the user2 values overwrite the user1 values in the $result array, is there a way I can return something like:

[{"user1": {"id": "value", "name":"value"}, "user2": {"id": "value", "name":"value"}}]

so that each user is a new set of data inside the users key value.

Any thoughts on how to do this? I am fairly new to PDO.

Gordon Linoff · Accepted Answer · 2014-03-26 10:59:51Z

1

You need to give them different aliases (column names) in the query:

SELECT t.*, u1.name as name1, u2.name as name2
FROM tasks LEFT JOIN
     users u1 ON tasks.user1 = u1.id LEFT JOIN
     users u2 ON tasks.user2 = u2.id ;

answered Mar 26, 2014 at 10:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Oram Over a year ago

This returns: [{"name1": "value", "name2":"value"}] I want it to return all columns in users into the key value, not each one separately

Mike Oram Over a year ago

the problem with this is that there are a lot of columns in the users table and I dont want to have to exract each one manually, I need a way of doing u1.* as name1[*] sort of thing?

Gordon Linoff Over a year ago

You have to list all the columns separately. You can get the list of columns using information_schema.columns and even use a SQL query to generate the SQL for the query <colname> as <colname>_1, (or whatever).

Mike Oram Over a year ago

can you explain how I can use information_schema.columns to get the SQL to generate the query? I dont want to type them in manually as any column changes would have to be mirrored in the code (not exactly maintainable). thanks.

Collectives™ on Stack Overflow

return row in row value using php PDO and MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related