find next record on click of link

@samohtJul 31.2007

Hello again,

I want to make a next and a previous link with my data.

here is my query:

[code=php] <?php $sql = “SELECT DISTINCT pdp.PackageId, p.PackageType FROM productpackaging pdp, packaging p WHERE pdp.ProductId =2 AND pdp.PackageId = p.PackageId”; $result = mysql_query($sql);[/code]

How do I write the code to get next or get previous??

Do I need to put the records in an array?

to post a comment

PHP

12 Comments(s) _↴

@JDM71488Jul 31.2007 — #if your URL is something like domain.com/products.php?id=2

you can get the product ID and decrement it and increment it to make the links for 1 and 3.

@samohtauthorJul 31.2007 — #Sorry,

I should have explained more. I am wanting to get the next packageId from my db and change my query based on the packageId - the page will stay the same url.

I am doing this because I have a html table displaying data for the package type - but I have too many package types to display them all at once - so I want to do it one at a time

Hopefully that helps

@JDM71488Jul 31.2007 — #you can have a function to get the package names and ids and return them in an array. you could get the index of your current package, and decrement and increment the index for the links.

@samohtauthorJul 31.2007 — #yep, that is what I want to do.

any ideas on how to do it?

I thought about having a couple of buttons that onClick would change the php variable - but that does not seem to be working?

@JDM71488Jul 31.2007 — #it will be hard to make a complete function without seeing your database tables, but this might help. hopefully it even works as it is untested.

[code=php]function get_packages()
 {
 // assuming connection exists
 
 $sql = mysql_query("SELECT packageID, packageName FROM packages ORDER BY packageID");
 
 while($row = mysql_fetch_assoc($sql))
 {
 $rows[] = $row;
 }
 
 return $rows;
 }
 
 $currentPackage = $_GET['packageID'];
 
 // don't forget to validate the packageID
 
 $packages = get_packages();
 
 $prevLink = false;
 
 $nextLink = false;
 
 for($i = 0; $i < count($packages); $i++)
 {
 if($packages[$i]['packageID'] == $currentPackage)
 {
 // check for previous link
 
  if($i != 0)
  {
   $prevLink = ($i - 1);
  }
 
  // check for next link
 
  if($i != (count($packages) - 1))
  {
   $nextLink = ($i + 1);
  }
 
  // done, stop searching
 
  break;
 }
 }
 
 // previous link
 
 if($prevLink)
 {
 echo "<a href="/store/?packageID={$packages[$prevLink]['packageID']}">{$packages[$prevLink]['packageName']}</a>";
 }
 
 // nextlink
 
 if($nextLink)
 {
 echo "<a href="/store/?packageID={$packages[$nextLink]['packageID']}">{$packages[$nextLink]['packageName']}</a>";
 }
 [/code]

this is assuming your packageIDs are not auto incremented. otherwise it will be a bit easier.

@samohtauthorJul 31.2007 — #yes, my packageId's are auto incremented

Also - I don't want to change the page (<a href...) but just update the the tables . Maybe I need AJAX??

Thanks for the help

@bokehJul 31.2007 — #The preferable way would be for your link URL to contain the current ID (not the next), plus a variable that states the direction (up or down). Then it is simply a matter of pulling the next row out of the DB. Something like this:

[code=php]switch($_GET['type'])
 {
 case 'Previous':
 $sql = "IFNULL(MAX(IF($priKey<'$id',$priKey,null)),MIN($priKey))";
 break;
 case 'Next':
 $sql = "IFNULL(MIN(IF($priKey>'$id',$priKey,null)),MAX($priKey))";
 break;
 case 'Last':
 $sql = "MAX($priKey)";
 break;
 default:
 $sql = "MIN($priKey)";
 }
 
 $result = mysql_query("
 SELECT *
 FROM $tablename 
 WHERE $priKey = (SELECT $sql FROM $tablename)
 LIMIT 1
 ");[/code]

This works with DATETIME columns also. Here's an example.

@samohtauthorAug 01.2007 — #Where would you set the $id in this example?

@samohtauthorAug 01.2007 — #jdm71488,

what if I set up the function like so:

[code=php]<?php
 function get_packages($pid)
 {
 $ProductId = $pid;
 $sql = mysql_query("SELECT DISTINCT pdp.PackageId, PackageType FROM productpackaging pdp, packaging p WHERE pdp.ProductId = $ProductId AND pdp.PackageId = p.PackageId");
 
   while($row = mysql_fetch_assoc($sql))
   {
   $rows[] = $row;
   }
 
   return $rows;
   }
   $currentPackage = $_GET['PackageID'];
 
   // don't forget to validate the packageID
 
   $packages = get_packages($ProductId);
 
   $prevLink = false;
   $clink = false;
   $nextLink = false;
 
   for($i = 0; $i < count($packages); $i++)
   {
   if($packages[$i]['PackageID'] == $currentPackage)
   {
   // check for previous link
 
   if($i != 0)
   {
   $prevLink = ($i - 1);
   $clink = ($i);
   }
 
   // check for next link
 
   if($i != (count($packages) - 1))
   {
   $nextLink = ($i + 1);
   $clink = ($i);
   }
 
   // done, stop searching
 
   break;
   }
   }
   mysql_free_result($result);
   ?>
 
   <td><?php 			// previous link
 
   if($prevLink)
   {
   echo '<button onclick="" ><<</button>';
   }
 
   // nextlink
 
   if($nextLink)
   {
   echo '<button onclick="" >>></button>';
   } ?></td>
   </tr><?php 
 
   ?>
   <tr>
   <td><div align="center" style="font-size:smaller;"><?php echo $packages[$clink]['PackageType']; ?></div></td>
 [/code]

Can I set up the button onclick event to go to the previous and next record?

Ultimately I would like to use the button to change a variable for the PackageId so I can display my table of prices differently for each PackageId

@bokehAug 01.2007 — #Where would you set the $id in this example?[/QUOTE]It would be based on the current ID.

@samohtauthorAug 02.2007 — #bokeh,

I am interested in using your approach but need a little more help setting it up.
[code=php]<?php switch($_GET['type']) [/code] does 'type' refer to previous, next etc or to the package type? I assume the latter.

I assume I need a query of the product and all its related packages - and from that query I will get the current Id (which will just be the first one in the query) - and that this query will precede the switch case function??

@bokehAug 02.2007 — #does 'type' refer to previous, next etc or to the package type? I assume the latter.[/QUOTE]No, the former. Here's the example script so you can see how it all works in context.

[code=php]<?php
 
 mysql_connect(/*******/);
 mysql_select_db(/*******/);
 $tablename = 'images';
 $priKey  = /*******/;
 $ImagePath = /*******/;
 
 $id = preg_replace('/D/', '', @$_GET['referer']);
 
 switch(@$_GET['type'])
 {
 case 'Previous':
 $sql = "IFNULL(MAX(IF($priKey<'$id',$priKey,null)),MIN($priKey))";
 break;
 case 'Next':
 $sql = "IFNULL(MIN(IF($priKey>'$id',$priKey,null)),MAX($priKey))";
 break;
 case 'Last':
 $sql = "MAX($priKey)";
 break;
 default:
 $sql = "MIN($priKey)";
 }
 
 $result = mysql_query("
 SELECT *
 FROM $tablename 
 WHERE $priKey = (SELECT $sql FROM $tablename)
 LIMIT 1
 ");
 
 if(mysql_num_rows($result))
 {
 $row = mysql_fetch_assoc($result);
 $id = urlencode($row[$priKey]);
 $content = "<p>n  <img src='$ImagePath{$row['src']}' width='{$row['width']}' height='{$row['height']}' alt='Image: {$row['id']}'>n </p>n";
 }
 else
 {
 $content = "<p class='advise'>Sorry, the image table is empty!</p>n";
 }
 
 $links  = ' <p class="pagination-links">' ."n  ".
 links('First'       )."n   |n  ".
 links('Previous',$id)."n   |n  ".
 links('Next',    $id)."n   |n  ".
 links('Last'        )."n </p>n ";
 
 header('Content-Type: text/html; charset=ISO-8859-1');
 ob_start('ob_gzhandler');
 
 ?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
 "http://www.w3.org/TR/html4/strict.dtd">
 
 <html lang="en">
 <head>
 <style type="text/css">
 p.pagination-links{clear:both;font-weight:bold;}
 p.pagination-links a{color:#369;}
 p.pagination-links a:hover{color:#f63;}
 p.advise{color:#DD002B;font-weight:bold;}
 p{margin:1em auto;text-align:center;}
 </style>
 <title>First, Previous, Next, Last</title>
 </head>
 <body>
 <?php echo $links.$content ?>
 </body>
 </html><?php
 
 function links($get, $referer = null)
 {
 $referer and $referer='&amp;referer='.$referer;
 return '<a href="'.htmlentities($_SERVER['PHP_SELF']).'?type='.$get.$referer.'">'.$get.'</a>';
 }
 
 ?>[/code]

Also in #PHP _↴

multiple drop down lists $php_self mailer and array

Success!

Help @samoht spread the word by sharing this article on Twitter...

Tweet This

find next record on click of link

12 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

find next record on click of link

12 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

12 Comments(s) _↴

Also in #PHP _↴