PDO FETCH_CLASS with related tables

Question

Let's say I have 2 php objects:

<?php class Post { public $id; public $text; public $user_id; } ?> 

and

 <?php class User { public $id public $name } ?> 

Each message has a unique restriction with 1 user in the database.

I want to fill in the data in the "Post" object using the PDO method "FETCH_CLASS", which works for all attributes of the "Post", but how to fill in the attributes in the "User"?

My SQL statement is as follows:

 SELECT post.id, post.text, post.user_id, user.id, user.name FROM POST INNER JOIN User on post.user_id = user.id 

Thanks!

UPDATE:

ATM I populate my Mail class as follows:

  $statement = $db -> prepare($query); $statement -> execute(); $statement -> setFetchMode(PDO::FETCH_CLASS, 'Post'); $posts = $statement -> fetchAll(); 

So, how do I change this to populate another User class?

DECISION:

 $statement = $db -> prepare($query); $statement -> execute(); $posts = array(); while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) { $post = new Post(); $post->id = $row['post_id']; $post->text = $row['post_text']; $post->created = $row['post_created']; $post->image = $row['post_image']; $post->url = $row['post_url']; $post->weight = $row['post_weight']; $post->likes = $row['post_likes']; $user = new User(); $user->id = $row['user_id']; $user->nickname = $row['user_nickname']; $user->created= $row['user_created']; $user->locked = $row['user_locked']; $post->user = $user; $posts[] = $post; } return $posts; 

Answer 1

As far as I know, in PDO there is no support directly in PDO. Usually, if you need to create a graph of a complex object from the result of a query that is responsible for ORM.

If you need this functionality, I recommend using Doctrine or Propel , rather than writing something yourself. There are others that may be easier, but I have no experience with them.

EDIT:

I think that perhaps I misunderstood this question, as I am sure that others can. I think the real question was how to access the joined columns, not how to create an object from them.

In this case, simply using the standard arry fethc method, such as PDO::FETCH_ASSOC , PDO::FETCH_NUMERIC or PDO::FETCH_BOTH , you will get all the columns that you requested.

Therefore, if you want to turn this into an "object graph", you need to do it manually without using PDO::FETCH_CLASS .

For example:

 //$db is pdo: // also notice im aliase the columns prefixing the name so that we can tell what belongs to // post and what belongs to user, an alternative approach would be to use FETCH_NUMERIC, // which just uses the column positions from the seelct statement as the keys // so in this case post.id would be in the array as key 0, and user.name would be in the // array as key 4 $stmt = $db->prepare('SELECT post.id as p_id, post.text as p_text, post.user_id as p_user_id, user.id as u_id, user.name as u_name FROM POST INNER JOIN User on post.user_id = user.id'); $stmt->execute(); while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) { print_r($row); /* will output: Array ( 'p_id' => 'value' 'p_text' => 'value' 'p_user_id' => 'value' 'u_id' => 'value', 'u_name' => 'value' ) So now you need to decide how to create your objects with the information returned */ } 

Answer 2

This is actually not an answer to OQ, but because it continues to appear on Google (yes, I know it for more than a year). You will find it AMAZINGly faster to just skip loops and query each table separately.

     SELECT post.id, 
            post.text, 
            post.user_id, 
     FROM POST INNER JOIN User on post.user_id = user.id
       $ statement = $ db -> prepare ($ query);
         $ statement -> execute ();
         $ statement -> setFetchMode (PDO :: FETCH_CLASS, 'Post');
         $ posts = $ statement -> fetchAll ();

     SELECT user.id, 
            user.name 
     FROM POST INNER JOIN User on post.user_id = user.id
       $ statement = $ db -> prepare ($ query);
         $ statement -> execute ();
         $ statement -> setFetchMode (PDO :: FETCH_CLASS, 'User');
         $ users = $ statement -> fetchAll ();
    

Answer 3

Perhaps use PDO :: FETCH_NAMED if you are working with multiple tables. Or use PDO :: ATTR_FETCH_TABLE_NAMES.

Answer 4

You can try using the __set method as follows:

 <?php include 'connection.php'; class Post { public $id; public $text; public $user; public function __construct() { $this->user = new User(); } public function __set($name, $value) { if (array_key_exists($name, get_object_vars($this->user))) { $this->user->$name = $value; } else { $this->$name = $value; } } } class User { public $id; public $name; } $statement = $pdo->prepare("SELECT * FROM post " . "LEFT JOIN user " . "ON post.user_id = post.id"); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE, Post::class); echo "<pre>"; var_dump($result);