Pagination Force Correct 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 Force Correct ID

Post by blacksnday »

I've been playing with pagination and have found a big problem.
I am currently using it to display all articles in db, with comments
and blah blah......

The problem comes when and how it displays the ID number based on
how it pulls the article from the database.

For example, if I have it pull just one article starting from the first one
found in DB, it isn't a problem and the comments and id all work in unison.
Because it will display the first result shown as id=1 always and work its way up.

However, If I want to display... for example, the 5 newest entries, which in turn would be
the last ones in the DB, then pagination wants to create the display as starting from id=1
instead of the last id, and the last id would be the correct result if the last article is being displayed
first.
I'm sure this problem has been discussed many times over before, but pagination is only way
I know how to display results, and right now I am extremly limited to what I can display.
How do you force pagination to always show the proper ID Number regardless of the order
the content is displayed?

Here is the code I am currently using to show One Article linked to comments, starting from
first article found in DB.

Code: Select all

function NewsPage() {
if(!isset($_GET['bash'])){ 
$bash = 1;
} else { 
$bash = $_GET['bash']; 
} 

$max_results = 1; 
$from = (($bash * $max_results) - $max_results); 
$sql = mysql_query("SELECT * FROM news LIMIT $from, $max_results"); 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM news"),0); 
$total_bashs = ceil($total_results / $max_results); 
        /* get number of comments */
        $comment_query = "SELECT count(*) FROM comments WHERE newsid='$bash'";
        $comment_result = mysql_query ($comment_query);
        $comment_row = mysql_fetch_row($comment_result);

if($bash > 1){ 
$prev = ($bash - 1); 
echo "<center><a href=\"display.php?bash=$prev\"><<-Previous</a>     "; 
} 
if($bash < $total_bashs){ 
$next = ($bash + 1); 
echo "     <a href=\"display.php?bash=$next\">Next->></a><br />"; 
} 

if($bash > 1){ 
$send = ($bash - 0); 
$com = ($bash - 0); 
echo "<a href=\"comment.php?bash=$bash\">($comment_row[0]) Comments</a>&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"tell.php?bash=$com\">Send This Bash To A Friend</a></center><br /><br />"; 
}
  while ($row = mysql_fetch_assoc($sql)) 
  { 
    $newposts = array ( "Name" => $row['name'], "Past Loves" => $row['lova'], "Bash" => $row['news'] ); 
    foreach ( $newposts as $postfield => $postinput ) 
    { 
      $postinput = submitTags($postinput, 1, 1); 
      echo "<b>{$postfield}:</b>  {$postinput}<br />"; 
    } 
    echo "<p>&nbsp;</p>"; 
  }
}
As you can see, the way i made this is so that the comment is related directly to the displayed Bash ID, and not the actual DB ID of the bash, since pagination wont show it, plus it would look nasty if you have deleted some articles and the id numbers jump from each other.

If I want to change this to show the last 5 entries

Code: Select all

$sql = mysql_query("SELECT * FROM news ORDER BY id DESC LIMIT 5");
then the starting id of the displayed results start from id=1 which throws everything off
since the id should equal the last available id.

Any Suggestions?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I often will transmit a hint as to how the data should continue to be presented, storing only the bare minimum of what's needed to create a duplicate query against the database again. So, along with 'id' will be things like 'sort', and search terms (if it's a search)
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Ok, here is a better example that shows exactly what I am trying to do.

Code: Select all

function show_last_five() {
$sql = mysql_query("SELECT * FROM news ORDER BY id DESC LIMIT 5"); 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM news"),0); 
/* get number of comments */
$comment_query = "SELECT count(*) FROM comments WHERE newsid='$bash'";
$comment_result = mysql_query ($comment_query);
$comment_row = mysql_fetch_row($comment_result);

$com = way to generate id number for each of the 5 displayed; 

  while ($row = mysql_fetch_assoc($sql)) 
  { 
	  echo "<a href=\"comment.php?bash=$com\">($comment_row[0]) Comments</a>&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"tell.php?bash=$com\">Send This Bash To A Friend</a></center><br /><br />"; 
    $newposts = array ( "Name" => $row['name'], "Past Loves" => $row['lova'], "Bash" => $row['news'] ); 
    foreach ( $newposts as $postfield => $postinput ) 
    { 
      $postinput = submitTags($postinput, 1, 1); 
      echo "<b>{$postfield}:</b>  {$postinput}<br />"; 
    } 
    echo "<p>&nbsp;</p>"; 
  }
}
With the above, the $com needs to read the correct id for each of the 5 displayed.
I have tried numerous things to make it work correctly, but the best I have came up with so far
is using
$com = $total_results;
which shows the last id as the same for all five shown.
I know that is incorrect but has been the best I have been able to produce so far :(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$com = $row['id'];

or whatever you use as the ID.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

feyd wrote:$com = $row['id'];

or whatever you use as the ID.
that wont work because of the

Code: Select all

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM news"),0);
I need com to equal the Count as Num

The reason is because the comments table identifies which news article the comment is
tagged to based on the Count as Num.

And I need to keep the $total_results a Count as Num because
I dont want to show the actual ID, since it is auto-inc and if deleting some articles
that would eventualy create nasty gaps in the id numbers.
And is a better security method not showing the actual ID i think.

So, i need to figure out how to get $com to realize there are 5 articles, and show the Count as Num
respectivly for each article, just as the $total_results can grab and realize.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Just like to add, that when i display an article on a single basis,
$com would equal $_GET['bash']; and that generates correctly.

But that won't work if trying to display more then one at same time,
hence my problem
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I said $row, not $total_result. :P
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Code: Select all

while($something = $somethingelse)
{
   $com[] = $row['com'];
}

$total = count($com);
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Ok, this is sorta getting frustrating :P

If I use

Code: Select all

$total = $total_results;
it will return the correct last id, however it returns this id
for all articles.

If I use

Code: Select all

$com[] = $total_results;
	  $total = count($com);
or

Code: Select all

$com[] = $row['id']; 
	  $total = count($com);
It will return the ID's starting from 1 but counts up for each article.

What I need to do is have it count from Last article id i.e. 36,
and then count down
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

don't store the id's into an array, just use them. Place

Code: Select all

$com = $row['id'];
into your while loop, leaving it as it was when you last posted code. (Adjust the element name to fit your table structure of course)
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

the problem with that is it doesnt maintain the same number as the pagination does.

For example, if I have an article with the Auto-Inc Id being 567
and the pagination displays it as ID=36.
This is because the pagination uses the
SELECT COUNT(*) as Num option so it doesnt reflect to true article ID of 567.

The way I have set up my comments to attach to the correct news id, is based
on the id that the pagination gives it.

So if you go to my comment.php page, and choose id=36 to make a comment,
it is really going to id 567.

If i use your code to get the actual id of the article, when user clicks comment
and goes to comment.php with the id= 567, it wont show because the
pagination only knows it as id=36

I would like to maintain the same ID number as my pagination gives it
but when trying to show multiple news posts with comments attached to news id number
this is causing problems......
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

okay.. that's just silly, but okay. You could do

Code: Select all

$com = $total_result - $count;
where $count is iterated on each pass of the while loop...
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

thanks for that... however still causes problems.

I will just have to re-write how the pagination is done, however
I have yet to see pagination by Auto-Inc id.

When displaying one by one everything has worked great, but
now I am trying to offer something like
'Last 5 Submissions' and need the comment link and number of comments shown
with the link to the article.

Oh well, better to realize this now then later eh? :P
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post by blacksnday »

Actually I got this working just fine.
Only thing is that the article auto-inc id needs to stay in synch with
the id number that shows when viewing all at once.

Not so bad since I only need this to show some latest entries.
It is something I will need to look at more closely later though
as this is a bad permanent solution :)
Post Reply