Skip to main content

Getting Next and Previous Item by ID From MySQL Database With PHP

If you know the ID of an item you want to get from a MySQL database with PHP, but you also want to get the next and previous items when the IDs are in numerical order but may contain gaps due to deleted items, you can do something like this:

$curID = NULL;
  $curRow = NULL;
  $prevID = NULL;
  $nextID = NULL;
  $lastID = NULL;

  // Get the latest one (for the "Last" link)
  $result = mysql_query("SELECT itemID FROM tableName ORDER BY itemID desc LIMIT 1");
  $row = mysql_fetch_array( $result );
  $lastID = $row['itemID'];

  // Get the ID for the requested item
  if (isset($_GET['curID'])) {
    // if $_GET['curID'] defined, use it as curID
    $curID = $_GET['curID'];
  } else {
    // Use the latest one
    $curID = $lastID;
  }

  // Get requested row and next/prev rows if they are there
  $sql = "
    SELECT * FROM tableName WHERE tableName.itemID IN (
      (select itemID from tableName where itemID < $curID order by itemID desc LIMIT 1),
      (select itemID from tableName where itemID = $curID),
      (select itemID from tableName where itemID > $curID order by itemID asc LIMIT 1)
    )
  ";
  $result = mysql_query($sql);

  while ( $row = mysql_fetch_array( $result ) )
  {
    if ( $row['itemID'] == $curID)
      $curRow = $row;
    else if ( $row['itemID'] < $curID )
      $prevID = $row['itemID'];
    else if ( $row['itemID'] > $curID )
      $nextID = $row['itemID'];
  }

  // DEBUG stuff
  echo "<font color=red>prevID=" . $prevID . "</font><br>";
  echo "<font color=red>curID=" . $curID . "</font><br>";
  echo "<font color=red>nextID=" . $nextID . "</font><br>";
  echo "<font color=red>lastID=" . $lastID . "</font><br>";
  echo "<font color=red>sql=" . $sql . "</font><br>";

  // The postDate column in this case has the value like '2010-05-11'
  // There is probably a better way to handle dates, but this is the data I was given
  $dateArray=explode('-', $curRow['postDate']);
  $postDate = date('M j, Y', mktime(0, 0, 0, $dateArray[1], $dateArray[2], $dateArray[0]));

  mysql_close($link);
  mysql_free_result($result);

  // Print the next/prev links
  $self = $_SERVER['PHP_SELF'];
  $first = '';
  $prev = '';
  $next = '';
  $last = '';

  if ($curID > 1)
    $first = " <a href=\"$self?curID=1\"><< First </a> ";

  if ($nextID != NULL)
    $next = " <a href=\"$self?curID=$nextID\">Next ></a> ";

  if ($prevID != NULL)
    $prev = " <a href=\"$self?curID=$prevID\">< Prev </a> ";

  if ($lastID != $curID)
    $last = " <a href=\"$self?curID=$lastID\">Last >></a> ";

  // print the page navigation link

  // There is probably a better way to do this to, but this is the code I was given.
  echo '<br><div id="navigator" align="center">';
  echo $first . "  " . $prev . "    <b>" . $postDate . "</b>  " . $next . "  "  . $last;
Labels:

Comments

Post a Comment

Popular posts from this blog

Running PowerShell commands from Linux

There are several options for running PowerShell commands from Linux. Run the PowerShell script over a REST interface Unless you need a remote shell, the easiest option is to set up a REST interface for your PowerShell scripts. More information here . Using the winrm Ruby Gem https://github.com/WinRb/WinRM Using a WS-Management client on Linux Set up Windows for remote access: https://github.com/Openwsman/openwsman/wiki/winrm-over-openwsman-setup Install OpenWSMAN on Linux: http://openwsman.github.io/ Use Openwsman Command-Line Client: https://github.com/Openwsman/openwsman/wiki/openwsman-command-line-client OR - Use Ruby client bindings: http://users.suse.com/~kkaempf/openwsman/ Install an SSH server on Windows Install a Salt Minion on Windows Install Salt Master on Linux Install Python on Windows Install Salt Minion on Windows Open firewall on Windows for Salt access On Linux, run: # salt "winServer" cmd.run "powersh...
1 comment
Read more

How to make an HTTP request with PowerShell

If you are making an HTTP request to a RESTful web service, you can use the PowerShell  Invoke-RestMethod cmdlet. This provides a very simple HTTP REST interface, and will also format the result into a PowerShell object. If you would like to use your own functions, you can follow the instructions below. This is a helper function to format (indent) an XML response from a web service. function Format-XML { Param ([string]$xml) $out = New-Object System.IO.StringWriter $Doc=New-Object system.xml.xmlDataDocument $doc.LoadXml($xml) $writer=New-Object system.xml.xmltextwriter($out) $writer.Formatting = [System.xml.formatting]::Indented $doc.WriteContentTo($writer) $writer.Flush() $out.flush() Write-Output $out.ToString() } Here is the function to make the http call. It dumps the response data on the terminal and also returns it as a string to the caller. If there is an error it will dump the HTTP status code and comment on the terminal and return the re...
6 comments
Read more

A simple IIS HTTP module to log request headers and post data

This is a simple http module that hijacks the incoming request and logs it to a file. Using this module prevents the application from getting the post data because the input stream can only be read once. With .NET 4.0 the module could be much smaller, and could allow the application to read the data. To compile this, create a new "Class Library" application called PostDataLogger. Most of this class is pieced together from stolen code (stack overflow, etc.) using System; using System.IO; using System.Web; /// /// Dump PostDataLogger.DLL in your bin, and put this in your web.config inside of /// /// /// /// public class SimpleLogger : IHttpModule { private HttpApplication _ctx; public void Init(HttpApplication context) { _ctx = context; context.BeginRequest += new EventHandler(Context_BeginRequest); } void Context_BeginRequest(object sender, EventArgs e) { string GUID = Guid.NewGuid().ToString(); string filename = @"d:\temp\adeber...
Post a Comment
Read more