munaz munaz - 6 months ago 60
SQL Question

Retrieve related post with codeigniter

I'm unable to retrieve similar post data from db with codeigniter. In my blog, I have a tags field which is keeping data like 'php,mysql,mongo,java,jquery'

I just try to get similar post which is related with current posts tags. But im not getting expected result. and the problem is in my query. Its show only three post and that is 1st, last, and number 3rd one.

[CONTROLLER]

public function showpost()
{
$data = array();
$this->load->view('header',$data);
$data['post'] = $query->result();
$data['similar'] = $this->crudModel->getSimilarPost();
$this->load->view('showfull',$data);
$this->load->view('footer');
}

[MODEL]

public function getSimilarPost()
{
$query = $this->db->get_where('blogs',array('id' => $this->uri->segment(3)));
foreach($query->result() as $row){ $tags = $row->tags; }
$match = explode(',', $tags);
for($i = 0; $i < count($match); $i++)
{
$this->db->like('tags',$match[$i]);
$this->db->from('blogs');
$sqlQuery = $this->db->get();
}
return $sqlQuery->result();
}

[VIEW]

foreach($similar as $row)
{
echo($row->btitle.'<br/>');
}

Answer

Try this.

public function showpost()
{
    $data = array();            
    $this->load->view('header',$data);      
    $data['post'] = $query->result(); // why this line??
    $data['similar'] = $this->crudModel->getSimilarPost();
    $this->load->view('showfull',$data);
    $this->load->view('footer');
}

[MODEL]

public function getSimilarPost()
{
    $query = $this->db->get_where('blogs',array('id' => $this->uri->segment(3)));
    foreach($query->result() as $row){ $tags = $row->tags }
    $match =  explode(',', $tags);
    $result = [];
    for($i = 0; $i < count($match); $i++)
    {
        $this->db->like('tags',$match[$i]); 
        $this->db->from('blogs');
        $sqlQuery = $this->db->get();
        if($sqlQuery->num_rows()>0)
        $result[] = $sqlQuery->result();
    }       
    return $result;
}

[VIEW]

$check = [];
foreach($similar as $row)
{
     foreach($row as $data)
     {
        if(!in_array($data->btitle,$check))
        {
            $check[] = $data->btitle;
            echo $data->btitle.'<br/>';
        }
     }
}
Comments