www.webdeveloper.com
Results 1 to 13 of 13

Thread: find next record on click of link

Hybrid View

  1. #1
    Join Date
    Jul 2007
    Posts
    49

    find next record on click of link

    Hello again,

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

    here is my query:

    PHP Code:
    <?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?

  2. #2
    Join Date
    May 2003
    Location
    Pensacola, FL
    Posts
    635
    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.

  3. #3
    Join Date
    Jul 2007
    Posts
    49
    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

  4. #4
    Join Date
    May 2003
    Location
    Pensacola, FL
    Posts
    635
    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.

  5. #5
    Join Date
    Jul 2007
    Posts
    49
    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?

  6. #6
    Join Date
    May 2003
    Location
    Pensacola, FL
    Posts
    635

    Post

    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.

    PHP Code:
    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.

  7. #7
    Join Date
    Jul 2007
    Posts
    49
    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

  8. #8
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739
    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:
    PHP Code:
    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.
    Last edited by bokeh; 07-31-2007 at 04:52 PM.

  9. #9
    Join Date
    Jul 2007
    Posts
    49
    Where would you set the $id in this example?

  10. #10
    Join Date
    Jul 2007
    Posts
    49
    jdm71488,

    what if I set up the function like so:
    PHP Code:
    <?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

  11. #11
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739

  12. #12
    Join Date
    Jul 2007
    Posts
    49
    bokeh,

    I am interested in using your approach but need a little more help setting it up.
    PHP Code:
    <?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??

  13. #13
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739
    Quote Originally Posted by samoht
    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 Code:
    <?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>';
    }

    ?>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles