Solved: Return X values where an ID number doesnt matter

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Solved: Return X values where an ID number doesnt matter

Post by Weiry »

Well as for subject heading, thats the best i could think of.

Currently im having a problem with a rather old piece of script i had written for a guestbook.
The guestbook itself relies completely on the `id` field of the table to show the posts.
But if a post is deleted, then it screws up the pagination of the posts such that if you were to click "next page" the same results as the last page would show.

The query:

Code: Select all

$set = $numAllResults-$offset+1;
SELECT * FROM `posts` WHERE `id` <= '$set' ORDER BY `id` DESC
This query works wonders if every entry in the table is only incremented by 1, but breaks when an entry of 'X' does not exist. ie. it skips post 'X' but also shows older posts based on the newest entry. As i know this isnt clear, let me clear it up with an example:

Code: Select all

table - `posts`
`id`, `name`, `post`
1,'Joe','Hi there'
2,'Johanna','Love the guest book'
3,'Jeff','Great stuff'
4,'James','Could have more stuff'
8,'John','Excellent!'
9,'Joel','Not bad'
As you can see, there are 3 entries missing because they were deleted.
As it stands, there should be 2 pages in the guestbook because there are more than 5 entries.
However, the first page will show entries:
9,8,4,3,2.
The second page:
8,4,3,2,1

Now in this example, the problem isnt too huge, although when i have say 20 entries with half deleted, it will show the same posts for about 4 pages, and then change after the $set is lower than the older posts.

Yes i am posting my code, but, this was written over a year ago now, so i know i am breaking some of my own today's standards for programming :P Im just looking for a fix so that when items are deleted it will still show everything properly.

Code: Select all

<?php
include_once("include/connect.php");
$guestbookconnect = connect();
mysql_select_db("alexast_db",$guestbookconnect);
$offset = $_GET['p'];
if(!$_GET['p']){$offset = 0;}
if($offset < 0){print "<script>window.location = 'guestbook.php';</script>";}
$results = 5; //display number of results per page.
$currentresults = 0;
 
$getAllPostsSQL = "SELECT * FROM `posts` ORDER BY `id` DESC";
$getAllPostsQuery = mysql_query($getAllPostsSQL, $guestbookconnect) or die(mysql_error($guestbookconnect));
$numAllResults = mysql_num_rows($getAllPostsQuery);
 
$set = $numAllResults-$offset+1;
$getPostsSQL = "SELECT * FROM `posts` WHERE `id` <= '$set' ORDER BY `id` DESC";
$getPostsQuery = mysql_query($getPostsSQL, $guestbookconnect) or die(mysql_error($guestbookconnect));
$numResults = mysql_num_rows($getPostsQuery);
 
while($getPostsResult = mysql_fetch_assoc($getPostsQuery)){
$currentresults++;
    if($currentresults <= $results){
?>
        <div class="guestbook_frame">
            <div class="guestbook_box1">
                <div id="style_name"><?php print $getPostsResult['name']; ?></div>
            </div>
            <div class="guestbook_box2">
                <div id="style_date"><?php print $getPostsResult['date']; ?></div>
            </div>
            <div class="guestbook_box3">
            </div>
            <div class="guestbook_box4">
                <div id="style_content"><?php print $getPostsResult['content']; ?></div>
            </div>
        </div>
<?php
    }
}
mysql_close($guestbookconnect);
?>
cheers
~Weiry
Last edited by Weiry on Thu Oct 22, 2009 7:08 pm, edited 1 time in total.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Return X values where an ID number doesnt matter

Post by Weiry »

Solved:

I should have known better and used the power of MySQL to fix this,

updated the second query:

Code: Select all

SELECT * FROM `posts` ORDER BY `id` DESC LIMIT {$offset},{$results}
Post Reply