Return X values where an ID number doesnt matter

Posted on 16th Feb 2014 07:03 pm by admin

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:
Line number On/Off | Expand/Contract$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:
Line number On/Off | Expand/Contracttable - `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 Im just looking for a fix so that when items are deleted it will still show everything properly.

Line number On/Off | Expand/Contract<?phpinclude_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);?>

Your Answer:

Login to answer

Other forums

modifying query string with no page refresh
Hi everybody,
I've tried to get info about my problem all over the internet but i didn't find out

How to calculate days from variable date?
This will be easy for one of you gurus. I want to fetch the date from a variable date, for example:<

Extract text from string
Hi folks,

I have a string that looks like this:

aaaaaaaaaa:
bbbbbbbbbb (ccccccccc)

Error in Configure System Landscape Directory phase
Hi

We are getting the error in Configure SLD phase ( 41 of 44 phase).

Please f

Warning message
I've put a website that I was doing live and I'm getting this warning message when I try to add a ne

$_POST
Hi, I have 2 seperate php files, and i want my $_POSt["fname"] To go into both of them, Fo

Menu restriction
How can I restrict the individual menu that would appear when a user logs in so that all users are c

Save cookie to DB
I'm working on a pixel tracking script for an incentive website and I need to use an ASP script to s

Extracting Long text from message class with parameters
Hi,

I would like to extract the long text from a message class. However, the long text ha

Query output more than expected
$query=mysql_query("SELECT * FROM players, communities, divisions WHERE dob BETWEEN '{$start_da