用许多不同的参数对表中的行进行计数 [英] count rows in table with many different parameters

问题描述

我知道我可以像这样计算表的列中具有特定字符串的行数...

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反映了我的数据库中以$timeOfClass为W-7PM-A的行数.

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

但是如何在不编写多个SQL语句的情况下对$timeOfClass字符串的多个变量执行此操作?我有很多.会像创建一个SQL语句数组然后通过fetchColumn(0)的while循环运行它们吗?

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) ?

推荐答案

<?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);

使用SQL更改结果，例如添加GROUP BY子句以获取多个循环计数.

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

这篇关于用许多不同的参数对表中的行进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！