?
Solved

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

Posted on 2003-03-12
3
Medium Priority
?
155 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

801 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