1

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

1 Answer 1

5

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 !!!

4
  • it worked! thanks a lot...i stripped the code meanwhile with ? instead of variable names (:variable) which is just the same Jun 21, 2012 at 20:16
  • $query_list_records = "SELECT COUNT(*) from (SELECT * from myl_news_comments WHERE news_id=? LIMIT ? OFFSET ?) A"; this will select the count well and displaying total with $total_records = $result_list_records->fetchColumn(); Jun 21, 2012 at 20:16
  • i wonder tho, what is the A at the end of the statement meaning Jun 21, 2012 at 20:17
  • 1
    The A is a subquery alias. It is required to SELECT from a subquery in MySQL. Jun 21, 2012 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.