PHP SQL - Finding a record's positon in a list of results.

Author

Finding a record's positon in a list of results.

Shawn Wilson

2005-09-29, 9:56 pm

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):

-----
function get_pic_position($picid_sent,$setid) {
$query = " SELECT picid FROM `pic` WHERE `setid` = '$setid' ORDER BY `sort`
ASC, `picid` ASC; ";
$result = mysql_query($query) or die('Query failed: $query<br><br>' .
mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
foreach($line AS $_NAME => $_VALUE) { $$_NAME = $_VALUE; }
$array_pic_ids[] = $picid;
# buidling this array is redundant isn't it?
}

$array_pic_position = array_keys($array_pic_ids, $picid_sent);
# can't I do this search directly on the results somehow?

$pic_position = $array_pic_position[0];
# is there a cleaner way of turning this one record array into a non-array?

return $pic_position;
}
-----

But I'm just thinking that there has to be a shorter way to do that, right?

Thanks in advance everyone!

--
Shawn Wilson

Marc Bissonnette

2005-09-29, 9:56 pm

"Shawn Wilson" <firstinitial_lastname@dvigroup.net> wrote in
news:Eu%_e.13754$so3.8195@fe06.news.easynews.com:

> 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):
>
> -----
> function get_pic_position($picid_sent,$setid) {
> $query = " SELECT picid FROM `pic` WHERE `setid` = '$setid' ORDER BY
> `sort`
> ASC, `picid` ASC; ";
> $result = mysql_query($query) or die('Query failed: $query<br><br>' .
> mysql_error());
> while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
> foreach($line AS $_NAME => $_VALUE) { $$_NAME = $_VALUE; }
> $array_pic_ids[] = $picid;
> # buidling this array is redundant isn't it?
> }
>
> $array_pic_position = array_keys($array_pic_ids, $picid_sent);
> # can't I do this search directly on the results somehow?
>
> $pic_position = $array_pic_position[0];
> # is there a cleaner way of turning this one record array into a
> non-array?
>
> return $pic_position;
> }
> -----
>
> But I'm just thinking that there has to be a shorter way to do that,
> right?

Hrm, lemme take a crack at it (this is perl, hope you don't mind)

Here's the quick and dirty way, assuming that each picture is numbered
sequentially, as they're entered, with no skipped numbers:

$query = "SELECT count(*) FROM `pic` WHERE `setid` = '$setid'";
my $dbh;
my $sth;
$dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,{RaiseError=>
1});
$sth=$dbh->prepare($query);
$sth->execute();
$totalresults=$sth->rows;
$sth->finish;
$dbh->disconnect;
$picture_position= "Picture is $picid_sent of $totalresults pictures in
set";

If there are skipped positions:

$query = "SELECT `picid` FROM `pic` WHERE `setid` = '$setid' ORDER BY
`sort` ASC, `picid` ASC";
my $dbh;
my $sth;
$dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,{RaiseError=>
1});
$sth=$dbh->prepare($query);
$sth->execute();
$totalresults=$sth->rows;
$count = 1;
while (@row=$sth->fetchrow()) {
if ($row[0] == $picid_sent) {
$pic_position = $count;
}
++$count;
}
$sth->finish;
$dbh->disconnect;
--$count;
$picture_position= "Picture is $pic_position of $count pictures in set";

Does that help, or did I completely miss what you needed ?

--
Marc Bissonnette
CGI / Database / Web Management Tools: http://www.internalysis.com
Looking for a new ISP? http://www.canadianisp.com

Shawn Wilson

2005-09-30, 3:56 am

"Marc Bissonnette" <dragnet@internalysis.com> wrote in message
news:Xns96E0D447071Edragnetinternalysisc
@216.196.97.131...
> "Shawn Wilson" <firstinitial_lastname@dvigroup.net> wrote in
> news:Eu%_e.13754$so3.8195@fe06.news.easynews.com:
>
>
> Hrm, lemme take a crack at it (this is perl, hope you don't mind)
>
> Here's the quick and dirty way, assuming that each picture is numbered
> sequentially, as they're entered, with no skipped numbers:
>
> $query = "SELECT count(*) FROM `pic` WHERE `setid` = '$setid'";
> my $dbh;
> my $sth;
> $dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,{RaiseError=>
> 1});
> $sth=$dbh->prepare($query);
> $sth->execute();
> $totalresults=$sth->rows;
> $sth->finish;
> $dbh->disconnect;
> $picture_position= "Picture is $picid_sent of $totalresults pictures in
> set";
>
> If there are skipped positions:
>
> $query = "SELECT `picid` FROM `pic` WHERE `setid` = '$setid' ORDER BY
> `sort` ASC, `picid` ASC";
> my $dbh;
> my $sth;
> $dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,{RaiseError=>
> 1});
> $sth=$dbh->prepare($query);
> $sth->execute();
> $totalresults=$sth->rows;
> $count = 1;
> while (@row=$sth->fetchrow()) {
> if ($row[0] == $picid_sent) {
> $pic_position = $count;
> }
> ++$count;
> }
> $sth->finish;
> $dbh->disconnect;
> --$count;
> $picture_position= "Picture is $pic_position of $count pictures in set";
>
> Does that help, or did I completely miss what you needed ?
>
>
> --
> Marc Bissonnette
> CGI / Database / Web Management Tools: http://www.internalysis.com
> Looking for a new ISP? http://www.canadianisp.com

No, you've got the idea, but you're stepping through it like I am. I was
thinking there had to be a way to use that $sth array (since that's what it
is, is an array of your whole result set) and step through that without
having to assign the data to another variable or array. In my case, that
result array is stored in $result... but the idea is the same. So far, my
attempts to step through that array have failed but I was thinking it was
because I'm doing something wrong.

Maybe I'm just being too picky, but if I can avoid making a new array by
using one that is already populated that would make more sense to me.

--
Shawn Wilson

Hilarion

2005-09-30, 7:57 am

> 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'] );

Sponsored Links