How can I do this with CodeIgniter

[eluser]Marcus Marden[/eluser]

Code:

$SQL1 = mysql_query("SELECT id, name FROM categories ORDER BY name ASC");
while(list($cat_id, $cat_name) = mysql_fetch_row($SQL1)) {
$cat_name = stripslashes($cat_name);
$SQL2 = mysql_query("SELECT name, content FROM blog WHERE category='$cat_id' ORDER BY id DESC LIMIT 5");
while(list($name, $content) = mysql_fetch_row($SQL2)) {
  $name = stripslashes($name);
  $content = stripslashes($content);
}
}

Showing like that:

Code:

News
-> news 1
-> news 2
-> news 3
-> news 4
-> news 5

Games
-> game 1
-> game 2
-> game 3
-> game 4
-> game 5

Thank you for advice.

[eluser]CroNiX[/eluser]
Try the tutorials and the user guide. There are several examples on how to do these basic CI things.

[eluser]rip_pit[/eluser]
you can have a look there for every DB querying methods :
http://www.codeigniter.fr/user_guide/dat...ecord.html

You can use a ->join to query multiple tables using one unique query.
then use a simple foreach to filter the results and separate the cat.
Something like :

Code:

$this->db->select('blog.*, categories.id, categories.name');
$this->db->from( 'blog' );
$this->db->join( 'categories', 'categories.id = blog.cat_id', 'LEFT');
$this->db->order_by('blog.id','DESC');
$this->db->order_by('categories.name ','DESC');
$query = $this->db->get();

if ($query->num_rows() > 0)
{
  $results = $query->result();
  
  //do what filtering is needed
  foreach ($results as $key => $row) {
    echo $row->cat_id.', ';
  }

}
else
{
  $results = false;
}

return $results ;

[eluser]Marcus Marden[/eluser]
@CroNiX, this is not a basic question! very simple if you know CI well...

@rip_pit, thank for codes but it doesn't show category name, it is only get results from blog and category tables. I want to do more different job. Thanks anyway.

[eluser]egy_programozo[/eluser]
rip_pit's code v1.2

Code:

$this->db->select('blog.*, categories.id as category_id, categories.name as categories_name');
$this->db->from( 'blog' );
$this->db->join( 'categories', 'categories.id = blog.cat_id', 'LEFT');
$this->db->order_by('categories.name ','DESC');
$this->db->order_by('blog.id','DESC');
$query = $this->db->get();

if ($query->num_rows() > 0)
{
  $results = $query->result();
  
  //do what filtering is needed
  $memory = false;
  foreach ($results as $key => $row) {
    if ( $row->categories_name != $memory ){
        $memory = $row->categories_name;
        echo $mermory.'<br>';
    }
    echo ' -> '.$row->name.'<br>';
  }

}
else
{
  echo 'nothing to do here...';
}