Codeigniter - How data list with multiple tables

Question

i have two tables of database 1st table is: Products *id *brand *model

2nd table is: Sales *id *pid *date

i want to create list with getting data form both tables.

here is my Model:

class Sales_model extends CI_Model {
    function getAll() {


        $q = $this->db->get('sales');
        foreach ($q->result() as $row) {    
            $data[] = $row;

            $q2 = $this->db->get_where('products', array('id' => $row->pid));

            foreach ($q2->result() as $row2) {
                $data[] = $row2;
            }

        }
        return $data;
}

-------- and here is controller code ------------

class Sales extends CI_Controller {

    function __construct()
    {
        parent::__construct();
    }

    public function index()
    {
        $this->load->model('sales_model');

        $data['q'] = $this->sales_model->getAll();

        $this->load->view('sales', $data);
    }
}

-------------------- here is my view code ----------------

<?php foreach($q as $row) : ?>
    <tr>
        <td><?php echo $row->id; ?></td>
        <td><?php $date = new DateTime("@$row->date"); echo $date->format('D M d, Y'); ?></td>
        <td><?php echo $row->brand; ?></td>
        <td><?php echo $row->model; ?></td>
    </tr>
<?php endforeach; ?>

anybody help me, i want to get date from sales table and products detail who add in sales table. Thanks

This isn't really a CodeIgniter question but more of how to build the query to get your intended results right? — Will Sampson
– Will Sampson, Commented Oct 3, 2012 at 19:25

Paul Benbow · Accepted Answer · 2012-10-04 07:36:19Z

1

I'm not quite sure what data you require but this might help.

$sql = "SELECT products.id, products.brand, products.model, sales.id, sales.date FROM products INNER JOIN sales ON (products.id = sales.pid)";
$query = $this->db->query($sql); 

if($query->num_rows() > 0){
return $query->result_array();
}

edited Oct 4, 2012 at 7:36

answered Oct 3, 2012 at 19:10

Paul Benbow

3292 gold badges12 silver badges22 bronze badges

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

Comments

Nishant Jani · Accepted Answer · 2012-10-04 06:07:05Z

0

Model :

$this->db->select('p.*,s.date');
$this->db->from('product p');
$this->db->join('sales s','p.pid = s.pid');
$result = $this->db->get();

if($result->num_rows)
{
return $result->result();
}
return false;

answered Oct 4, 2012 at 6:07

Nishant Jani

1,9918 gold badges30 silver badges41 bronze badges

Collectives™ on Stack Overflow

Codeigniter - How data list with multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related