View Lists of Values ​​with PHP and MySQL

Posted on August 30, 2013 by giampy107

Let’s see how it is possible to list a certain number of records in a page written in PHP with the ability to navigate between the values ​​of residents of a MySQL database. The first thing to do is create a list in a file to be placed on a data server, for this purpose I will use the common table in a MySQL database, precisely to name “Vikings” on my test server, but the same operation can be accomplished very easily on any other server. So let’s see the code of the PHP page that shows a list of 20 values ​​per page with possibility of moving going back and forth:

<?php

   // Take page values
   $page = $_REQUEST['page'];
   $valxpage = $_REQUEST['valperpage'];

   if (strlen($page) <= 0)
      $page = 1;
   if (strlen($valxpage) <= 0)
      $valxpage = 20;

   // I open the connection to the town in the database Vikings
   $my_server = mysql_connect("localhost", "root", "") or
       trigger_error(mysql_error(),E_USER_ERROR);
   mysql_select_db("vikings", $my_server);
   $query = "SELECT * FROM towns ORDER BY Town";
   $towns = mysql_query($query, $my_server) or
       die(mysql_error());
   $row_towns = mysql_fetch_assoc($towns);
   $tot_towns = mysql_num_rows($towns);

   // Calculate last page
   $last_page = (int)($tot_towns / $valxpage);
   if (($tot_towns % $valxpage) > 0)
      $last_page++;

  // Take a mouvement
   if (strlen($_REQUEST['next']) > 0)
      $page++;
   else if (strlen($_REQUEST['last']) > 0)
      $page = $last_page;
   else if (strlen($_REQUEST['previous']) > 0)
      $page--;
   else if (strlen($_REQUEST['first']) > 0)
      $page = 1;

   // I go to the page selected
   if ($page > 1)
   {
      $numrec = $valxpage * ($page - 1);
      for ($i = 0; $i < $numrec; $i++)
      {
         $row_towns = mysql_fetch_assoc($towns);
         if (!$row_towns)
           break;
      }
   }
?>

<html>
<head><title>Towns List</title></head>
<body>

<table width="800" align="center" cellpadding="0"
        cellspacing="0" border="0">
<tr>
   <td align="center" height="50" valign="middle" colspan="5"
       bgcolor="#bababa" style="font-size:24px;font-weight:bold"
       >Towns List</td>
</tr>
<tr>
   <td width="200" align="center" height="40" valign="middle"
        bgcolor="#eeeeee" style="font-size:18px;font-weight:normal">
        <i>Town</i></td>
   <td width="200" align="center" height="40" valign="middle"
        bgcolor="#eeeeee" style="font-size:18px;font-weight:normal">
        <i>C.A.P.</i></td>
   <td width="200" align="center" height="40" valign="middle"
        bgcolor="#eeeeee" style="font-size:18px;font-weight:normal">
        <i>Province</i></td>
   <td width="200" align="center" height="40 "valign="middle"
        bgcolor="#eeeeee" style="font-size:18px;font-weight:normal">
        <i>Region</i></td>
   <td width="200" align="center" height="40" valign="middle"
        bgcolor="#eeeeee" style="font-size:18px;font-weight:normal">
        <i>Other.</i></td>
</tr>

<?php

   for ($i = 0; $i < $valxpage; $i++)
   {
      if (!$row_towns)
         break;

      echo '<tr>';
      echo '<td width="200" align="center">'.
           $row_towns['Town'].'</td>';
      echo '<td width="200" align="center">'.
           $row_towns['CAP'].'</td>';
      echo '<td width="200" align="center">'.
           $row_towns['Province'].'</td>';
      echo '<td width="200" align="center">'.
           $row_towns['Region'].'</td>';
      echo '<td width="200" align="center">'.
           $row_towns['Other'].'</td>';
      echo '</tr>';

      $row_towns = mysql_fetch_assoc($towns);
   }

?>

<tr>
<td align="center" valign="middle" height="100" colspan="5">

   <form action="#" method="POST" name="navigazione">
<?php
   if ($page > 1) echo '<input name="first"
        type="submit" id="first" value="<< First Page">';
   if ($page > 1) echo '<input name="previous"
        type="submit" id="previous" value="< Previous Page">';
   echo " page $page di $last_page ";
   if ($page != $last_page) echo '<input name="last"
        type="submit" id="next" value="Next Page >">';
   if ($page != $last_page) echo '<input name="last"
        type="submit" id="last" value="Last Page >>">';

   echo '<input name="page" type="hidden" id="page"
        value="'.$page.'">';
   echo '<input name="valperpage" type="hidden"
        id="valperpage" value="'.$valxpage.'">';
?>
</form>

</td>
</tr>
</table>

</body>
</html>

<?php

   mysql_free_result($towns);
   mysql_close($my_server);

?>

You must excuse me for going to head in an unorthodox way, due to the narrowness of the page, but I am sure that the sense of what I wanted to do is understand anyway, because your job is to understand the code and propose it again, certainly not then copy and paste, as I have explained many times before.
As I always do I retrieve the data from the topics in the first part of the page, so I made ​​the connection to the database, a process that should be familiar to you if you have been following my progress on the PHP language. I run the query by ordering the values ​​for common account and the total number of records that will help me to calculate the total number of resulting pages. Getting the values ​​from the submit button, then that is what the user has selected, proceed to increase or decrease the count of pages that will allow us to advance the exact number of records in order to get the values ​​to be submitted. The rest of the code is concerned to iterate over records to display and show them formatted in a table at the end of which I insert the keys and the page count.
Of course, everything can be improved, this approach I’ve used it only to show the basis of this procedure then you will use every time there is a need to manage lists of values ​​from a MySQL database.

This entry was posted in PHP. Bookmark the permalink.