Posted by Marc Bissonnette on 09/30/05 03:52

"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

[Back to original message]