Cycling through database records with 'next' button

[eluser]sore eyes[/eluser]
hi, I'm going round in circles with this one. I want to cycle through each record using 'first' 'next' etc buttons.

The 'shop_view' code is:

Code:

<h1>&lt;?=$title?&gt;</h1>

&lt;?=form_open('welcome/shop_insert');?&gt;
<p>Shop ID &lt;input type="text" name="shop_id" value="&lt;?=$query-&gt;shop_id?&gt;" /></p>
<p>Shop Name &lt;input type="text" name="shopname" value="&lt;?=$query-&gt;shopname?&gt;" /></p>
<p>Shop Name &lt;input type="text" name="phone" value="&lt;?=$query-&gt;phone?&gt;" /></p>
<p>&lt;input type="submit" value="submit" /&gt;&lt;/p>
&lt;/form&gt;


&lt;!--Go to the next page--&gt;
&lt;?php $shop_id=$query->shop_id ?&gt;
&lt;?php echo form_open('welcome/enterNextShop/'.$shop_id);?&gt;
<p>&lt;input type="submit" value="goNext"   /&gt;&lt;/p>
&lt;/form&gt;

which uses a button to run the control:

Code:

function enterNextShop()
    {
        $data['main'] = 'shop_view';
        $data['title'] = "Shop";
        $shop_id = $this->uri->segment(3);
        $data['query'] = $this->MShops->getNextShop($shop_id);
        $this->load->vars($data);
        $this->load->view('template');
    }

The model for which is:

Code:

function getNextShop($shop_id)
    {
        $this->db->where('shop_id >',$shop_id);
        $this->db->select('*');
        $data = $this->db->get('shop');
        return $data->row();
    }

It does work and I can cycle through each record, but the address given is http://localhost/retailers/welcome/enterNextShop/5 (say), whilst the shop_id given in the view menu is 6. i.e. they do not tally. I can see why it does not work, but don't know how to fix it. Perhaps I've taken the wrong fork in the road. So your help would be much appreciated.

[eluser]darkhouse[/eluser]
I think you should be doing things differently. Instead of passing $shop_id, why not pass $page, that way you can you just tell it what "page" to display rather than getting the next shop greater than the current id.

You controller would be:

Code:

function shop($page){
     $data['main'] = 'shop_view';
     $data['title'] = "Shop";
     $data['page'] = $page;
     $data['query'] = $this->MShops->getShop($page);
     $data['total_shops'] = $this->MShops->totalShops();
     $this->load->vars($data);
     $this->load->view('template');
}

This would be your model:

Code:

function getShop($page){
     $this->db->order_by('shop_id');
     $data = $this->db->get('shop', 1, $page - 1);
     return $data->row();
}

function totalShops(){
     $data = $this->db->query('SELECT COUNT(*) AS total_shops FROM shops');
     $row = $data->row();
     return $row->total_shops;
}

So now you can create some pagination buttons like this:

Code:

<a href="http://localhost/retailers/welcome/shop/1">First</a>
<a href="http://localhost/retailers/welcome/shop/&lt;?=($page-1)?&gt;">Previous</a>
<a href="http://localhost/retailers/welcome/shop/&lt;?=($page+1)?&gt;">Next</a>
<a href="http://localhost/retailers/welcome/shop/&lt;?=$total_shops?&gt;">Last</a>

[eluser]sore eyes[/eluser]
hi darkhouse, many thanks for responding with such detail. I do appreciate your time and effort. I was getting a little worried as there had been little response.

I did initially go down the road of using records rather than shop id's. But there appears to be some problems with this. Firstly, for instance, if I wish to do a search for a shop, it's difficult to end up with that shop on the page with the navigation buttons. In general, navigation using the shop id is easier. This is the prime reason I chose to navigate using shop_id.

Secondly, if there is more than one user accessing the database, then the total number of records is constantly changing, as is the record id for each shop. So if one user deletes a record at the beginning of the database, then another user will find his/her 'next' 'previous' navigation buttons may not respond in the expected way.

I would like your thoughts on this, as I am a bit of a newbie.

[eluser]ChrisMiller[/eluser]

Code:

function getNextShop($shop_id)
{
    $this->db->where('shop_id >',$shop_id);
    $this->db->select('*');
    $data = $this->db->get('shop');
    return $data->row();
}

Just a quick thing you may want to limit the selection to limit only one row, this query could become very slow if you have a very large amount of rows in your database.

Try this code it will give you the same result but only return one row of data which will be much faster in the end.

Code:

function getNextShop($shop_id)
{
    $this->db->where('shop_id >',$shop_id);
    $this->db->select('*');
    $this->db->limit(1);
    $data = $this->db->get('shop');
    return $data->row();
}

[eluser]darkhouse[/eluser]
Ok, well what about doing this. Instead of using the database directly for your pagination, maybe you could just get the list of id's the first time the user comes to the page, and store the array in the session. Then use your pagination like I suggested, but just to traverse the array of id's. So the $page variable would really be the array index, instead of the query offset.

One issue you would run into is like you said when someone deletes a record. You're going to have this problem no matter what solution you go with, but what I think you should do in this case is when you get an id from the array, you grab the data from the database, but if it doesn't exist, recreate the array, and grab the proper id for that 'page'.