[RESOLVED] alpahbetical next link

[klair]

I have an image gallery and the following function is used to scroll through the large images once a single image has been selected from the thumbnail gallery.

What is happening is that although on the first call to "Next" it correctly goes to the next image (alphabetically), but ... the second call to "Next" takes it back to the very first record instead of the next picture? (could this be because of the "ORDER BY pic_name"?). Does anyone have a solution?

<?php
function shownext ($gallery_id, $pic_name) {
$gallery_id = $GET['gallery_id'];
$pic_name = $GET['pic_name'];

$result = @("SELECT gallery_id, pic_name FROM gallery WHERE foldername='2003' AND location='gallery' AND pic_name >'$pic_name' ORDER BY pic_name LIMIT 1");
if(@mysql_num_rows($result) == 1){

$next_id = mysql_result($result, 0, 'gallery_id');
$next = "<a href=$self?gallery_id=$next_id>Next</a>";
} else {
$next = "Next";
}
echo $next;
}
?>

[axsdeny]

Can you show the table design so I can replicate the db on my end and test?

[ednark]

I tried your sql logic on a alphabetical table and it seemed to work fine

INDEX.PHP

<?php

/*--------- DATABASE CONNECTION INFO---------*/ 
$hostname       = "localhost"; 
$mysql_login    = ""; 
$mysql_password = ""; 
$database       = "test"; 

// connect to the database server 
$db = mysql_pconnect($hostname, $mysql_login , $mysql_password);
$name = !empty($_GET['name']) ? $_GET['name'] : 'aa'; 

echo "NAME : $name <br />\n";

/*
CREATE TABLE `alphabet` (
  `name` char(2) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `alphabet` (`name`) VALUES ('aa'), ('bb'), ('cc'), ('dd'), ('ee'), ('ff'), ('ee'), ('ff'), ('gg'), ('hh'), ('ii'), ('jj'), ('kk'), ('ll'), ('mm'), ('ee'), ('nn'), ('oo'), ('pp'), ('qq'), ('rr'), ('ss'), ('tt'), ('uu'), ('vv'), ('ww'), ('xx'), ('yy'), ('zz');
*/
$sql = "
    SELECT 
        name 
    FROM 
        alphabet 
    WHERE 
        name>'$name'
    ORDER BY 
        name 
    LIMIT 1
"; 

print "<pre>";
print_r($sql);
print "</pre>";

$result = mysql_query($sql);

if(mysql_num_rows($result) == 1){ 
    $next_id = mysql_result($result, 0, 'name'); 
    $next = "<a href=\"./?name=$next_id\">Next Found : $next_id</a>"; 
} else { 
    $next = "No Next Found (<a href=\"./?name=aa\">start over</a>)"; 
} 
echo $next; 

?>

perhaps it is in your logic for only passing the gallery id between pages, but im assuming there is only one picture for each gallery.

[klair]

Thanx for all the replys!

To AXSDENY:
The applicable table structure is below:

CREATE table gallery (
gallery_id int not null primary key auto_increment,
pic_name varchar (255),
image_thumb varchar (255),
image_large varchar (255));

To EDNARK:

I just tried the following:

print "<pre>"; 
print_r($result); 
print "</pre>"; 

This produces "Resource id #6"?

Perhaps I can try and elaborate to make it clearer as to what my gallery is doing:

Say I have the following structure in my thumbnail gallery sorted alphabetically:
No 1: name=a, id=1
No 2: name=b, id=2
No 3: name=c, id=4
No 4: name=d, id=3
No 5: name=e, id=5

The following code, once a thumbnail image is selected, the large image format, produces the following results:

$result = @mysql_query("SELECT gallery_id FROM gallery WHERE pic_name >'$pic_name' ORDER BY pic_name LIMIT 1");

If I select "No 1" then select "Next" it goes correctly to "No 2", the second time I select "Next" it goes back to "No 1". Thereafter it stays on "No 1".
If I select "No 4" then select "Next" it goes correctly to "No 5", the second time I select "Next" it goes back to "No 1". Thereafter it stays on "No 1".

$result = @mysql_query("SELECT gallery_id FROM gallery WHERE pic_name >'$pic_name' LIMIT 1");

If I select "No 1,2,3 or 5" then select "Next" it goes to "No 4". Thereafter it stays on "No 4".
If I select "No 4" then select "Next" it goes correctly to "No 5", the second time I select "Next" it goes back to "No 4". Thereafter it stays on "No 4".

$result = @mysql_query("SELECT gallery_id FROM gallery WHERE gallery_id >'$gallery_id' LIMIT 1");

If I select "No 1" then select "Next" it goes to "No 2", then "No 4", then "No 3", then "No 5". So it correctly loops though numerically based on id-key.

HELP ME PLEASE!!??

[ednark]

Ok here is my test table, and your queries. Only thing I did was remove the LIMIT so I could see what was being pulled each time.

All of these queries seem to correctly scroll through the list of values.

<?php

/*
CREATE TABLE `gallery` (
  `gallery_id` int(11) NOT NULL auto_increment,
  `pic_name` varchar(255) default NULL,
  `image_thumb` varchar(255) default NULL,
  `image_large` varchar(255) default NULL,
  PRIMARY KEY  (`gallery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `gallery` (`gallery_id`, `pic_name`, `image_thumb`, `image_large`) VALUES (1, 'b', 'test.jpg', 'test.jpg');
INSERT INTO `gallery` (`gallery_id`, `pic_name`, `image_thumb`, `image_large`) VALUES (2, 'a', 'test.jpg', 'test.jpg');
INSERT INTO `gallery` (`gallery_id`, `pic_name`, `image_thumb`, `image_large`) VALUES (3, 'd', 'test.jpg', 'test.jpg');
INSERT INTO `gallery` (`gallery_id`, `pic_name`, `image_thumb`, `image_large`) VALUES (4, 'c', 'test.jpg', 'test.jpg');
INSERT INTO `gallery` (`gallery_id`, `pic_name`, `image_thumb`, `image_large`) VALUES (5, 'e', 'test.jpg', 'test.jpg');
*/

/*--------- DATABASE CONNECTION INFO---------*/ 
$hostname       = ""; 
$mysql_login    = ""; 
$mysql_password = ""; 
$database       = ""; 

// connect to the database server 
$db = mysql_pconnect($hostname, $mysql_login , $mysql_password);

// give defaults
$default = array( 'gallery_id'=>2, 'pic_name'=>'a' );

// get anything from last page
$gallery_id = !empty($_GET['gallery_id']) ? $_GET['gallery_id'] : $default['gallery_id']; 
$pic_name   = !empty($_GET['pic_name'])   ? $_GET['pic_name']   : $default['pic_name']; 

// list any queries you want to test here in $queries

/*
If I select "No 1" then select "Next" it goes correctly to "No 2", the second time I select "Next" it goes back to "No 1". Thereafter it stays on "No 1". 
If I select "No 4" then select "Next" it goes correctly to "No 5", the second time I select "Next" it goes back to "No 1". Thereafter it stays on "No 1".
*/
$queries[] = "
SELECT
    gallery_id, pic_name
FROM
    gallery
WHERE
    pic_name>'$pic_name'
ORDER BY
    pic_name
";

/*
If I select "No 1,2,3 or 5" then select "Next" it goes to "No 4". Thereafter it stays on "No 4". 
If I select "No 4" then select "Next" it goes correctly to "No 5", the second time I select "Next" it goes back to "No 4". Thereafter it stays on "No 4".
*/
$queries[] = "
SELECT 
    gallery_id, pic_name
FROM 
    gallery 
WHERE 
    pic_name>'$pic_name'
";

/*
If I select "No 1" then select "Next" it goes to "No 2", then "No 4", then "No 3", then "No 5". So it correctly loops though numerically based on id-key.
*/
$queries[] = "
SELECT
    gallery_id, pic_name
FROM
    gallery
WHERE
    gallery_id>'$gallery_id'
";

function testSql($sql)
{
    static $run = 0;
    $run++;

global $default;
global $gallery_id;
global $pic_name;

print "<h2><a name=\"test$run\">SQL QUERY TEST $run</a></h2>";

$result = mysql_query($sql);
$rows = array();

$sql_result = "<pre>SQL RESULT CONTAINED:\n";
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
    $sql_result .= "( gallery_id : {$row['gallery_id']}, pic_name : {$row['pic_name']} )\n";
}
$sql_result .= "</pre>";

$next = ( count($rows)>=1 ) ? $rows[0] : $default;

echo "<a href=\"./?gallery_id={$next['gallery_id']}&pic_name={$next['pic_name']}#test$run\">Next</a>"; 
echo " - ?gallery_id={$next['gallery_id']}&pic_name={$next['pic_name']}"; 
echo "<br />";

echo "<pre>";
echo "Current ( gallery_id : $gallery_id, pic_name : $pic_name )\n";
echo "Next    ( gallery_id : {$next['gallery_id']}, pic_name : {$next['pic_name']} )";
echo "</pre>";


print "<pre style=\"color: #888888;\">Query Run:\n";
print_r($sql);
print "</pre>";


echo $sql_result;
echo "<br />";

}

/// run any tests set up
foreach( $queries as $query )
{
    testSql($query);
}
?>

[klair]

Hi ednark,

Thank you soooooooooo much, you are a genius, I really appreciate the time you spent on this, your test script worked beautifully.

So what was the problem?...

echo "<a href=\"./?gallery_id={$next['gallery_id']}&pic_name={$next['pic_name']}#test$run\">Next</a>";

After using your test script in conjunction with my existing code, I realized that because I was only echo-ing the gallery_id, and hadn't appended the pic_name, it was re-assigning the first alphabetical letter to the next_id key, explaining why it kept going back to the first alphabetical record.

You have no idea how much I appreciate this, it was giving me many sleepless nights. (always the simple things!).

[KingWylim]

Please Mark this thread Resolved.