Developer Forum

I am working a photo database and I ran into a problem, let's say someone
selects a photo from a selection, in order to view the large version they
must click on it.

Knowing the id of the image, a simple search is performed and the large
version of this image is displayed.

now I would like the user to click on the large image which will
advanced them to the next large in the database (start pagination from this
point one image at a time)
so.. my question is: "How in mysql can i get the next record/image of what the
person is currently viewing"

Thanks!


--
Paul Cartier

Paul:

So, do you have a table 'images' with contents like

id int(x) autoincrement
smallimg either filename or blob
mediumimg either filename or blob
largeimg either filename or blob

??

I assume you have a hyperlink around the image, like

<a href="imageproducer.php?imgid=123&size=M"><img ....></a>


MySQL runs queries. Your application has to build that query, based on
information from the link the visitor clicks. So, you must make sure
that:
a) you have a hyperlink with the proper information
b) you have a script that interpretes the information, builds a query,
receives the image, and displays it.

At what point do you have a question?


--
Erick

My database contains the following structure:
Vendor varchar(20)
ID varchar(50)
Caption varchar(50)
Image varchar(25)

the ID field is not an autoincrement field, the ID's have values like
"SDK10001"

Thanks for replying.
--
Paul Cartier

Paul:


OK. So your images have an ID, a caption, a pointer to a vendor table
(I hope) and something called "image".
What is the latter (and if it is a filename, where a re the filenames
of the larger images?)

--
Erick

The large image is the same name as the thumb image, the location where they
are stored are in different dirs. ie: Thumbs/ and Preview/ (preview being
the larger images)

--
Paul Cartier

Paul:


Since that information is not in your database, your application must
fetch the data, based upon the querystring (or whatever way you pass
information - may as well be a javascript parameter in an AJAX
environment).

Your question was:

The answer is: you can't, since there is no "next" and "previous"
pointer in your database structure.



--
Erick

Thats what i figured! thanks for helping out and confirming what I thought.
--
Paul Cartier

n 21 Aug, 21:08, Paul < XXXX@XXXXX.COM > wrote:
> > smallimg either filename or b>o>
> > mediumimg ither filename or b>o>
> > largeimg either filename or >lo> >>
> > ?> >>
> > I assume you have a hyperlink around the image, >ik> ><
> >>/a> >>
> >> now I would like the user to click on the large image which >i>>
> >> advanced them to the next large in the database start pagination from>t>>s
> >> point one image at a >i>>)
> >> so.. my question is: "How in mysql an i get the next record/image of wha> >>e
> >> person is currently vie>in>"> >
> > MySQL runs queries. Your application has to build that query, bas>d>on
> > information from the link the visitor clicks. So, you must make>s>re
> > >h>t:
> > a) you have a hyperlink with the proper inform>t>on
> > b) you have a script that interpretes the information, builds a q>e>y,
> > receives the image, and display> i>.> >
> > At what point do you have a ques>io>?
>
> My database contains the following struc>ure:
> Vendor varcha>(20)
> ID varcha>(50)
> Caption varcha>(50)
> Image varcha>(2>)
>
> the ID field is not an autoincrement field, the ID's have values>like
> "SDK1>00>"
>
> Thanks for repl>ing. >> --
> Paul Cartier

Well, first you have to decide on what is to be meant by "next" in
this instance. Records in a table have no order. Ordering is done by
specifying an ORDER BY clause. This may sort the result set from a
query, or it may use an index to retrieve the records from the table
in the specified order.

Consider the following dataset, whose columns represent id, last
update date, size

1, 2009-07-30, 200
7, 2008-01-03, 500
3, 2009-04-22, 100
2, 2008-01-03, 150

If I consider it to be in id order, the next record after 3 is 7
If I consider it to be in last update date order, the next record
after 3 is 1
If I consider it to be in size order, the next record after 3 is 2

In the above example, my Primary Key (PK) is the id. In reality, this
is the only ordering I can use to have a true before/next record,
since there is no reason why more than one record may not share a last
update date or size with another record. In this case I would most
likely attach the PK after one of the other fields in the ORDER BY
clause, so as to give a real sense of order.

Once I have done this, finding the "next" record is trivial. For
example, I am looking at an ordering of last update date and currently
viewing record id 2. My query for the next record will be:
SELECT
required_fields
FROM table
WHERE last_update_date = '2008-01-03' >ND id > 2 OR last_updat>_date >
'2008-01-03'
ORDER BY last_update_date, id
LIMIT 1

Everything Erick said is correct. Now I have another question for you,
Paul. What logic is used *ANYWHERE* do determine what is next in the
sequence? Simply saying "Gee, I want the next one" is not good enough
unless you know HOW to determine what will be the next one. IOW, what
criterea are used to determine a "sequence"? Would the next in your
example be SDK10002 or would it be XYZ99991? You haven't told us enough.

Maybe this will help:

First I display a list of images for the user to choose from (thumb view).
I can page through all these images by using the LIMIT From,Count.

$sql = "SELECT ID,CAPTION,THUMB,VENDOR FROM tblPhotos WHERE CAPTION =
\"$this->topic\" ORDER BY TIMESTAMP DESC LIMIT $from,$count"

Now the user clicks on a thumb image (I have the id in the image url)
and then I just simply fetch the image directly from the database based on ID
and display the large view.

It would be great if the user could now just click the large image to get to
the next image.

Trying to figure out what's next is impossible , the next image a really
want is the next thumb image that was on the previous page that contain the
list of thumb images.

I think i said that right :)



--
Paul Cartier

n 21 Aug, 21:57, Paul < XXXX@XXXXX.COM > wrote:

Whoa there Paul! Where did all these extra fields come from?

You told us that your table had only 4 fields and now you are showing
us a query which references 5 fields, two of which are not in the
table!!!

Now, I have already written you a detailed post explaining exactly
what you need to so. In particular, you have to do what I said in that
post, which is to "define what is meant by next". I gave some examples
of how to do this. All the time you leave it to chance, your task
remains impossible and there is no point asking how to do the
impossible.

Now I notice that you are ordering by a column called timestamp. Now
if you take that a bit further and make sure that you have a constant
and unique ORDER BY clause, precisely as I have already explained in
my post, you can implement a "next picture" process.

n 2009-08-21, Captain Paralytic < XXXX@XXXXX.COM > wrote:

Sorry about that! I will review your previous post again and pick this back
up on Monday.

Thanks for all the help guys and have a great weekend!

--
Paul Cartier

Paul:



Perhaps I was just lucky, then. After reading this, I realized that I
misunderstood the initial question.



....and I thought Paul wanted the next larger version of the same
image (going from thumbnail to normal to XXL, or so). Silly me.


Now, since Paul wants the next image from a result set he already had,
he could simply use the same query with added criteria.
If 'timestamp' is unique, it would be better to keep 'timestamp' as an
ID to fetch the next record. Another simple solution would be to order
the initial collection of thumbnails by ID (not timestamp) and go from
there.

If it's really necessary to order the thumbnails by timestamp AND keep
the ID as the only reference, then the required query to fetch the next
record would include a self join to get the timestamp of the current
image first.


...and if nothing works, he could always carry the initial resultset
around in a SESSION-variable and present 'next images' from that array.
Never mind those few images that get deleted from the database in the
meantime. :-)



--
Erick

I think it is actually simpler than that. By the query he put up
recently, it appears that he is using php. Since that query brings back
the entire array, he already has all the ids, all the information he
needs, and in the order he wants. All he now needs to do is keep track
whether the user is looking at thumbnail, middle, or large picture.

The next gives him the next id and knowing which one he has clicked, he
simply goes to the location of that next image using the id and size.
All that is now done using the original array without having to re-query
the database. It can be be done in straight php (session variable or
hidden element to pass the array) or done with javascript where the
update is the appropriate next image.

--
Shelly

A basic assumption in what I just wrote is that earlier he stated he
keeps the images in three separate directories, one for each type, and
all he needs is the ID to get the image. If he needs the ID in order to
query the database to get the name of the file, then he should modify
his original query with a join to get the names.