pagination results vs actual row id?

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
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

pagination results vs actual row id?

Post by blacksnday »

I really wish i didnt need to post here, but
after 2 weeks of trying to solve this problem I have
not been able to come up with a solution.


My pagination code won't use the sql row id for the prev/next links
being displayed. This results in not being able to delete anything in the table, because if I do it throws off the pagination and then wont display anything past the deleted item.

If I have a row for news, with AUTO INC field for the row=id, and
i have 10 results then the pagination will display 1-10 correctly.
However if I delete id#8 then the pagination wont show anything past
the id of 7, because it cant use the actual row=id for the results.

Below is the code I am using, and any help to get the pagination results to use the row=id when retrieving/displaying news, would be
greatly appreciated.

relevant sql info....

new table with auto inc row= id
need to get the pagination code to use the row=id ior the ($bash = $row['id']; )i nstead of the
$_GET['bash']; but so far have not been able to do it in a way where it will
display the data.

I think the

Code: Select all

if(!isset($_GET[$seodisplay])) 
      { 
       $bash = 1; 
      } else { 
       $bash = $_GET[$seodisplay]; 
     }
is the problem.

The
$seodisplay
just equals 'bash'

the full code is :

Code: Select all

// Returns News Page Links 
function NewsPage($sitename, $siteurl, $seodisplay) { 
//THE below $bash is the row id being used by the comment code below 
$bash = $row['id']; 
//THE below $bash is needed in order for the pagination 
//to correclty get and display the news item 
$bash = $_GET['bash']; 
$max_results = 1; 

$from = (($bash * $max_results) - $max_results); 
$sql = mysql_query("SELECT * FROM table WHERE published= '1' LIMIT $from, $max_results"); 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM table"),0); 
$total_bashs = ceil($total_results / $max_results); 

        /* get number of comments */ 
        $comment_query = "SELECT count(*) FROM table WHERE newsid='$bash'"; 
        $comment_result = mysql_query ($comment_query); 
        $comment_row = mysql_fetch_row($comment_result); 
        $bash = $show_news['id']; 
              
        if(!isset($_GET[$seodisplay])) 
      { 
       $bash = 1; 
      } else { 
       $bash = $_GET[$seodisplay]; 
     } 
  
      if($bash > 1){ 
       $prev = ($bash - 1); 
        echo "<center><a href=\"$siteurl/display/$seodisplay/$prev\"><<-Previous</a>&nbsp;&nbsp;|"; 
   } 
      if($bash < $total_bashs){ 
       $next = ($bash + 1); 
        echo "&nbsp;&nbsp;<a href=\"$siteurl/display/$seodisplay/$next\">Next->></a><br />"; 
   } 
   if($bash > 1){ 
$send = ($bash - 0); 
$com = ($bash - 0); 
echo " 
<a href=\"$siteurl/comment/$seodisplay/$bash\">($comment_row[0]) Comments</a>&nbsp;&nbsp; 
<a href=$siteurl/submitcomment.php?$seodisplay=$bash>Make A Comment</a>&nbsp;&nbsp; 
<a href=\"$siteurl/tell/$seodisplay/$bash\">Send This Bash To A Friend</a></center><p>&nbsp;</p> 
"; 
} 
$title_extra = ''; 
     while ($row = mysql_fetch_assoc($sql)) 
  {  
   $title_extra .= $row['lova'] .'&nbsp;|&nbsp;Bashed by:&nbsp;'. $row['name'] .' '; 
    $newposts = array ( "Bash Title" => $row['lova'], "Name" => $row['name'], "Bash" => $row['news'] ); 
    foreach ( $newposts as $postfield => $postinput ) 
    { 
      $postinput = submitTags($postinput, 1, 1, $siteurl, $directory, $imgalt, $vf); 
      echo "<b>{$postfield}:</b>  {$postinput}<br />"; 
    } 
    echo "<p>&nbsp;</p>"; 
  } 
  echo"<title>$sitename | $title_extra</title>"; 
}
I really wish I could correct this problem, because I alread have features like
Published/Not Published/Delete.... ready to go, but until I can solve pagination results
I am stuck trying to fix it before i can release the other features.

I might even have to redo how I am paginating.. and if so, please forward me in the
right direction.....thanks alot in advance :)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

I've not read through your entire code admiteddly, and there has been plently of discussions on here about pagination already.

But one thing.. you have defined $bash twice, first with $row['id'] then immediately again with $_GET['bash'];

To start - is $row defined?

Second, there is no point defining with $row['id'] if you will be re-definding with $_GET['bash'] straight after.


Also, when using pagination, imo it is preferable to use a method such as:

Code: Select all

<?php

mysql_connect('bobsserver', 'bob', 'bobspassword') or die('DB Connection failure');
mysql_select_db('bobsdb') or die('DB Selection Failure');

$start_row = (isset($_GET['row']) ? intval($_GET['row']) : 0);

$limit = 30; //change to whatever you want to limit to

$result = mysql_query("SELECT * FROM table LIMIT $limit OFFSET $start_row");

//etc..

?>
Then for the links to the next or previous 'pages' of rows:

Code: Select all

<?php


//next
echo '<a href="' . $_SERVER['PHP_SELF'] . '?row=' . $start_row + $limit . '">Next</a>';

//previous
echo '<a href="' . $_SERVER['PHP_SELF'] . '?row=' . $start_row - $limit . '">Previous</a>';

?>
Obviously a small touch of tweaking will be necessary to ensure you don't set the offset to greater than the number of rows on the DB and of course a negative number would not be desirable :)

HTH :)
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Jenk wrote:I've not read through your entire code admiteddly, and there has been plently of discussions on here about pagination already.

But one thing.. you have defined $bash twice, first with $row['id'] then immediately again with $_GET['bash'];

To start - is $row defined?

Second, there is no point defining with $row['id'] if you will be re-definding with $_GET['bash'] straight after.
Yes, as my first post code comments show, the first $bash is actually used by the comment code below
to generate the id number, and the second $bash is needed for the pagination results...\

WITHOUT IT my current code does not work with displaying the page results.
believe me, i have tried over 20 variations............

I will try your suggest now and see if it solves my dilema.

As i said before, the above code fully works as expected, unless you delete a news artical,
then it throws off the pagination results because if the News Id = 8 and you deleted News Id=7,
then pagination wants to show News Id=8 as News Id=7, which of course just cant work.
Last edited by blacksnday on Sat Oct 08, 2005 10:57 pm, edited 1 time in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Sorry, I don't understand what you mean, neither in the code example or in you post with regards to $row['id'], $bash, $_GET['bash'] and the comment code.

I don't see any commented code?!

Anyway..

Use the SQL OFFSET feature as shown in my example, that should 'fix' your problem with inconsitant ID numbers.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Jenk wrote:Sorry, I don't understand what you mean, neither in the code example or in you post.

I don't see any commented code?!

Anyway..

Use the SQL OFFSET feature as shown in my example.
There is a total of 4 comments in my code, and 5 if you want to be picky and include the
line that extends one of my comments.


But just as I said in my FIRST reply, I am attempting to use you code as we speak.
thanks for the advice :)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Ok :)

It's 5am here, yay for Insomnia, so it is probably me just being bleary eyed. :)
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Jenk wrote:Ok :)

It's 5am here, yay for Insomnia, so it is probably me just being bleary eyed. :)
lol! its only 12:20am here... but after a few drinkie drinkie.... might as well be 5 am :wink:
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

ok! thanks to my godly rum.... I will need to wait till morning to attack this issue again.
Thanks for the advice given above...... hopefully it can solve the problem so that I can
finally enable my delete and/or non-publish options without it messing up the
pagination results :D
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Just one question!

I see some users have a donater graphic?
I would really like to donate if shown where to, because
this forum has so far been able to answer every question I have asked.

Hopefully this last question can be answered also! how to mysql result to
show in pagination with the ID row and not the GET '..'.

can someone link me to a donate page so I can at least
help those who helped me?
thanks :)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Do you mean is there a way to not use $_GET in pagination? (to avoid having all the extra stuff on the url is the only real benefit)

Probably, but it's not worth the hassle.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

From what I have seen so far is that the problem with
$_GET is that it wont recongize the ID of the actual sql entry,
which means that if you have News Articles
with the Ids from
1-20,
and you delete #15, then any article after #15 cannot be displayed
because pagination is trying to to use $_GET id as 15 when the actual
DB ID is 16.

Hence my whole problem.

if you have news articles with auto-inc ids 1-50,
and have ids 20-30 deleted. then how can you get
pagination to correctly display articles 1-19/31-50 if its using $_GET
as the paramenter?
Because it will only count up from 1-end results
instead of knowing something was deleted and counting from 1-19/31-50
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Hence why OFFSET is used, as it ignores the actual ID and just returns from the nth row specified.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

I am really starting to get <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span>!
I have spent many many many days trying to
get my pagination to work... that if i had the money
I would just pay someone to do it for me.

All i want it to do, is recongize an article is deleted, and skip over
it while maintaining the proper ID Number Order.

Since my first post in this thread, i have changed my code
and with every accomplishment... there proves to be another problem.. lol :roll:

However, I am closer now then ever to correcting this problem.
I changed how my pagination ties ID numbers to news articles.
I DID have it by the Auto-Inc.... but now it is changed to
a new ID field that will auto-inc itself based upon the last
last actual number for the last article.

This way, I can delete stuff and not worry about number line breakage
since it will Auto-Pick up from where it left off.
Also it allows any news article to be sure to have a permanent ID field that
will never change.. even if I change all the actual auto-inc numbers.

My new code now gets its News ID Page number based on the New Id Field
(did) for each article. If The number line is broken....
i.e.
1,2,3,5,6,
then it correctly Grabs and Displays and Links to the actual number
(thats half the problem I had before..... where the pagination wanted to create
its own id number... which was causing me many problems)

However... the one thing I cant seem to fix... is if it jumps from
News Id 3 and goes to News Id 5, then all links after
want to jump 2 places as well.
Doesnt matter what the number is... it wants to jump all links
based on the last jump.

Any ideas on how to stop it from jumping based on previous jump
would be great... and I would then finally have my months of
pagination solved!
Heres te current code to generate NEXT links:

Code: Select all

$startdisplay = $_GET['display'];
$didQuery = mysql_query ("SELECT * FROM $table WHERE published = '1' ORDER BY did LIMIT $startdisplay,1"); 
  	
	while($displayDid = mysql_fetch_array($didQuery))
   		{ 
   			$updateDid = $displayDid['did']; 
   			$next = $updateDid;

echo "<a href='$_SERVER[PHP_SELF]?display=$next'>Next ></a> ";
		}
My guess is it has to do with
$startdisplay = $_GET['display'];
but i haven't been able to solve it.
Post Reply