How to execute a query using a PHP variable as a value in 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.

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

You initially mentioned that you wanted to grab the Username from the posts table. Can you confirm in phpMyAdmin or your DB program that User_id is definitely being populated in your posts table?? Also don't put username in the posts table. If you allow a user to change their username - you will have to update all the corresponding records. Its much better to link purely on IDs. I think you're going to find the posts.username column is empty? — Ukuser32
– Ukuser32, Commented Jul 8, 2019 at 9:02
Hi Ukuser32 So yes that is what i initially wanted to do. But then after some digging around I came to the conclusion that I'd rather use the ID's to link them up, just as you mentioned there. And yes, "user_id" is being populated into the posts table. And also yes, posts.username is empty so I removed it from the DB. Do you perhaps have any tips or pointers for me? — Alex Coetzee
– Alex Coetzee, Commented Jul 8, 2019 at 17:48

MackieeE · Accepted Answer · 2019-08-29 22:01:12Z

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'];
 }

answered Aug 29, 2019 at 22:01

MackieeE

11.9k4 gold badges41 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

How to execute a query using a PHP variable as a value in CodeIgniter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related