Mysql recordset help
Jon
I'm currently working on a photo gallery project that is requiring quite a
bit of PHP/mySQL and am having trouble. Right now, I have a program that is
bringing in records from the DB, and using a 'next' button to cycle through
those records. Currently, the record I'm obtaining is running off of an
incrementing variable (nextImage) . I'm able to increment the nextImage
variable, and link through each record in the DB while moving to the next
using one link on the page.
The problem - I now need to have the recordset I'm grabbing from the DB be
based off of a Category ID - Each image has a catID associated with it (in
the same table as a FK). My problem is that as I'm incrementing the
nextImage variable by 1 each time which is used to call the next photoID,
the query fails when I reach a photoID with a catID that's not specified.
My question is: How can I cycle through a recordset of data from the DB,
without knowing what the next ID will be? Right now, I can say "SELECT *
FROM table WHERE catID = $variable" - however, I need to move through each
record in this query via an HTML link - so when the user clicks 'Next', it
needs to not go to 'currentImage + 1' for example, but it needs to move to
the actual next value in our recordset.
Any help is appreciated, thank you in advance.
Anze
Hi Jon!
This is not a nice way, but it would work. You'll have to use your field
names, of course, but the principle is easy...
Next id:
'SELECT min(id) FROM table WHERE id>'.$currentId
Previous id:
'SELECT max(id) FROM table WHERE id<'.$currentId
Have fun!
Anze
Shawn Wilson
news:wygYe.335$h6.1...@news.siol.net...
I like Anze's solution actually, it seems sort of elegant.
I personally have the same kind of setup and have thumbnail pages that cycle
through pages with 15 thumbnails per page and then the individual images
that go one at a time. I have an 'albumid' tied to each picture and then a
'fname' field for the filename that I'm looking for next.
This is how I do it.
for album list:
SELECT albumid,blurb FROM albums LIMIT $start_at, $return_how_many
for thumbnails:
SELECT fname,caption FROM pics WHERE albumid = 'albumid' LIMIT $start_at,
$return_how_many
for singles:
SELECT fname,caption FROM pics WHERE albumid = 'albumid' LIMIT $start_at,
$return_how_many
I send into my functions the $start_at and $return_how_many variables.
The start at is figured based on how many pics I put per page. So for
albums and thumbnails the $return_how_many is set to 15 always, for singles,
it's set to 1.
the $start_at var is set by each page having a page number in the URL
(i.e. album.php?page=2)
I then take that page number and use the per page number to figure out what
record to start at.
For thumbnails I take that page number (say it's page 2) and multiply that
by the number per page (15) to get 30, then subtract the number per page to
get 15 in this case. (because my page numbers start at 1 and records start
at 0)
page 1 produces:
SELECT fname,caption FROM pics WHERE albumid = 'albumid' LIMIT 0, 15
(because page 1 times 15 equals 15, minus 15 equals 0)
page 2 produces:
SELECT fname,caption FROM pics WHERE albumid = 'albumid' LIMIT 15, 15
(because page 2 times 15 equals 30, minus 15 equals 15)
LIMIT tells SQL to only return to you the results starting at the record
number you specify and only return the number of results you specify. For
page 2, I told SQL to start at record 15, and give me 15 results.
For singles, I use the same principle. Each picture gets a page number, and
I step through them with LIMIT.
pic 1 produces:
SELECT fname,caption FROM pics WHERE albumid = 'albumid' LIMIT 0, 1
pic 5 produces:
SELECT fname,caption FROM pics WHERE albumid = 'albumid' LIMIT 5, 1
Unlike the thumbnails, the single pics don't require much math to figure out
what record to return. I increment the page var in the PHP when I make the
prev and next links. I take the current page number and increment for the
next link and decement for the prev link.
Of course, I do a count to find out how many pages are possible first so I
don't go past the limit, and I change any page number 0 or less to 1 so I
don't run under into negative numbers.
That's my current way, but maybe the other way is simpler. But it only
acounts for a single step where my option lets you do from 1 to as many at
at time as you need (like 15 per page for thumbnails)
--
Shawn Wilson
Stefan Rybacki
The problem on this solution is you can't find the next item if you want to have the items
ordered by name or something.
Stefan
PS: we had such a diskussion not that long ago at
http://groups.google.com/group/alt.php.sql/browse_frm/thread/f86dacb1b139dfee/3f053fb2a6da0bd6?tvc=1&
>
> --
> Shawn Wilson
>
>
Jon
Thanks for the help. I unfortunatley think I'm going to have to rewrite my
entire design to implement this. Maybe someone else has some last minute
advice for me on my current style before I go this route.
I have essentially solved the problem moving through the records with a next
button - What I did, was run through each record in a loop using
mysql_fetch_array - and inside the loop, I've created and assigned an array
with each index corrosponding with the current photoID and fileName - here's
the code:
$sql = "SELECT photoID, fileName FROM photoGallery WHERE catID = $category";
$dataFile = mysql_query($sql);
while($photoList = mysql_fetch_array($dataFile)){
$newNextPhoto[] = $photoList['photoID'];
$newPhoto[] = $photoList['fileName'];
}
So basically, my link is using $newNextPhoto and $newPhoto (the ID and
filename), as well as passing $i and incrementing it's value so I can find
where I'm at in the array. The link looks like this:
<a href = "photoDisplay.php?i=<?php echo($i); ?>&photoID=<?php
echo($newNextPhoto[$i]); ?>&fileName=<?php echo($newPhoto[$i]);?>">Next</a>
The image tag is pretty simple: <img src="dev_images/<?php
echo($newPhoto[$i]); ?>">
So... My problem is from the full gallery page now - I have a list of
photos, and when the user clicks on one of them, I want to be able to send
in that photo's ID as well as a value of $i (to know where we start the
loop). The problem is that when $i comes in, it is incremented automatically
by the display page (one photo at a time with the next button), and the
photo that gets displayed is always 1 ahead of where it is supposed to be.
Anyone have a clue on how I can pass $i in once without incrementing it,
while still needing to increment for the next button?
I probably got too complex with this, I'm kind of a C++ guy so this stuff is
new to me. Any help is once again appreciated.
"Stefan Rybacki" <stefan....@gmx.net> wrote in message
news:3pfcbsF...@individual.net...
Jon
selected from my gallery page to the displayImage page, I'm decrementing $i
by one. So if the array is at 0, it becomes -1 once it hits the image page,
and is instantly incremented by that page before the photo gets displayed.
Seems to work.
My only question is if anyone can see any catestrophic problems on the
horizon with the method I've chosen here. Functionality seems great, and if
a user inserts new images to the DB, or even new categories, my code doesn't
care as it's running the links of the array itself. Any problems that
someone can see before I begin to push this to production?
"Jon" <jo...@netins.com> wrote in message
news:dguf8q$4vp$1...@news.netins.net...
Stefan Rybacki
> OK, I've actually figured it out. In the link passing the current picture
> selected from my gallery page to the displayImage page, I'm decrementing $i
> by one. So if the array is at 0, it becomes -1 once it hits the image page,
> and is instantly incremented by that page before the photo gets displayed.
> Seems to work.
>
> My only question is if anyone can see any catestrophic problems on the
> horizon with the method I've chosen here. Functionality seems great, and if
> a user inserts new images to the DB, or even new categories, my code doesn't
> care as it's running the links of the array itself. Any problems that
> someone can see before I begin to push this to production?
> "Jon" <jo...@netins.com> wrote in message
> news:dguf8q$4vp$1...@news.netins.net...
>
Well if I got you right, you still could have some problems with sorted lists.
Imagine you want to print out the pictures ordered by name (say we have names: A B and C)
All work fine until you are at picture B and somebody adds AA to the list, while you are
looking at B. Pushing Next will show picture B again. Well I don't know whether this is
bad or not, just to mention.
Another thing is the overhead of getting all pictures everytime you push the next button.
Its ok for 100 pictures but could take some time for thousands of pictures, think about it.
Stefan
Jon
right now with roughly 50 pictures... Even with lots of records, I'm not TOO
worried, as I'm only really bringing in text data anyways, plus I don't
think our user base will get much over 10 pics per category on average.
I also thought about the sorting thing... It's definately going to be
difficult when inserting new pics and having current users of the gallery,
as it's re-querying every time - I might look into a way to fix that down
the road.
Thanks for the advice :)
"Stefan Rybacki" <stefan....@gmx.net> wrote in message
news:3pg8cvF...@individual.net...
Anze
Thanks... :)
Too bad it's a bit inefficient - but for small number of images it doesn't
really matter. Time spared is much more valuable.
Regards,
Anze