Module 0302: Database entry browsing and selection

Assuming a SELECT query is already performed, the following code can be used to display rows corresponding to the results of the query:

<?php
  // assumptions:
  //   queryResult is the result of a previous mysqli_query
  //   rowCSSClass contains the name of a CSS class for each row
  //   itemCSSClass contains the name of a CSS class for each item on
  //     a row
  print "<table>";
  print "<th>...</th>"; // specify the header
  while ($row = mysqli_fetch_row($queryResult)) // for each row
  {
    print "<tr>";            // begin tr
    foreach ($row as $item)  // for each item on this row
    {
      print "<td>$item</td>"; // generate the td
    }
    print "</tr>";           // end tr
  }
  print "</table>";
?>

3 Paging SQL query results

For a query that has a lot of rows, it may not be eﬀective to display all rows on the same page. This problem can be ﬁxed by the use of the LIMIT clause in a SELECT statement.

To use this feature, the PHP script needs to know the number of entries to display on a single page. This can be a ﬁxed number, but it may be best to make it a variable so that browsers with a larger display can display more rows. The following code may be used to specify the number of rows.

<?php
  // declare global variables that will be used in the script
  $numRows = array(10, 25, 50, 100);
  $strNumRows = ’numrows’;
?>

<?php
  // to interpret the current numrows _GET param
  // note that some forms use _POST, change this code
  // accordingly then
  if (isset($_GET[$strNumRows]) and
      $numRowsIndex = $_GET[$strNumRows] and
      $numRowsIndex < count($numRows))
  {
  }
  else
  {
    $numRowsIndex = 0;
  }
  $currentNumRows = $numRows[$numRowsIndex];
  // from here on, just use currentNumRows as the number of rows
  // to print per page
?>

<?php
  // generate anchors, not too useful inside a form
  foreach ($numRows as $key=>$numRowsOption)
  {
    print ’<a␣href=\’’; // begin of an anchor
    print basename(__FILE__); // the name of this script
    print "?$strNumRows=$key’>"; // specify the GET param
    print $numRowsOption."␣per␣page"; // text inside the anchor
    print ’</a>’; // end of anchor
  }
?>

<?php
  // alternative code to generate radio buttons in a form
  foreach ($numRows as $key=>$numRowsOption)
  {
    print "<input␣type=’radio’␣name=’$strNumRows’␣value=’$key’>"
    print $numRowsOption."␣"; // text inside the anchor
  }
  print "␣per␣page";
?>

To perform paging, you will also need to track the start row of the current page. In a form, this is best done using a “hidden” type input element. Outside of a form, this has to be a parameter speciﬁed in the “href” attribute.

The script should always check to see if such a ﬁeld exists. If not, the default should be 0 (the LIMIT clause is zero oriented). The

Next, we also need to generate the navigation links/controls. To generate such links/controls, it is important to ﬁrst know the number of rows in a query.

<?php
  // assume $query is a SELECT statement
  // assume $link is an established link
  if ($queryResult = mysqli_query($link, $query))
  {
    $rowTotal = mysqli_num_rows($queryResult);
  }
  else
  {
    // something went wrong, possibly "die" here
  }
?>

This approach works, but it also has a performance issue. PHP actually needs to get the entire result of the query to do this counting. This means that if the result ends up with thousands of rows, all these rows need to be communicated from the SQL server back to the server running the PHP script ﬁrst.

A better approach is to use SQL to do the counting, as demonstrated in the following approach:

<?php
  $query = "select␣count(*)␣from␣sometable";
  if ($queryResult = mysqli_query($link, $query))
  {
    $rowCount = mysqli_fetch_row($queryResult)[0];
  }
  else
  {
    die($query.’␣failed:␣’.mysqli_error($link));
  }
?>

Because this approach is generally very useful, it makes sense to make it a function.

Once we know the total number of rows, we can generate navigational links. Most navigation links has a “previous” and a “’next’. Some also includes a few pages around the current page. For simplicity, we will just generate links to all pages.

<?php
  for ($pageNumIndex = 0;
       $pageNumIndex < intdiv($rowTotal, $currentRowNum);
       ++$pageNumIndex)
  {
    print ’<a␣href=\’’; // beginning of anchor
    print basename(__FILE__); // print my own file name
    print ’?’; // the parameters start here
    print "$strRowNum=$currentRowNum&$strPageNum=$pageNumIndex";
    print ’>’; // end of beginning of element
    print ($pageNumIndex+1).’ ’;
    print ’</a>’;       // end of anchor
  }
?>

Note how this code does not actually encode the start row. This allows the PHP code to compute and validate the start row when it is time to display content of a page like follows:

<?php
  if (isset($_GET[$strPageNum]))
  {
    $pageNum = $_GET[$strPageNum];
    if ($pageNum < intdiv($rowTotal, $currentNumRow) and $pageNum >= 0)
    {
      // $pageNum is valid, it is ok
    }
    else
    {
      // page num is not okay, default to 0
      $pageNmm = 0;
    }
  }
  else
  {
    // page num is not defined, default to 0
    $pageNum = 0;
  }
?>

With the start row computed (it is zero oriented), we can now specify the query string.

<?php
$query = "select␣*␣from␣sometable␣limit␣".
($pageNum * $currentRowNum).",".$currentRowNum.’;’;
?>

The rest of the code to actually take each row to display is the same as before.

4 “Indexing” each row

In certain queries, it is important to index each row so that it can be selected and identiﬁed. This can be for the purposes of editing a single entry, or deleted entries that are selected (checkboxes).

Multi-delete can be supported by the use of a checkbox per row. The code to generate a checkbox may look like the following:

<?php
print "<input␣type=’checkbox’␣name=’$strMultiSelect’␣value=’$row[0]’>";
?>

This code assumes the name of the checkbox is stored in the variable “strMultiSelect” and the unique ID of each row is the ﬁrst ﬁeld. This approach requires a submit button that speciﬁes the action of “delete selected”.

The matching code to act on the submit button click may look like the following:

<?php
  if (isset($_GET[$strMultiDeleteSubmit] and
      isset($_GET[$strMultiSelect])))
  {
    $multiSelect = $_GET[$strMultiSelect];
    if (is_array($multiSelect))
    {
      $cleanList = ’(’;
      $first = true;
      foreach ($multiSelect as $item)
      {
        if (!$first)
        {
          $cleanList = $cleanList.’,’; // comma to separate
        }
        $cleanList = $cleanList.mysqli_real_escape_string($link, $item);
        $first = false;
      }
      $cleanList = $cleanList.’)’;
      // now we have a clean list
      $query = "delete␣from␣sometable␣where␣id␣in␣$cleanList;";
      if ($queryResult = mysqli_query($link, $query))
      {
        // it is done!
      }
      else
      {
        die($query."␣failed:␣".mysqli_error($link)."\n");
      }
    }
  }
?>

<?php
print "<a␣href=’".basename(__FILE__)."?$strEditId=’$row[0]’>edit</a>";
?>

The matching code to generate the form with items pre-ﬁlled may look like the following:

<?php
  if (isset($_GET[$strEditId]))
  {
    $qeury = "select␣*␣from␣sometable␣where␣id=’".mysqli_real_escape_string($link,$_GET[$strEditId])."’;";
    if ($queryResult = mysqli_query($link, $query))
    {
      if ($row = mysqli_fetch_row($queryResult))
      {
        // generate form with prefilled inputs:
        // such as
        print "field2␣<input␣type=’text’␣name=’field2’␣value=’$row[2]’><br>";
        // don’t forget the hidden field of the ID so the handler
        // logic knows which row in the database table is to be
        // updated
        print "<input␣type=’hidden’␣name=’$strEditId’␣value=’$row[0]’>";
      }
      else
      {
        // cannot find a row matching this ID, hack attempt?
        // may want to log this event
        print "edtry␣not␣found";
      }
    }
    else
    {
      // query failed, may need to log and report this
      print "query␣failed";
    }
  }
?>

When the submit button of the form is clicked, whichever PHP is to process it may have the following code:

<?php
  if (isset($_GET[$strEditSubmit]))
  {
    // edit triggered
    // optional but highly recommended: validate all the fields to
    // to updated first before proceeding to update the database!
    $query = ’update␣sometable␣set␣’;
    // for each field, do the following or something similar:
    $query = $query.’␣fieldx=\’’.mysqli_real_escape_string($link,$_GET[$strFieldx]),’\’␣’;
    $query = $query.’␣where␣id=’.$_GET[$strEditId].’;’;
    if ($queryResult = mysqli_query($link,$qeury))
    {
      // successful!
    }
    else
    {
      // something went wrong
    }
  }
?>