Some PHP pagination help

[My220x]

Hi,

 

Im creating a PHP script which displays records from a database and displays a certain number of records on each page. My code is below:

 

       if($_GET['cat'] && is_numeric($_GET['cat'])) {
       if($_GET['convo'] && is_numeric($_GET['convo'])) {
       $query = "SELECT * FROM conversations WHERE id = '{$_GET['convo']}' AND cat = '{$_GET['cat']}' AND display = 'yes'";
       $result = mysql_query($query) or die("Error");
       if(mysql_num_rows($result) > 0) {
       while($row = mysql_fetch_array($result)) {
       $query = "SELECT name FROM cats WHERE id = '{$row['cat']}'";
       $results = mysql_query($query) or die("Error");
       $data = mysql_fetch_array($results);
       echo "{$row["title"]}
       
Category: " . htmlentities($data["name"]) . "";
       echo "
Submitted On: " . date("j \of F Y", strtotime($row["submit"])) . "";
       echo "
Author: " . htmlentities($row["author"]) . "";
       echo "
Rating: {$row["yes"]} like it and {$row["no"]} don't.";
       echo "
Conversation: " . nl2br(wordwrap(htmlentities($row["text"]), 100)) . "";
       }
       } else {
       echo "That conversation does not exsist.";
       }
       } elseif(!$_GET['convo']) {
       $query = "SELECT COUNT(*) FROM conversations WHERE cat = '{$_GET['cat']}'";
$result = mysql_query($query) or die("Query Error");
$rows = mysql_fetch_array($result);
$r = $rows[0];
$rowsperpage = 10;
$totalpages = ceil($r / $rowsperpage);
if (isset($_GET['page']) && is_numeric($_GET['page'])) {
$currentpage = (int) $_GET['page'];
} else {
$currentpage = 1;
}
if ($currentpage > $totalpages) {
$currentpage = $totalpages;
}
if ($currentpage < 1) {
$currentpage = 1;
}
$offset = ($currentpage - 1) * $rowsperpage;
      $query = "SELECT * FROM conversations WHERE cat = '{$_GET['cat']}' AND display = 'yes' LIMIT $offset, $rowsperpage";
$result = mysql_query($query) or die("Query Error");
       echo "</pre><ul>";
       if(mysql_num_rows($result) > 0) {
       while($row = mysql_fetch_array($result))
       {
       echo "{$row['title']}";
       }
       $range = 3;

if ($currentpage > 1) {
echo " << ";
$prevpage = $currentpage - 1;
echo " < ";
}
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
if (($x > 0) && ($x <= $totalpages)) {
if ($x == $currentpage) {
echo " [$x] ";
} else {
echo " $x ";
}
}
}
if ($currentpage != $totalpages) {
$nextpage = $currentpage + 1;
echo " > ";
echo " >> ";
}
       } else {
       echo "This Conversation does not exsist.";
       }
       } else {
       echo "This conversation does not exsist";
       } 
       } else {
       echo "I am sorry but this category does not exsist";
       }
       ?&gt

 

Now if you do click on the next page link it says "This conversation does not exsist" however I want it to go to the next page can someone help me fix it please.

[mrwITch]

1st off - you have 2 phrases 'This conversation does not exsist'

 

Change them to be better debug statements - or at least

'This conversation does not exsist 1' &

'This conversation does not exsist 2'

 

That'll help debug which path through your code your going through

 

If I've read your code correctly

 

 

you reach the 2nd of these debug statements as a result of the final else clause from

 

if($_GET['convo'] && is_numeric($_GET['convo'])){

....code

}elseif(!$_GET['convo']){

....code

} else {

echo "This conversation does not exsist";

}

 

 

I suspect this might be where your problem lies

 

The

if($_GET['convo'] && is_numeric($_GET['convo'])){

 

tests for a Non-Blank & numeric parameter named convo

 

whereas

}elseif(!$_GET['convo']){

 

This tests if convo is Blank / zero length string

 

and therefore the

} else {

echo "This conversation does not exsist";

 

will catch convo not blank, not zero length, but not numeric

 

I suspect that this

}elseif(!$_GET['convo']){

 

is not what you intended

[autismuk]

The main chunk, after the elseif (!_$GET['convo']) is the bit that's actually doing the work of the paging.

 

On the face of the error message is wrong, because convo presumably isn't set when you come back with page 2. It is more likely to be the capital 'c' version as a result of the mysql_fetch_array failing. Definitely enumerate the error messages so you know which one isn't failing.

 

I notice that in the main SQL fetch (the one with Limit), it is unsorted. It's a while since I've written any PHP/MySQL but it struck be that this being unordered might not help, as SQL doesn't guarantee the order of any returned dataset if you don't specify it (I think !)

 

Might also be worth outputting the main SQL fetch as a debug effort just so you can check it using the CLI MySQL utility to see if it works.

 

I presume you've removed the mysql_connects for clarity

[My220x]

Im going to do a bit more reading to fix it. Edited March 21, 2009 by My220x

[My220x]

Ok heres my version all 100% working:

 

       if(isset($_GET["page"]) && is_numeric($_GET["page"])) {
       $page = ceil($_GET["page"]);
       } else {
       $page = 1;
       }
       $perpage = 10;
       $querycount = "SELECT COUNT(*) FROM conversations WHERE display = \"no\"";
       $resultcount = mysql_query($querycount) or die(mysql_error());
       $fetch_row = mysql_fetch_row($resultcount);
       $lastpage = ceil($fetch_row["0"] / $perpage);
       if($page < 1) {
       $page = 1;
       } elseif($page > $lastpage) {
       $page = $lastpage;
       }
               $startlimit = ($page-1)*$perpage;

     $query = "SELECT * FROM conversations WHERE display = \"no\" ORDER BY \"submit\" ASC LIMIT $startlimit, $perpage ";
     $result = mysql_query($query) or die(mysql_error());
     if(mysql_num_rows($result) > 0) {
     ?>
     </pre><table id="latest" border="1">
     Title
     Conversation
     Category
     Submitted On
     Author
     Action
           while($row = mysql_fetch_array($result)) {
     echo "";
     echo "" . htmlentities($row["1"]) . "";
     echo "" . htmlentities($row["2"]) . "";
     $sql = "SELECT * FROM cats WHERE id = {$row["cat"]}"; // This is so I can show the categories name
     $catresult = mysql_query($query) or die("Error");
     $catname = mysql_fetch_array($catresult);
     echo "" . $catname["1"] . "";
     echo "" . date("jS \of F Y", strtotime($row["6"])) . "";
     echo "" . htmlentities($row["8"]) . "";
     echo "Accept / Reject";      
     echo "";
     }
     echo "
     </table><br>     ";<br>     if($page == 1) {<br>       echo "<<< ";<br>       echo "<< ";<br>       } else {<br>       echo "<a href="%5C%22?page=1%5C%22" rel=""><<<</a> ";<br>       echo "<a href="%5C%22?page=%22" . rel=""><<</a> ";<br>       }<br>       <br>       for ($i=$page; $i<=$page+2; $i++) {<br>if ($i>0 && $i<=$lastpage) {<br>	if ($page == $i) {<br>		echo "<b>$i</b> ";<br>	} else {<br>		echo "<a href="%5C%22?page=%24i%5C%22" rel="">$i</a> ";<br>	}<br>}<br>}<br>       <br>       if($page == $lastpage) {<br>       echo ">>> ";<br>       echo ">> ";<br>       } else {<br>       echo "<a href="%5C%22?page=%22." . rel="">>></a> ";<br>       echo "<a href="%5C%22?page=%22" . rel="">>>></a> ";<br>       }<br>     } else {<br>     echo "<p>No Conversations waiting approval</p>";<br>     }<br>

[mrwITch]

Ok heres my version all 100% working:

 

<?php

       if(isset($_GET["page"]) && is_numeric($_GET["page"])) {

        $page = ceil($_GET["page"]);

        } else {

        $page = 1;

        }

        $perpage = 10;

        $querycount = "SELECT COUNT(*) FROM conversations WHERE display = \"no\"";

        $resultcount = mysql_query($querycount) or die(mysql_error());

        $fetch_row = mysql_fetch_row($resultcount);

        $lastpage = ceil($fetch_row["0"] / $perpage);

        if($page < 1) {

        $page = 1;

        } elseif($page > $lastpage) {

        $page = $lastpage;

        }

                $startlimit = ($page-1)*$perpage;
 

      $query = "SELECT * FROM conversations WHERE display = \"no\" ORDER BY \"submit\" ASC LIMIT $startlimit, $perpage ";

      $result = mysql_query($query) or die(mysql_error());

      if(mysql_num_rows($result) > 0) {

      ?>

      

      

      

      

      

      

      

      <?php

      while($row = mysql_fetch_array($result)) {

      echo "
";

      echo "
";

      echo "
";
 

[color=Red][b]

// This is so I can show the categories name

     $sql = "SELECT * FROM cats WHERE id = {$row["cat"]}";

      $catresult = mysql_query($query) or die("Error");

      $catname = mysql_fetch_array($catresult);
 

????? Shouldn't this be  ???????

      $catresult = mysql_query([b][color=Red]$sql[/color][/b]) or die("Error");

      $catname = mysql_fetch_array($catresult);
 
 

[/b][/color]
 

 
 

You need to check that your inner SQL query is what you intended
Title
Conversation
Category
Submitted On
Author
Action
" . htmlentities($row["1"]) . "
" . htmlentities($row["2"]) . "