CodeIgniter Forums
Query problems - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query problems (/showthread.php?tid=15875)

Query problems - El Forum - 02-17-2009

[eluser]Laranjeiro[/eluser]
Hi
I'm new at CI and I got a problem. I have been searching in the forum for a long time and I could not find the resolution for my problem.
I'm designing a web page for a motorcycle club. In one page is intended to keep a journal of their activities. I created a table called journal that contains, among other fields, title, id and body.
In the first page I want to show the last inserted item and a list of the last 20 titles.
My doubt is how to show the last item and how to keep the list of the last titles.
I could make a query that would retrieve all the information and them only show the info I need but I am a performance freak and I want to do things the right way.
Any suggestions?

Query problems - El Forum - 02-17-2009

[eluser]TheFuzzy0ne[/eluser]
I don't understand the problem. Please could you post your existing code with some comments on what you feel needs to be improved upon?

Query problems - El Forum - 02-17-2009

[eluser]jdfwarrior[/eluser]
Just run two queries. One to get the most recent post (select the article/item with the most recent post date). Then a second query to select the titles, ordered by their post date, limit 20

Query problems - El Forum - 02-17-2009

[eluser]Laranjeiro[/eluser]
I have found the solution.

The problem wasn't that complicated. I am just not used to CI yet.

What I wanted to do was when I clicked the journal link, it appear the last submitted information.
I solved it like

Code:
// last headline
        $this->db->select_max('id');
        $query = $this->db->get('journal');

        foreach ($query->result() as $row):
            $last = $row->id;
        endforeach;

        $this->db->where('id',$last);
        $data['query2'] = $this->db->get('journal');

For the headlines list

Code:
// headlines
        $this->db->select('title,id');
        $this->db->from('journal');
        $this->db->order_by("id", "desc");
        $data['query'] = $this->db->get();

For the selected headline
Code:
//selected headline
        $this->db->where('id',$this->uri->segment(3));
        $data['query2'] = $this->db->get('journal');

I divide it in two functions. One that would show the last inserted item and another for the selected item.
And that's it.

Thanks for all the help

Query problems - El Forum - 02-17-2009

[eluser]TheFuzzy0ne[/eluser]
Order by post date descending, and limit by 20. One simple query.

./system/application/models/journal_model.php
Code:
class Journel_model extends Model {
    
    function Model()
    {
        parent::Model();
    }
    
    function getLatest($limit="20")
    {
        $this->db->order_by('post_date', 'desc');
        $this->db->limit(0, $limit);
        return $this->db->get('some_table');
    }
The code above is untested.

Query problems - El Forum - 02-17-2009

[eluser]Laranjeiro[/eluser]
Ok
And how do I get the 20 next headlines?

Query problems - El Forum - 02-17-2009

[eluser]TheFuzzy0ne[/eluser]
Code:
class Journel_model extends Model {
    
    function Model()
    {
        parent::Model();
    }
    
    function getLatest($start_result=1, $offset=20)
    {
        $this->db->order_by('post_date', 'desc');
        $this->db->limit($start_result, $offset);
        return $this->db->get('some_table');
    }
}
The code above is untested.

Modify the function so that it accepts two parameters.

Code:
$this->journal_model->get_latest(1); // page 1
$this->journal_model->get_latest(21); // page 2
$this->journal_model->get_latest(41); // page 3


Query problems - El Forum - 02-17-2009

[eluser]Laranjeiro[/eluser]
Humm not quite what I expected but it might work.
Thanks