Problems with querys

[eluser]mikeyhell[/eluser]
I'm trying to write an app that returns the previous and next rows based on what the current query is and it's not working? The way I understand it, if you return a query w/ the row #4 the next row would be #5 and previous would be #3. Instead I get the same row returned for all 3.

$this->db->where('id', $id);
$query= $this->db->get('entries');
$data['next']= $query->next_row();
$data['last']= $query->previous_row();

??

[eluser]James Gifford[/eluser]
Actually, I believe that those functions are used to "walk" through the rows.

When you call 'next_row' since that is the first call made on the query it returns the current row (row 4). Then when you call 'previous_row' it walks back to the last row which is also row 4.

To get the current, previous and next rows, I think you'll need to do something like this:

Code:

$this->db->where(’id’, $id);
$query = $this->db->get(’entries’);
$data[’current’] = $query->row(); // get the current row
$data[’previous’]= $query->previous_row(); // go back one row
$query->next_row(); // go forward one row to the current row
$data[’next’]= $query->next_row(); // go forward one more row

[eluser]mikeyhell[/eluser]
I still get the same row returned. If you have a chance here's the page:

http://www.1001webpromotions.com/guestbook/posts/id/9

If you notice, the id is in the URI(9) ... the text at the very top is the result of $data['next'}= $query->next_row()

[eluser]James Gifford[/eluser]
Oh, I overlooked something critical!

In your example query, you're getting a result that only has one row. So no matter how many times you call next_row and previous_row you'll always get the same row.

You'll need to change your query so that it returns more than one row. Something like:

Code:

$this->db->where(’id >=’, $id - 1)->where(’id <=’, $id + 1);
$query = $this->db->get(’entries’);

[eluser]mikeyhell[/eluser]
so is it possible to return with a negative offset or is this the incorrect way?

[eluser]mikeyhell[/eluser]
oh thanks ... the sql worked perfectly but I had to change the chaining since this server is still php4

[eluser]James Gifford[/eluser]
I just tried a simpler example on a local database and I got the desired results:

Code:

$this->db->where(’id >=’, $id - 1)->where(’id <=’, $id + 1)->orderby(’id’);
$query = $this->db->get(’entries’);        
$data[’previous’] = $query->previous_row();
$data[’current’] = $query->next_row();
$data[’next’] = $query->next_row();

This assumes that there are valid ids within the specified range.

[eluser]mikeyhell[/eluser]
Nevermind, the rows aren't consistent b/c a row could be deleted. Should be next row - 11 previous row 7 if you're on row 9 for instance.

[eluser]mikeyhell[/eluser]
Is this being referenced by an internal mySql row id or by the actual id itself?

[eluser]James Gifford[/eluser]
The query is looking at the actual values stored in the id field in your table. If you want to get a record with an id of 4 then you would also want to get ids 3 and 5 as well.

Of course, since you are allowing records to be deleted and there might be gaps, this method won't be 100% effective.