Jump to content


Photo

annoying LIMIT problem


  • Please log in to reply
6 replies to this topic

#1 volatileboy

volatileboy
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 21 June 2006 - 01:25 PM

Okay I have been playing with this for ages now and it doesnt appear to work correctly, I have it to show 40 entries per page and it works correct on page 1 showing results 0-39, however on page 2 it shows results 40-84 and I have no idea why its doing it, here is the code I am using... please bare in mind that what im talking about above is not using the $cat variable.

here is the code:

<?
        if(isset($_GET['cat'])) {
            $cat = $_GET['cat'];
            $sql = "SELECT * FROM poems WHERE type='$cat'";
                } else {
                    $sql = "SELECT * FROM poems";
                }

                $res = mysql_query($sql);
                $numperpage = 40;
                $num = mysql_num_rows($res);
                $numpages1 = ($num / $numperpage);
                $numpages = ceil($numpages1);

                if(isset($_GET['page'])) {
                    $page = $_GET['page'];
                } else {
                    $page = 1;
                }

                $min = ($page * $numperpage - $numperpage);
                $max = ($page * $numperpage - 1);

                if(isset($_GET['cat'])) {
                    $q = "SELECT * FROM poems WHERE type='$cat' LIMIT $min,$max";
                } else {
                    $q = "SELECT * FROM poems LIMIT $min,$max";
                }

                $result = mysql_query($q);

                if($page == 1) {
                    $count = 1;
                } else {
                    $count = $min;
                }

                while($row = mysql_fetch_array($result)) {
                    $id = $row["id"];
                    $title = $row["title"];
                    echo "<tr><td><div align=\"center\">$count</div></td><td>&nbsp;<a href=\"viewpoem.php?id=$id\">$title</a></td></tr>";
                    $count++;
                }

                echo '</table>';

                //pagination

                $r = 1;
                echo "<br><br><b>Jump To Page:<br>|&nbsp;&nbsp;";
                while($r <= $numpages) {
                    if(isset($_GET['cat'])) {
                        if($r == $page) {
                            echo "<span class=\"paginationred\">[$r]</span>&nbsp&nbsp;";
                        }
                        if($r != $page) {
                            echo "<a href=\"$PHPSELF?cat=$cat&page=$r\" class=\"pagination\">$r</a>&nbsp;&nbsp;";
                        }
                    } else {
                        if($r == $page) {
                            echo "<span class=\"paginationred\">[$r]</span>&nbsp;&nbsp;";
                        }
                        if($r != $page) {
                            echo "<a href=\"$PHPSELF?page=$r\" class=\"pagination\">$r</a>&nbsp;&nbsp;";
                        }
                    } // end if
                    $r++;
                } // end loop
                echo '|';
                ?>

Any help is appreciated, thanks

#2 teomanersan

teomanersan
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 June 2006 - 02:02 PM

try this.. i didnt check it but if it`s not working u should check ur counters etc..

$min = ($page -1 ) * $numberpage;
$max = $numberpage;

hope this helps..
good coding


#3 volatileboy

volatileboy
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 21 June 2006 - 02:07 PM

It has to be done mathmatically otherwise the pagination wont work, I echoed the $min and $max values and they show correctly however the results are not coming out like that.

$min = ($page * $numperpage - $numperpage); << this is correct cos page 1 would have a value of 0 and page 2 would have a value of 40

$max = ($page * $numperpage - 1); << this is correct cos page 1 would have a value of 39 and page 2 would have a value of 79

Thats why I am so baffled with this because I dont understand why its doing it, ive used pagination so many times before and not had this problem.

#4 teomanersan

teomanersan
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 June 2006 - 02:43 PM

dude.. your $min and $max values are not mathematically correct..

let`s check the results for page = 1;

min = 0;
max = -1;

so how is it possible to get a guery like select from bla bla where bla bla limit 0,-1
-> which means get me first -1 results starting with 1st

this is absurd..

set $max = 40 and it should solve the problem unless u want a dynamic max which changes for the selected $page -> for e.g. odd numbers show 10 results , evens show 20 results etc..

good coding..

#5 volatileboy

volatileboy
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 21 June 2006 - 02:54 PM

the values are correct:

PAGE 1
$min = ($page * $numperpage - $numperpage); which is (1 * 40 - 40) = 0
$max = ($page * $numperpage - 1); which is (1 * 40 - 1) = 39

I dont see how that can be absurd, and yes its pagination so its dynamically generated min and max values, however the above is not absurd, ive echoed the values and they come out exactly as above so I am clueless how you came to "-1"

#6 teomanersan

teomanersan
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 June 2006 - 03:06 PM

$page = 1;
$numberpage = 40; // this was my mistake i wrote number
$min = ($page * $numperpage - $numperpage);
$max = ($page * $numperpage - 1);

echo($min);
echo('<br>');
echo($max);

so it gave 0 , -1


anyway thats not the deal.. my point is using your sql statement...

you want 40 results for everypage, so $max should be 40, it shoud be fixed because in sql query, like

select bla bla from bla bla where bla bla LIMIT 20,40 -> this means starting from 21st , get the next 40 results

lets check for page-> 2

$min = 40;
$max = 79;

then it will bring starting from 41st, next 79 records..

this is what i wanted to mention..

good coding..

#7 volatileboy

volatileboy
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 21 June 2006 - 03:12 PM

thank you mucho dude, thats exactly what I needed to hear.

Thanks for clarifying that for me, should work now :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users