Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to return simply "the next row after the current row" from mysql DB

Posted on 2003-03-12
3
Medium Priority
?
156 Views
Last Modified: 2013-12-25
Here is my scenario,

I am trying to create a next / previous buttons for my image gallery which is all stored in a table "images" that has various ID's (some have been deleted so I cant just add 1 each time)

I am trying to simply return the next row after the current displayed row, so i can incoporate this into the next button.

I thought maybe I should load all the ID's in an array, and then identify the next and previous values in the array, but not sure how to do this.

Thanks for help in advance!.
0
Comment
Question by:scottwright421
3 Comments
 

Expert Comment

by:markyello
ID: 8118816
Hey,
i tried to do that too, what i recommend is you make an array, and on the NEXT button click to get the next picture use the action='GET' in the <form> html code...

thats too confusing..
never mind
just use an array
like:
my $pics[4];
my $pics[0] = "mypic.Gif";
...
0
 
LVL 2

Accepted Solution

by:
Itatsumaki earned 300 total points
ID: 8132567
What you want to do is return both the image & the ID in your query, and sort on ID.  So your first query is:

###############
my $sql = '
SELECT    id, image
FROM      my_table
ORDER BY  id';

my $sth = $dbh->prepare($sql);
$sth->execute();
my @row = $sth->fetchrow_array();
###############

Now you want to pass $row[0] (i.e. the id) on your forward button.  Then, on your next page you'll use the query:

###############
my $lastval = $cgi->param('lastval');

my $sql = '
SELECT    id, image
FROM      my_table
WHERE     id > ?
ORDER BY  id';

my $sth = $dbh->prepare($sql);
$sth->execute($lastval);
my @row = $sth->fetchrow_array();
###############

And if you want to do this really pretty with just a single CGI file, you pass two parameters on your forward & backward buttons: 'direction' and 'lastval'.

Then to pick out the correct query to run you use an if loop like this:

###############
my $dir = $cgi->param('direction');
my $lastval = $cgi->param('lastval');
my $sql;
my $sth;

if ($dir eq 'forward') {
    $sql = 'SELECT    id, image
            FROM      my_table
            WHERE     id > ?
            ORDER BY  id';
    $sth = $dbh->prepare();
    $sth->execute($last);
    }
elsif ($dir eq 'back') {
    $sql = 'SELECT    id, image
            FROM      my_table
            WHERE     id < ?
            ORDER BY  id';
    $sth = $dbh->prepare();
    $sth->execute($last);
    }
else {
    $sql = 'SELECT    id, image
            FROM      my_table
            ORDER BY  id';
    $sth = $dbh->prepare();
    $sth->execute();
     }
##########

Let me know if any of this is unclear,
Tats
0
 

Author Comment

by:scottwright421
ID: 8134914
very nice work, helped out heaps!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question