Adding navigation buttons to a simple web SQLite database

by David Halls


Screenshot of database display

Click image to see working database display

This builds on the database developed in a previous article. This described how SQLite and PHP5 with PDO could be used to produce a simple web database. Now we are going to add navigation buttons to allow the user to select the First, Previous, Next and Last record.

The navigation buttons

The four navigation buttons are added within the control form at the top of the display page underneath the dropdown box. All are of the type "submit" and, as defined in the original code, will cause the reloading of the same file with the action "GET". All have the name "navigate" and we will use this to test after submission whether a navigation button has been pressed or the dropdown menu has been selected.

<button type = "submit" name = "navigate" value = "First">First</button>
<button type = "submit" name = "navigate" value = "Prev">Prev</button>
<button type = "submit" name = "navigate" value = "Next">Next</button>
<button type = "submit" name = "navigate" value = "Last">Last</button>

Amendments to code

We need to make two amendments to the code to give us more information:

Acting on the control used for submission

The get method could return up to three parameters depending on which control is used for submission:-

  1. "navigate" if one of the navigation buttons is pressed
  2. "rowno" if the dropdown menu is used.
  3. "currentrow" if either is used.
if ($_GET[navigate])
{
switch ($_GET[navigate])
{
case "First":
  $rowno = 0;
   break;
case "Prev":
  if ($_GET[currentrow]!= 0)
    {$rowno = $_GET[currentrow] - 1;}
   else 
    {$rowno = 0;}
   break;
case "Next":
	 if ($_GET[currentrow]!= $lastrow)
  	 	{$rowno = $_GET[currentrow] + 1;}
    else
   {$rowno = $lastrow;} 
    break;
case "Last":
$rowno = $lastrow;
  
  break;
}
}

First we test whether a navigation button has been pressed by seeing whether there is a value for $_GET[navigate]. If there is, a switch routine choses the action based on its value. First and Last are straightforward with $rowno being equal to 0 or $lastrow, respectively. For Prev and Next, we need to make sure we do not go off the ends. For Prev, if the value of $_GET[currentrow] is not 0, we make $rowno equal to $_GET[currentrow] - 1. If it is 0, we keep $rowno as 0. For Next, we test to see whether $_GET[currentrow] is not equal to $lastrow. If so, we increment the row number by 1, otherwise the rownumber is kept at the value for $lastrow.

Then we test whether the dropdown menu was used for submission, in which case there will be a value for $_GET[rowno] and we set $rowno to the value of $_GET[rowno]. The last possibility is that there was no submission at all, i.e. the page is first loaded. In this case, $rowno is set to 0 so that the first record is displayed.

elseif ($_GET[rowno])
{
$rowno = $_GET[rowno];}

else
{$rowno = 0;}

Conclusion

In VBScript with OBDC, we can navigate the recordset with the methods movefirst, moveprevious, movenext and movelast. With SQlite, PDO and PHP, we do not have these functions, but, as this article shows, it is not difficult to devise your own code to carry out these functions.

You can see this further development of the database working here.