Join column from one table to another column in other table CodeIgniter

Question

I've got 2 Tables that I want some of the columns to auto update when the other one gets data from user.

Here is my scenario: User registers and his details go to "users" Table. The registered user can now make a post. When he makes a post his post goes to "posts" table.

Now I want to display the users "username" when he/she makes a post.

So, conclusion is that I want the column "username" from table "users" to automatically synchronize with column "username" from table "posts". So that once a user makes a post, it will see that a specific user made a post.

Here's how i will implement it

<?php foreach ($posts as $post) : ?>

    <div class="row">
        <div  class="col-md-3">
            <img class="post-thumb img-fluid" src="<?php echo site_url(); 
?>assets/images/posts/<?php echo $post['post_image']; ?>">
        </div>
        <div class="col-md-9">
            <h3><?php echo $post['title'];?></h3>
            <small class="post-date">Posted on: <?php echo 
$post['created_at']; ?>
                in <strong><?php echo $post['category_name']?></strong> by 
<strong><?php echo $post['username']?></strong></small><br>
            <?php echo word_limiter($post['body'], 50); ?>
            <br><br>
            <p><a class="btn btn-info" href="<?php echo 
site_url('/posts/'.$post['slug']);?>"
                >Read More</a></p>
        </div>
    </div>

<?php endforeach; ?>

Here is a function I tried, but it doesn't update my "posts" table column "username" when I make a post.

public function get_posts($slug = FALSE){
    if ($slug === FALSE){
        $this->db->order_by('posts.id', 'DESC');
        $this->db->join('categories', 'categories.id = 
posts.category_id');
        $this->db->join('users', 'users.username = posts.username');
        $query = $this->db->get('posts');
        return $query->result_array();
    }

    $query = $this->db->get_where('posts', array('slug' => $slug));
    return $query->row_array();
    }

Here are the DB tables

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `posted_by` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `post_image` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `register_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

There is no error, just no result.

Any advice or help will be appreciated.

EDIT:

I've managed to make a "get_username" function. But I'm not sure why its not working and how to create a loop for it to loop through posts that have been made. Please see code below

public function get_username(){
    $username = $this->db->query("
          SELECT u.username AS username
          FROM users u
          LEFT JOIN posts p 
          ON u.id = p.user_id
          WHERE p.id = ?"
        , array('id')
    )->row_array();
    return $username['username'];
 }

I get error saying:

A PHP Error was encountered
Severity: Notice

Message: Undefined index: username

Filename: posts/index.php

Line Number: 19

Answer 1

Your get_username() method takes no parameters - what if you wanted to get id of 5? or 20? Currently you have no ability to specify what id to query. The current query, results in:

LEFT JOIN posts p ON u.id = p.user_id WHERE p.id = 'id'

As it's passing a string literally called id, whereas what you actually want, is to pass a parameter with the associated ID to query.

/**
 *
**/
public function get_username( int $userId )
{
    $username = $this->db->query("
          SELECT u.username AS username
          FROM users u
          LEFT JOIN posts p 
          ON u.id = p.user_id
          WHERE p.id = ?"
        , array( $userId )
    )->row_array();
    return $username['username'];
 }