Need help with writing a query!!!

[eluser]heylarson[/eluser]
Suppose I have a mysql table called 'events' that looks as such.

Code:

id  start_date  title
--  ----------  -----
1   08-01-2010  foo
2   07-13-2010  bar
3   08-10-2010  foobar
4   07-21-2010  blah
5   08-16-2010  blah blah

The URL references the events by their event id as such, http://foo.com/event/2.

I would like to put a prev and next event links, on the current event the user is looking at. Obviously, the prev/next event links would point to the nearest events based off their 'start_date' and referenced by their 'id'.

For example, if you're looking at, http://foo.com/event/1, the prev link would be, http://foo.com/event/4, and the next link would be, http://foo.com/event/3.

I have tried sorting the events by 'start_date' and return the results as an array, but I can't seem to get the surrounding event id's for the currently viewed event.

Any help would be REALLY appreciated!!

[eluser]Bart v B[/eluser]
Something like this?

Code:

$sql = "SELECT * FROM events ORDER BY start_date ASC";

A date or datime in SQL is written like this: 2010-01-01
So i guess that your using a VARCHAR field, and there you can not doing things with.
I think you can make it better a DATE or a DATETIMe column for that. :-)

[eluser]heylarson[/eluser]
I currently have:

Code:

//get the details for a particular event based off the event id
function GetEventView($id)
{
    $this->db->select('id')->from('events')->order_by('start_date', 'desc');
    $query = $this->db->get();
    
    // $query->result() should contain the array of event id's
    // the id of the event that the user is currently viewing
    // is passed as a parameter. I need to somehow capture the
    // event id of the event that occurs next in time.
}

[eluser]Bart v B[/eluser]
[quote author="heylarson" date="1282698566"]I currently have:

Code:

//get the details for a particular event based off the event id
function GetEventView($id)
{
    $this->db->select('id')->from('events')->order_by('start_date', 'desc');
    $query = $this->db->get();
    
    // $query->result() should contain the array of event id's
    // the id of the event that the user is currently viewing
    // is passed as a parameter. I need to somehow capture the
    // event id of the event that occurs next in time.
}

[/quote]

Is this a good start to look?
Pagination Class

Then it should be something like this:

Code:

function GetEventView()
{
    $this->db->select('id');
    $this->db->from('events');
    $this->db->order_by('start_date DESC');
    $this->db->limit(1, $this->uri->segment(3));
            
    return $this->db->get();  
}


function Getpagnination()
{
    //set pagination parameters
        $config['base_url'] =  base_url().'events/index';
        // counts how many rows
        $config['total_rows'] = $this->db->count_all('events');            
        $config['per_page'] = '1';
        $config['full_tag_open'] = '<div id="pagination">';
        $config['full_tag_close'] = '</div>';
        
        return $this->pagination->initialize($config);
}

in your Conroller:

Code:

$data['pagination'] = $this->pagination->create_links();

And everything goes atomatic ;-)

[eluser]heylarson[/eluser]
I appreciate the feedback, but I think i'm looking more along the lines of how to take the results from a query and access individual values like an array. For example, I have the following function:

Code:

function GetEventView($id)
{
    $query = $this->db->select('id, start_date')->from('events')->order_by('start_date', 'desc')->get();
    print_r($query->result());
}

The results are:

Code:

Array
(
    [0] => stdClass Object
            (
                    [id] => 5
                    [start_date] => 08-16-2010
            )

    [1] => stdClass Object
            (
                    [id] => 3
                    [start_date] => 08-10-2010
            )

    [2] => stdClass Object
            (
                    [id] => 1
                    [start_date] => 08-01-2010
            )

    [3] => stdClass Object
            (
                    [id] => 4
                    [start_date] => 07-21-2010
            )

    [4] => stdClass Object
            (
                    [id] => 2
                    [start_date] => 07-13-2010
            )

)

If someone is looking at event id 1 (which is position 2 in the array), I want to be able to store the next event in the array, which is event id 4 (position 3 in the array), and store the id (which is 4) into a variable, let's call it $next.

Don't get hung up on the format of the date, i've abbreviated much of this code to just give the gist of what i'm looking for.

[eluser]heylarson[/eluser]
Alright, so after my racking my brain, I figured it out:

Code:

function GetEventView($id)
{
    $sql = "SELECT id FROM events ORDER BY start_date DESC";
    $result = $this->db->query($sql)->result();
    for($i = 0; $i < count($result); $i++)
    {
        if($result[$i]->id == $id)
        {
            $prev = $result[$i-1]->id;
            $next = $result[$i+1]->id;
        }
    }
}