Database join needed to join two tables

[eluser]swgj19[/eluser]
Here is a pic of the backend of my backend panel:
click here

As one can see the column Article Title is not showing. I did a var_dump($data) and the title field in the database is not showing. I tried to join the comments and posts tables so that the title field in posts will show relating to the comment that was posted for that titled article. Thank you for any direction.

Here is the function in my controller:

Code:

@access public
  * @return void
  */
public function index()
{
  $data['title']  = "Manage Comments";
  $data['main']  = 'admin_comments_home';
  $data['comments'] = $this->comment_model->get_all_comments();
  //$data['article_title'] = $this->comment_model->get_article_title();


  $this->load->vars($data);
  $this->load->view('dashboard');
  //var_dump ($data);  
}

// --------------------------------------------------------------------

/**
  * create()
  *
  * Description:
  *
  * @access public
  * @return void
  */  
public function create()
{

  if ($this->input->post('name'))
  {
   $this->comments->add_comment();
   $this->session->set_flashdata('message', 'Comment created');

   redirect('admin/comments/index', 'refresh');
  }
  else
  {
   $data['title']   = "Create Comment";
   $data['main']   = 'admin_comments_create';
   $data['comments']  = $this->comments->get_comments();
  
   $this->load->vars($data);
   $this->load->view('dashboard');    
  }
}

Here is my function in my model

Code:

public function get_all_comments()
{
  $data = array();

  $this->db->select('*');
  $this->db->from('comments');
  //$this->db->join('posts', 'posts.id = comments.id');

  

  $query = $this->db->get();

  if ($query->num_rows() > 0)
  {
   foreach ($query->result_array() as $row)
   {
    $data[] = $row;
   }
  }

  $query->free_result();  

  return $data;
}

Here is my view:

Code:

<br />
<h2>&lt;?php echo $title;?&gt;</h2>

<p>&lt;?php echo anchor("admin/comments/create", "Create new comment");?&gt;</p>
<br />

&lt;?php
if ($this->session->flashdata('message'))
{
echo "<div class='message'>".$this->session->flashdata('message')."</div>";
}

if (count($comments))
{
echo "<table border='1' cellspacing='0' cellpadding='3' width='700'>\n";
echo "<tr valign='top'>\n";
echo "<th>ID</th>\n<th>Name</th><th>Email</th><th>body</th><th>Article Title</th><th>Post ID</th><th>Status</th><th>Actions</th>\n";
echo "</tr>\n";

foreach ($comments as $key => $list)
{
  echo "<tr valign='top'>\n";
  echo "<td>".$list['id']."</td>\n";
  echo "<td>".$list['name']."</td>\n";
  echo "<td align='center'>".$list['email']."</td>\n";
  //echo "<td align='center'>".$list['body']."</td>\n";
  echo "<td align='center'>".auto_typography(word_limiter($list['body'], 25))."</td>\n";
  echo "<td align='center'>".$article_title."</td>\n";
                //echo "<td align='center'>".$list['title']."</td>\n";
  echo "<td align='center'>".$list['post_id']."</td>\n";
  echo "<td align='center'>".$list['status']."</td>\n";
  echo "<td align='center'>";
  echo anchor('admin/comments/edit/'.$list['id'], 'edit');
  echo " | ";
  echo anchor('admin/comments/delete/'.$list['id'], 'delete');
  echo "</td>\n";
  echo "</tr>\n";
}

echo "</table>";
}
?&gt;
<br />

Here is my table info:

Code:

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `tags` varchar(255) NOT NULL,
  `body` text,
  `category_id` int(11) unsigned NOT NULL,
  `pub_date` date NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `status` enum('draft','published') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;

Code:

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `body` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `post_id` int(11) unsigned NOT NULL,
  `pub_date` datetime NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

[eluser]greedyman[/eluser]
Why don't you use list['title'] instead $article_title? You shouldn't write html code in php code, example:

Code:

<?php foreach ($comments as $key => $list): ?>
<tr><td>etc</td></tr>
&lt;?php endforeach; ?&gt;

[eluser]swgj19[/eluser]
I have turned it upside down and that is not the problem. I think it is something with the database. ?Thank you though. html is designed to encompass .php and vice versa...

[eluser]Pert[/eluser]
Try this simple join

Code:

$query = $this->db->select('comments.*, posts.title AS article_title')
   ->where('posts.id', 'comments.post_id', false)
   ->get('comments, posts');