Table Joins Example

[eluser]clintonbeattie[/eluser]
Hi,

I need a quick complete code example to get table joins drilled into my head.

Has anyone, or could anyone spare a few minutes to put together a model, controllers and views to join, say for example, posts and comments for a blog. Eg To link a column in comments called post_id to the id of the post id?

I'm just learning and have searched the internet. There have been no examples and this would really straighten things in my head.

Thanks in advance.
C

[eluser]davidbehler[/eluser]

Code:

$this->db->select('*');
$this->db->from('blog');
$this->db->join('comments', 'comments.post_id = blogs.id');
$query = $this->db->get();

That's how you would do it using CodeIgniters Active Record.

Code:

select * from blog left join comments on comments.post_id = blogs.id;

That's more or less the query in standard sql.

What do you need exactly? Someone to tell you how joins work? Try to google!
Maybe this one helps: http://www.w3schools.com/Sql/sql_join.asp

[eluser]clintonbeattie[/eluser]
That's great. I'm attempting joining again now.

[eluser]clintonbeattie[/eluser]
Bit of a problem. This is my model function...

Code:

function getAllPosts(){
        $data = array();
        //all records from the database are retrieved as an array.
        $this->db->select('*');
        $this->db->from('posts');
        $this->db->join('comments', 'comments.post_id = posts.id');
        $Q = $this->db->get();
        if ($Q->num_rows() > 0) {
        foreach ($Q-> result_array() as $row) {
            $data[] = $row;
            }
        }
        $Q-> free_result();
        return $data;
    }

How do I se a controller to output that to a view?

[eluser]pistolPete[/eluser]

How do I se a controller to output that to a view?

Where's the problem?

controller

Code:

$this->load->model('your_model');
$data['posts']=$this->your_model->getAllPosts();
$this->load->view('your_view');

view

Code:

<?php
foreach($posts as $post)
{
   // echo each post
}
?>

[eluser]Shanto[/eluser]
Modify this in your controller, u have miss second parameter.

Code:

$this->load->model('your_model');
$data['posts']=$this->your_model->getAllPosts();
$this->load->view('your_view', $data);

[eluser]majidmx[/eluser]
Regarding the JOIN syntax take a look at MySQL documents as well :

http://dev.mysql.com/doc/refman/5.0/en/join.html

It has lots of example and you'll learn a lot of good tricks