How to use user_id to get users details from database and display them

Question

I'm making a website on the CodeIgniter framework so users can add products to the website. Now my question is: How do I use the user_id in the products table to get the user's details from the database and display them next to the product? So for example when I click on a product I want to have a link to the profile of the user who uploaded the product but I'm not sure how I can do that.

Database tables information:

table users:

user_id (primary_key)
email
voornaam
achternaam
beschrijving


table products:

product_id (primary_key)
product_naam
product_beschrijving
user_id
category_id

My function in model file:

public function get_product_details($product_id) {
    $this->db->select('products.*, users.*');
    $this->db->from('products');
    $this->db->join('users', 'users.user_id = products.user_id', 'left');
    $this->db->where('product_id', $product_id);
    $query = $this->db->get();
    $result = $query->row_array();
    if (!empty($result)) {
        return $result;
    } else {
        return array();
    }
}

score 4 · Accepted Answer · edited Mar 13 '18 at 08:00

4

Use join query based on user_id fetch details from 2 tables in database.

Like this:

Model:

public function get_product_details($product_id)
{
    $this->db->select('users.user_id,users.email,users.voornam,products.product_id,products.category_id,products.product_naam');
    $this->db->from('users');
    $this->db->join('products','products.user_id = users.user_id');
    $this->db->where('product_id', $product_id);
    $query = $this->db->get();
    if($query->num_rows()>0)
    {
       return $query->result_array();
    }
}

Controller:

public function edit($product_id)
{
    $data['products_list'] = $this->your_model->get_product_details($product_id);
    $this->load->view('your_view_filename',$data);
}

// data from $data['products_list'] in controller.

View:

<?php print_r($products_list); //for refference
    foreach($products_list as $row)
                    {
                    ?>
                    <tr>
                        <td><?php echo $row['email'];?></td>
                        <td><?php echo $row['voornam'];?></td>
                        <td><?php echo $row['product_naam'];?></td>
                            etc...
                     </tr>
   <?php } ?>

edited Mar 13 '18 at 08:00

Pang

9,564
146
81
122

answered Sep 20 '17 at 11:58

karthikeyan

172
1
9

What do I type in view to echo it? – lablanco Sep 20 '17 at 12:35
Could you change you're code with my database information? table users: user_id (primary_key) email voornaam achternaam beschrijving table products: product_id (primary_key) product_naam product_beschrijving user_id category_id – lablanco Sep 20 '17 at 13:33
I also have my database information in my question. could u please change it because i might confuse with some stuff – lablanco Sep 20 '17 at 13:34
check this i updated my codings used your database details in my view – karthikeyan Sep 20 '17 at 13:42
https://stackoverflow.com/questions/793807/codeigniter-php-mysql-retrieving-data-with-join reffer this link for join 2 tables – karthikeyan Sep 20 '17 at 13:47
http://www.mysqltutorial.org/mysql-join/ its teaches how to join 2 tables you can clearly understand. – karthikeyan Sep 20 '17 at 13:49
Hello sir I have 1 question why do you have this line in the controller: $data['menu_name'] = "subcategory_list"; – lablanco Sep 21 '17 at 07:34
what changes would you like to change please analyze the code you can easliy understand model view controller. – karthikeyan Sep 21 '17 at 07:40
Hello I now see it like this on the page: Array ( [0] => Array ( [user_id] => 6 [email] => jeremy2@gmail.com [voornaam] => Jeremy [product_id] => 73 [category_id] => 3 [product_naam] => Tennisracket ) ) jeremy2@gmail.com Jeremy Tennisracket with all the database info but I dont want it to show all the database information. Do you know how I can only show the name for example without for example [category_id] => 3 – lablanco Sep 21 '17 at 07:56
Do you understand what I mean? – lablanco Sep 21 '17 at 08:15
I can't understand this line (Do you know how I can only show the name for example without for example [category_id] => 3) – karthikeyan Sep 21 '17 at 09:07
I already got it man. I removed: print_r($products_list); To only see the names and not the full database information – lablanco Sep 21 '17 at 09:10
what information you want to display fetch it from database through model and display in view based on user_id. – karthikeyan Sep 21 '17 at 09:11
Instead of this: Array ( [0] => Array ( [user_id] => 6 [email] => jeremy2@gmail.com [voornaam] => Jeremy [product_id] => 73 [category_id] => 3 [product_naam] => Tennisracket ) ) jeremy2@gmail.com Jeremy Tennisracket I only wanted to display the names :jeremy2@gmail.com Jeremy Tennisracket So I removed this line in the view: print_r($products_list); – lablanco Sep 21 '17 at 09:12
Ok i put print_r() for your refference what are the data's comes from database. – karthikeyan Sep 21 '17 at 09:14

How to use user_id to get users details from database and display them

1 Answers1