|
|
Posted by Hilarion on 09/30/05 13:25
> This was almost asked recently, but not quite how I need to use it. I tried
> to extrapolate, but alas I need some clarification.
>
> What I am doing is displaying an image in a photo album. I get to the page
> in question with this:
>
> detail.php?picid=101
>
> That page then calls a function to find out what set that pic belongs to,
> and then I need to know that pics position in the whole scheme of things.
> i.e. this is pic 5 of 10.
>
> Up to this point, I have the picid and the setid it belongs to.
>
> Since the mySQL return is an array that I normally step through, shouldn't I
> be able to skip stepping through it myself and simply search the return from
> mySQL for what I'm looking for and pull out it's position in the array
> without stepping through it?
>
> here's the long way (that works):
>
> [...]
>
> But I'm just thinking that there has to be a shorter way to do that, right?
In Oracle 8i it would be something like this (AFAIK it will not work in MySQL
because it does not have ROWNUM pseudocolumn or any like it):
SELECT rn
FROM (
SELECT picid, ROWNUM rn
FROM pic
WHERE setid = '$setid'
ORDER BY sort ASC, picid ASC
)
WHERE picid = '$picid'
This one should work in Oracle and in MySQL:
SELECT COUNT(*)
FROM pic
WHERE setid = '$setid'
AND ((sort < '$sort') OR (sort = '$sort' AND picid < '$picid'))
This one above requires getting 'sort' for the current 'picid',
but you can do it by one single query. This way you do the whole
job by two queries and no PHP which is much faster (databases
are always faster in such cases).
Hilarion
PS.: If "picid" and "sort" are numeric fields, then you do not
need to enclose them in single quotes (apostrophes) in
the queries. You should be prepared for users messing
with parameters, but it's easy to do by:
$picid = @intval( $_GET['picid'] );
Navigation:
[Reply to this message]
|