MYSQL/PHP Pagination shows 9 pages regardless of results returned

Question

I'm using the following code for an events page mysql query to show paginated results from the current date however it will display 9 pages in the pagination links even when the results would only total 3 pages etc. Can someone help me identify why this is happening?

            $per_page = 25;
            $pages_query = mysql_query("SELECT COUNT('CompName') FROM my_db");
            $pages = ceil(mysql_result($pages_query, 0) / $per_page);
            $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
            $start = ($page - 1) * $per_page;
            $query = mysql_query("SELECT *, DATE_FORMAT(StartDate, '%d/%m/%Y') StartDate, CompName, HostState, Location,  competitiontypedesc FROM my_db WHERE StartDate >= CURDATE() LIMIT $start, $per_page"); 

            while($query_row = mysql_fetch_assoc($query)) {
            echo"
            <li class='kleo-masonry-item event-item'> 
            <div class='member-inner-list animated animate-when-almost-visible bottom-to-top start-animation grey-border'> 
            <div class='event-cell event-date-cell'>
            <p class='no-margin'>" . $query_row["StartDate"] . "</p>
            </div>
            <div class='event-cell name'>
            <p class='no-margin'>" . $query_row["CompName"] ." " .$query_row["LastName"]. "</p></div>
            <div class='event-cell'>
            <p class='no-margin'>" . $query_row["Location"] . "</p>
            </div>
            <div class='event-cell'>
            <p class='no-margin'>" . $query_row["HostState"] . "</p>
            </div>
            </li>"
            ;   
            }

            $prev = $page - 1;
            $next = $page + 1;

            if(!($page <= 1)){
            echo "<a href='?page=$prev'><</a> ";}

            if($pages >= 1){
            for($x=1; $x<=$pages; $x++){
            echo ($x == $page) ? '<b><a href="?page='.$x.'">'.$x.'</a></b> ' : '<a href="?page='.$x.'">'.$x.'</a>';}
            }

            if(!($page >= $pages)){
            echo "<a href='?page=$next'>></a> ";}
            }
            }

Please, read this question: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php . — Michas, Oct 11 '16 at 01:36
Thanks Michas, realise its depreciated and will update, code is from an older tutorial — Dexx, Oct 11 '16 at 01:43
@ParkProductions An answer has been given below, explaining the thing you're doing wrong. Hopefully *that* answer will resolve your issue. — Rajdeep Paul, Oct 11 '16 at 01:48

Tung Nguyen · Accepted Answer · 2016-10-11T01:48:58.357

1

Because when you count number of row, you always count in whole the table.

$pages_query = mysql_query("SELECT COUNT(`CompName`) FROM my_db");

But when you get data, you have a condition on StartDate in where clause.

$query = mysql_query("SELECT *, DATE_FORMAT(StartDate, '%d/%m/%Y') StartDate, CompName, HostState, Location,  competitiontypedesc FROM my_db WHERE StartDate >= CURDATE() LIMIT $start, $per_page");

So, you should add the same condtion of StartDate in count query.

$pages_query = mysql_query("SELECT COUNT(`CompName`) FROM my_db WHERE StartDate >= CURDATE()");

Actually, count(*) will be better than count(CompName).

$pages_query = mysql_query("SELECT COUNT(*) FROM my_db WHERE StartDate >= CURDATE()");

https://www.percona.com/blog/2007/04/10/count-vs-countcol/

edited Oct 11 '16 at 01:48

answered Oct 11 '16 at 01:42

Tung Nguyen

767
7
6

If you use MyISAM tables, count(*) will have a better performance. $pages_query = mysql_query("SELECT COUNT(*) FROM my_db WHERE StartDate >= CURDATE()"); https://www.percona.com/blog/2007/04/10/count-vs-countcol/ If you use Innodb, count(*) and count(`ComName`) have the same performance. – Tung Nguyen Oct 11 '16 at 01:46
Thanks Tung that worked, its now displaying the correct amount of pages. – Dexx Oct 11 '16 at 01:53

MYSQL/PHP Pagination shows 9 pages regardless of results returned

1 Answers1