基于一个表id获取valueigniter中另一个表的值

I have created two tables. One is for abprodut_detail, another is tbproduct_detail

1.abproduct_deatil structure

id    product_id        product_name       cost_price     selling_price

7        4          Alentin DS 400 Tablet      55           60

I created a form like this one

<table class="table table-borderd table-hover">
                <thead>
                    <tr>
                        <th>Product Name</th>
                        <th>Price</th>
                        <th>Quantity</th>
                        <th>Total</th>
                        <th><input type="button" class="btn btn-primary addmore" value="+"></th>
                    </tr>
                </thead>
                <tbody id="itemlist2">
                    <tr id="tr_1">
                          <td class="prod_c">
                            <select class="select-product form-control" id="itemName_1" name="product_name[]">
                              <option value="">
                      </option>
                         <?php
                        foreach ($product as $key ):
                          ?>
                         <option value="<?php echo $key['id']  ?>">
                           <?php echo $key['product_name'] ?>
                         </option>
                        <?php  endforeach; ?>
                            </select>
                        </td>

                        <td>   
                        <input type="text" name="price[]" id="price_1" class="price form-control" value=""  data-cell="C1">
                       </td>

                        <td><input type="text" data-cell="D1" name="quantity[]" id="quantity_1" class="qty form-control"></td>
                        <td><input type="text" data-cell="E1" name="discount[]" id="discount_1" class="form-control"></td>
                        <td><input type="text" data-cell="F1" data-formula="(C1*D1)-(C1*D1*E1/100)" name="total[]" id="total_1" class="amount form-control"></td> 
                  </tr>
              </tbody>
            </table>

I select all data from abproduct_detail by that from and insert to tbproduct_detail . After inserting to tbproduct_detail looks like this one . That means from select option i stored product id number into product_name.

2.tbproduct_detail structure

   id    product_id        product_name   quantity      price

   19      16                 7               5          60

I want to show like this structure on my edit view page

 id    product_id        product_name         quantity     price

 19      16         Alentin DS 400 Tablet       5            60

You will notice that abproduct_deatil, id = tbproduct_detai, product_name

I want to combine 2 tables and view data on my edit view page.

my edit view page

<thead>
                    <tr>
                        <th>ProductName</th>
                        <th>Quantity</th>
                        <th>Price</th>

                    </tr>
                </thead>
                <tbody class="detail">
                   <?php 
                     if($rows->num_rows() > 0)
                     {
                        foreach($rows->result() as $d)
                        {
                            ?>
                                 <tr>
                                    <td><input type="text" value="<?= $d->product_name ?>" name="product_name[]" class="form-control"></td>
                                    <td><input type="text" value="<?= $d->quantity ?>" name="quantity[]" class="form-control"></td>
                                    <td><input type="text" value="<?= $d->price ?>" name="price[]" class="form-control"></td>

                                </tr>
                            <?php 
                        }
                     }
                   ?>

                </tbody>
            </table>

my controller

public function edit($id)
{

    $data['rows']= $this->db->query("SELECT * FROM tbproduct_detail WHERE product_id = '$id'");
    $this->load->view('product/edit',$data);
}

I do not know how to do this? Please help me. I am new to this forum and novice in codeigniter.

try following query in your controller.

$data['rows']= $this->db->query("SELECT a.id, a.product_id, b.product_name, a.quantity,a.price FROM tbproduct_detail a, abprodut_detail b  WHERE a.product_name= b.id AND a.product_id = '$id'");

off the topic, its better to use model files to write your queries.

In your controller used JOIN query.

Your expected query is :

select a.id, a.product_id, b.product_name, a.quantity, a.price from tbproduct_detail a join abproduct_deatil b on a.product_name = b.id WHERE a.product_id = '$id'

So, final code is

public function edit($id)
{
    $data['rows']= $this->db->query("select a.id, a.product_id, b.product_name, a.quantity, a.price
    from tbproduct_detail a join abproduct_deatil b
    on a.product_name = b.id WHERE a.product_id = '$id'");

    $this->load->view('product/edit',$data);
}