count rows in table with many different parameters

Question

I know that I can count how many rows have a certain string in the columns of my table like this...

  $timeOfClass="W-7PM-A";
  $inclass101 = $db->prepare("SELECT count(*) FROM students WHERE timeOfClass =?");
  $inclass101->execute(array($timeOfClass));
  $inclass101rows = $inclass101->fetchColumn(0);

$inClass101rows reflects the number of rows in my database with $timeOfClass as W-7PM-A.

But how can I do this for multiple variables of the $timeOfClass string without writing multiple SQL statements? I have a lot of them. Would this be something like make an array of SQL statements and then run them through a while loop of fetchColumn(0) ?

Example: WHERE timeOfClass IN ('W-7PM-A', 'W-7PM-B', 'W-7PM-C'); (using IN) then the array. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 10, 2014 at 23:02
If what you're asking is "how can I have a variable length array and pass it into a prepared statement" the answer is you can't without jumping through some hoops. but look around for "array" and "prepared statement" and pdo and you should see some approaches — Anthony
– Anthony, Commented Feb 10, 2014 at 23:11

Fleshgrinder · Accepted Answer · 2014-02-10 23:02:09Z

1

<?php

// $timesOfClasses is an array in the form:
$timesOfClass = array(
    "W-7PM-A",
    "X-8AM-Y",
    "...",
);


// Generate the IN clause safely for usage with prepared statements
$params = substr(str_repeat("?,", count($timesOfClass)), 0, -1);

$inclass101 = $db->prepare("SELECT COUNT(*) FROM `students` WHERE `timeOfClass` IN({$params})");
$inclass101->execute($timesOfClass);
$inclass101rows = $inclass101->fetchColumn(0);

Use SQL to alter the result as you which, e.g. add a GROUP BY clause to get several counts for looping.

answered Feb 10, 2014 at 23:02

Fleshgrinder

16.4k5 gold badges54 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Brown Over a year ago

Looks great! How do I separate the results to find the how many instances of each string in the table though?

Fleshgrinder Over a year ago

I don't know how your database abstraction works, but you can add GROUP BY timeOfClass to your SQL query and iterate with a loop over the result, it will give you the count for each time of class.

steinmas · Accepted Answer · 2014-02-10 23:02:27Z

0

Are you trying to do this for several values of $timeOfClass?

You could put all of the $timeOfClass variables in an array (W-7PM-A,W-8PM-A,etc..) then use an sql like this:

SELECT count(*) FROM students WHERE timeOfClass IN ('W-7PM-A','W-8PM-A',...) GROUP BY timeOfClass

This would give you one SQL query that you can loop through the rows to get the individual counts.

answered Feb 10, 2014 at 23:02

steinmas

3983 silver badges9 bronze badges

Comments

Funk Forty Niner · Accepted Answer · 2014-02-10 23:03:34Z

0

where timeofclass in ("value 1, 'value 2'); is the right sql syntax. You can take your array of times and create the in clause and concatenate it onto the end of your statement. you could also create a stored proc that handles the query and takes a CSV of values as a parameter. HTH

edited Feb 10, 2014 at 23:03

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Feb 10, 2014 at 22:59

user3059517

1 Comment

Funk Forty Niner Over a year ago

You have a double quote where it should be a single & missing a single quote. ("value 1, 'value 2') - Rewrite ('value 1', 'value 2')

Collectives™ on Stack Overflow

count rows in table with many different parameters

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related