Count all the rows that match all the values in IN clause

Question

The values of my $topics are 1,2,3 i want to make a select query that is similar to SELECT COUNT(*) FROM subtopics WHERE main_id = 1 AND main_id = 2 and main_id = 3..

    function countTopics($mid,$forum){
    $topics = implode(', ', array_column($mid, 'main_id'));
    parse_str("id=1");
    $query = "SELECT COUNT(*)
                FROM subtopics
               WHERE main_id   
                  IN ($topics)
                  GROUP BY main_id
                  HAVING COUNT(DISTINCT $topics) = 3";
    $stmt = $forum->prepare($query);
    $stmt->execute(array(
        'mid' => $topics
    ));
    $rows = $stmt->fetchAll();
    $count = implode(', ', array_column($rows, 'COUNT(*)'));
    $_SESSION['topics_count'] = $count;
}

You have error ? .. show the message. wrong result? .. show a proper data sample and the expected result.. what's your problem? — ScaisEdge, Feb 05 '18 at 15:32
You may want to select the main_id as well so that you know which count is which. — aynber, Feb 05 '18 at 15:38
Using `IN (1,2,3)` will give you the equivalent of `OR`, not `AND`. — jeroen, Feb 05 '18 at 15:40
im trying to get the number of topics base on the main id for example main id 1 contains 9 topics,main id 2 topics contains 3 and so on..Im having difficulty in using the where clause because the value of the main id is from another page so i store it in array and implode it.. — peejey, Feb 05 '18 at 15:56
Remove the `HAVING` clause. `SELECT main_id, COUNT(*) FROM subtopics WHERE main_id IN ($topics) GROUP BY main_id` — waterloomatt, Feb 05 '18 at 16:03
https://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause...i found this a bit similar about my problem please help me in tweaking my code and add select count — peejey, Feb 05 '18 at 16:12

Nigel Ren · Answer 1 · 2018-02-06T15:22:55.927

0

There are various problems with the code, your SQL doesn't need a HAVING clause, your trying to bind to a variable which isn't in the SQL statement...

function countTopics($mid,$forum){
    $topics = implode(', ', array_column($mid, 'main_id')); 
    $query = "SELECT main_id, COUNT(*) as countID
            FROM subtopics
            WHERE main_id IN ($topics)
            GROUP BY main_id";
    if ( $stmt = $forum->prepare($query) ) {
        if ( $stmt->execute() ) {
            $rows = $stmt->fetchAll();
            $count = implode(', ', array_column($rows, 'countID'));
            $_SESSION['topics_count'] = $count;
        }
     }
}

Also you should check if the previous stage has worked - so 'if the statement prepare has worked' etc. Not sure what you would do if any of these fail, but that is down to your application design.

Not surewhat $forum is as it doesn't seem to be used and it would be more normal to return the value of the count and let the calling code decide what to do with the value.

You probably want to do something like...

    $rows = $stmt->fetchAll();
    $_SESSION['topics_count'] = $rows;

and then later...

foreach ( $_SESSION['topics_count'] as $topic ) {
   echo $topic['main_id'].'-'.$topic['countID'].PHP_EOL;
}

Update:

function countTopics($mid,$forum){
    $topics = implode(', ', array_column($mid, 'main_id')); 
    $query = "SELECT main_id, subtopic_id, COUNT(*) as countID
            FROM subtopics
            WHERE main_id IN ($topics)
            GROUP BY main_id, subtopic_id";
    if ( $stmt = $forum->prepare($query) ) {
        if ( $stmt->execute() ) {
            $rows = $stmt->fetchAll();
            $_SESSION['topics_count'] = $rows;
        }
     }
}

I've added a column called 'subtopic_id', this may need to be changed depending on your database column name. This will give rows as something like...

main_id   subtopic_id countID
1         1            12
1         2            6
1         3            10
2         6            1
2         11           3

This means you will have multiple rows for each main_id, but they will have each sub topic and a count for that subtopic. If you want to output the data - you will have to use a loop, like the foreach above.

edited Feb 06 '18 at 15:22

answered Feb 05 '18 at 16:32

Nigel Ren

56,122
11
43
55

this is giving me all the different row counts but the problem is it is displaying all the different values how can i echo it separately topics topic counts main topic1 9,5,2 main topic2 9,5,2 maintopic 3 9,5,2 – peejey Feb 06 '18 at 09:19
expected output main topic1 9 main topic2 5 maintopic 3 2 – peejey Feb 06 '18 at 09:21
I'm not sure where you get the labels from the output from, but the issue is probably using the `implode` to set `$count`. If you store the `$rows` value in `$_SESSION['topics_count']` then you can loop over the result set to output the data your after. I've added some bits to the answer – Nigel Ren Feb 06 '18 at 09:59
This is what i been doing i used select query and fetch the main_id from another page and store it a session and implode it because it is giving me a array to string conversion error.And now im going to count all the rows in subtopics that is match with the main_id value and fetch the number of topics,by the way $forum is a variable for my connection.Can u please help me..sorry im new to php. – peejey Feb 06 '18 at 10:24
And I'm looping it in another page thats why i store it again in session – peejey Feb 06 '18 at 10:26
is there another way to do it?please help me – peejey Feb 06 '18 at 11:35
As your fetching data for a set of main_id's, then there is an array of data - 1 element per main_id value. If you want the data for each sub-topic, then your query should probably be different - I will update the answer with some idea. – Nigel Ren Feb 06 '18 at 15:17

Count all the rows that match all the values in IN clause

1 Answers1