find next record on click of link

[samoht]

Hello again,

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

here is my query:

<?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);

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

Do I need to put the records in an array?

[JDM71488]

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.

[samoht]

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

[JDM71488]

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.

[samoht]

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?

[JDM71488]

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.

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>";
} 

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

[samoht]

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

[bokeh]

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:

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
"); 

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

[samoht]

Where would you set the $id in this example?

[samoht]

jdm71488,

what if I set up the function like so:

<?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>

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

[bokeh]

Where would you set the $id in this example?

It would be based on the current ID.

[samoht]

bokeh,

I am interested in using your approach but need a little more help setting it up.

<?php switch($_GET['type'])

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??

[bokeh]

does 'type' refer to previous, next etc or to the package type? I assume the latter.

No, the former. Here's the example script so you can see how it all works in context.

<?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>';
}

?>