Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
263 views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
<?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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...