PDO Get Previous and Next Record partially working
-
I'm trying to fetch the previous and next record using PDO/MYSQL. The SQL code for Next fetches the next record, but for Previous always returns the first record. Don't understand why Previous does not work. There are 7 records in this test set and the 'article_id' field is not contiguous (due to deletions) but is broken up as such: 24, 45,46,47, 48, 50, 51. Code below:
$id = 47; echo 'current id is:'.$id; //GET PREVIOUS ID $stmt= $db->prepare("SELECT \* FROM blog WHERE article\_id<$id LIMIT 0,1"); $stmt->execute(); $row=$stmt->fetch(PDO::FETCH\_ASSOC); echo '';
echo 'Previous ID is:'.$row['article_id'];
//GET NEXT ID
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id>$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '
';
echo 'Next ID is:'.$row['article_id'];The resultant output is:
current id is:47 Previous ID is:24 Next ID is:48The previous ID should be 46, not 24! I've tried the same SQL in phpMyAdmin directly and it still returns 24 for the previous, but works fine for the next. I've seen many other examples on the web showing the similar code as I have, but theirs seems to work (via youtube), mine doesn't. Any help would be appreciated! Thanks in advance.
-
I'm trying to fetch the previous and next record using PDO/MYSQL. The SQL code for Next fetches the next record, but for Previous always returns the first record. Don't understand why Previous does not work. There are 7 records in this test set and the 'article_id' field is not contiguous (due to deletions) but is broken up as such: 24, 45,46,47, 48, 50, 51. Code below:
$id = 47; echo 'current id is:'.$id; //GET PREVIOUS ID $stmt= $db->prepare("SELECT \* FROM blog WHERE article\_id<$id LIMIT 0,1"); $stmt->execute(); $row=$stmt->fetch(PDO::FETCH\_ASSOC); echo '';
echo 'Previous ID is:'.$row['article_id'];
//GET NEXT ID
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id>$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '
';
echo 'Next ID is:'.$row['article_id'];The resultant output is:
current id is:47 Previous ID is:24 Next ID is:48The previous ID should be 46, not 24! I've tried the same SQL in phpMyAdmin directly and it still returns 24 for the previous, but works fine for the next. I've seen many other examples on the web showing the similar code as I have, but theirs seems to work (via youtube), mine doesn't. Any help would be appreciated! Thanks in advance.
I am not a SQL expert, but the following occurs to me. Your SELECT statement says: find all records whose article_id is less than the value given (i.e. 47), and return the first record found. So SQL starts searching the table at the beginning, checks the first record and its id is 24, and that matches your search criteria. In order to do what you want you need somehow to tell it to find the record with the highest id that is less than 47. But I am not sure how you would specify that.
-
I'm trying to fetch the previous and next record using PDO/MYSQL. The SQL code for Next fetches the next record, but for Previous always returns the first record. Don't understand why Previous does not work. There are 7 records in this test set and the 'article_id' field is not contiguous (due to deletions) but is broken up as such: 24, 45,46,47, 48, 50, 51. Code below:
$id = 47; echo 'current id is:'.$id; //GET PREVIOUS ID $stmt= $db->prepare("SELECT \* FROM blog WHERE article\_id<$id LIMIT 0,1"); $stmt->execute(); $row=$stmt->fetch(PDO::FETCH\_ASSOC); echo '';
echo 'Previous ID is:'.$row['article_id'];
//GET NEXT ID
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id>$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '
';
echo 'Next ID is:'.$row['article_id'];The resultant output is:
current id is:47 Previous ID is:24 Next ID is:48The previous ID should be 46, not 24! I've tried the same SQL in phpMyAdmin directly and it still returns 24 for the previous, but works fine for the next. I've seen many other examples on the web showing the similar code as I have, but theirs seems to work (via youtube), mine doesn't. Any help would be appreciated! Thanks in advance.
To extend Richard's reply: you may want to refine your SQL query using ORDER BY clause or/and MAX / MIN functions.
-
I'm trying to fetch the previous and next record using PDO/MYSQL. The SQL code for Next fetches the next record, but for Previous always returns the first record. Don't understand why Previous does not work. There are 7 records in this test set and the 'article_id' field is not contiguous (due to deletions) but is broken up as such: 24, 45,46,47, 48, 50, 51. Code below:
$id = 47; echo 'current id is:'.$id; //GET PREVIOUS ID $stmt= $db->prepare("SELECT \* FROM blog WHERE article\_id<$id LIMIT 0,1"); $stmt->execute(); $row=$stmt->fetch(PDO::FETCH\_ASSOC); echo '';
echo 'Previous ID is:'.$row['article_id'];
//GET NEXT ID
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id>$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '
';
echo 'Next ID is:'.$row['article_id'];The resultant output is:
current id is:47 Previous ID is:24 Next ID is:48The previous ID should be 46, not 24! I've tried the same SQL in phpMyAdmin directly and it still returns 24 for the previous, but works fine for the next. I've seen many other examples on the web showing the similar code as I have, but theirs seems to work (via youtube), mine doesn't. Any help would be appreciated! Thanks in advance.
Solved the issue, and thanks for the hints!
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id<$id ORDER by article_id DESC LIMIT 0,1");
It seems like the for the next record, the system naturally assumes ascending, but for the previous, I had to put in the DESC order in!
-
Solved the issue, and thanks for the hints!
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id<$id ORDER by article_id DESC LIMIT 0,1");
It seems like the for the next record, the system naturally assumes ascending, but for the previous, I had to put in the DESC order in!