Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
it worked! thanks a lot...i stripped the code meanwhile with ? instead of variable names (:variable) which is just the same –  Adrian Tanase Jun 21 '12 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(); –  Adrian Tanase Jun 21 '12 at 20:16
    
i wonder tho, what is the A at the end of the statement meaning –  Adrian Tanase Jun 21 '12 at 20:17
1  
The A is a subquery alias. It is required to SELECT from a subquery in MySQL. –  RolandoMySQLDBA Jun 21 '12 at 20:19
    
thanks, now i understand it better –  Adrian Tanase Jun 22 '12 at 17:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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