PDO count(*) does not work with LIMIT and OFFSET

Question

here's my code it works normally, but this time i want to also count the records it gives. at the point of inserting COUNT(*) it will give NULL value to counted rows

        $query_list_records = "SELECT COUNT(*) from myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded";
        $result_list_records = $db->prepare($query_list_records);
        $result_list_records->bindValue(':news_id', $_POST['news_id'] , PDO::PARAM_INT);
        $result_list_records->bindValue(':ajax_read_how_many_comments', (int)$ajax_read_how_many_comments , PDO::PARAM_INT);
        $result_list_records->bindValue(':items_already_loaded', (int)$_POST['items_already_loaded'] , PDO::PARAM_INT);
        $result_list_records->execute();                    
        $total_records = $result_list_records->fetchColumn();

echo $total_records;

how can i make this work ?

thanks have a great night

Answer 1

Within the query itself, trying separating the COUNT(*) from the retrieval by rewriting the query like this:

SELECT COUNT(*) from (SELECT myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded) A

Your code should look like this now:

        $query_list_records = "SELECT COUNT(*) from (SELECT myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded) A";
        $result_list_records = $db->prepare($query_list_records);
        $result_list_records->bindValue(':news_id', $_POST['news_id'] , PDO::PARAM_INT);
        $result_list_records->bindValue(':ajax_read_how_many_comments', (int)$ajax_read_how_many_comments , PDO::PARAM_INT);
        $result_list_records->bindValue(':items_already_loaded', (int)$_POST['items_already_loaded'] , PDO::PARAM_INT);
        $result_list_records->execute();                    
        $total_records = $result_list_records->fetchColumn();

echo $total_records;

Give it a Try !!!