0

I am using @Nev Stokes very elegant pagination script:

here's the SO link

my version of the script is currently configured to scroll through each row of my sql table one row at a time using the prev/next buttons. I am trying to add in a drop down list that spits out all of the sql rows and clicking one of them advances the page to the correct page #.

The pagination component is working fine. For the drop down list however, I'm having trouble setting the href value for each list item.

After counting the number of sql rows, the pagination script defines the current page using the following:

// How many items to list per page
  $limit = 1;
// How many pages will there be
  $pages = ceil($total / $limit);
// What page are we currently on?
  $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
    'options' => array(
        'default'   => 1,
        'min_range' => 1,
      ),
  )));

It then adds 1 to $page for the next button, and subtracts 1 from $page for the previous button, and writes into the href for the button. ex)

<a href="?page='.($page + 1).'"title="Next page">

As for the drop down list I'm using:

//begin dropdown list
  echo '<div class="dropdown" style="display:inline; margin-left:5%;">
  <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-expanded="true">
   Browse by address
    <span class="caret"></span>
  </button>
     <ul class="dropdown-menu" role="menu" aria-labelledby="dropdownMenu1">';
//get all sql rows
 $stmtdd = $conn->prepare('SELECT * FROM listings Order by id DESC');
 $stmtdd->execute();
 $stmtdd->setFetchMode(PDO::FETCH_ASSOC);
 $iteratordd = new IteratorIterator($stmtdd);
  foreach ($iteratordd as $row) {
    $adrdd = $row['address'];
//print the address and link to the page
     echo '<li role="presentation"><a role="menuitem" tabindex="-1" href="?page='.$currpagehref.'">'.$adrdd.'</a></li>';}
     echo '</ul>
  </div>';

How would I define $currpagehref (the href for the list item), it needs to ireterate the number in the sql row count essentially? Thank you in advance!

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
rhill45
  • 559
  • 10
  • 35
  • 2
    So the question here is are you going to be removing rows in the table at any point in time? If so, then you might have a problem just linking the page number and would rather need to link the id of the row. In other words, what is the authoritative way by which you need to refer to to the item ion the URL? Is it OK for an item to be found at page 10 one day and then at page 9 another day when one of the first nine id's in the table have been removed. – Mike Brant Jan 21 '15 at 20:46
  • @MikeBrant yes some rows will be removed! In fact when they are it throws off my id number which is an autoincremented value. For ex, if i have 45 rows, and I delete 5 of them, the next time a row is added the id number will be 46, so ,my rows will go 39...40...46 in terms of their unique id. Is rowIndex related to this var that I set up? I really need to understand this cause it's caused probs in my db before. Answer below seems to work though, but i prob need to test more still. – rhill45 Jan 21 '15 at 20:50
  • 1
    That answer will work if your only expected use case for getting to these pages is to use pagination navigation to get through them, but what happens if someone bookmarks a certain page and then you delete pages in front of it? The bookmark breaks. Is seems that you have a very specific degenerate use case of pagination where there is one item per page, which I would argue is not really a true pagination use case at all where you would expect to get more than one item on a page such that the page number is the more important item. I would suggest looking at navigation based on row id – Mike Brant Jan 21 '15 at 20:55
  • 1
    I would also state that you should not fight against the autoincrement sequence in the DB (i.e. by backfilling gaps in id sequence) The autoincrement id is really used ONLY for providing a guaranteed unique index value for each row. Gaps in the sequence should not be a concern for a well-conceived application. – Mike Brant Jan 21 '15 at 20:58
  • That's a good point, it's a page for editing and I had trouble getting one of the ajax functions working for the correct item, when multiple items were listed on the page. But it needed this server side division for page speed. I completely forgot about this and your right it needs to be fixed (i knew i would be revisiting that for some reason one day ;-) ) – rhill45 Jan 21 '15 at 20:59

1 Answers1

2

Since you are using 1 page per row, you can use the index of the row within the iterator to use as your page number.

Change your foreach for this :

foreach ($iteratordd as $rowIndex => $row) {

And define your $currpagehref this way

href="?page='.($rowIndex + 1).'"

$rowIndex is 0-based, so page 1 is for $rowIndex = 0, and so on.

potashin
  • 44,205
  • 11
  • 83
  • 107
Cedric
  • 61
  • 5
  • this has to be the most simple perfect answer, thank you very much @Cedric. I never knew that var existed, and I can prob use it to solve other probs on my site. – rhill45 Jan 21 '15 at 20:44
  • The `$index` var in a foreach loop is indeed very helpful. Glad it helps. I have to admit that @MikeBrant's comment would make the entire structure more robust. Also, your script would benefit from parting with the pagination altogether, as it adds complexity without benefit in this case. Refactoring your code the way he suggests is certainly the way to go. – Cedric Jan 22 '15 at 01:35