php - SQL Query which helps me to get me no of "y"s in a column -
i want show how many "y"s there in date
i wonder whether there thing :(
i want display numner of "y"s in 2013.01.01 column
<?php $link = mysql_connect("localhost", "", ""); mysql_select_db("", $link); $result = mysql_query("select * attendance date = '2013.01.01'", $link); $num_rows = mysql_num_rows($result); echo "$num_rows"; ?>
my table (attendance)
date pt17 pt18 pt19 pt20 pt21 pt22 pt23 pt24 2013.01.01 y n y n y y n 2013.01.02 n n y y y y n 2013.01.03 n y n y n y n n ......
desire output:
no. of presents on 2013.01.01 4
your base sql query is
select coalesce((pt17 = 'y'), 0) +coalesce((pt18 = 'y'), 0) +coalesce((pt19 = 'y'), 0) +coalesce((pt20 = 'y'), 0) +coalesce((pt21 = 'y'), 0) +coalesce((pt22 = 'y'), 0) +coalesce((pt23 = 'y'), 0) +coalesce((pt24 = 'y'), 0) num attendance date = '2013.01.01'
output:
| num | ------- | 4 |
here sqlfiddle demo
your php part, assuming still use mysql_
extension might like
$link = mysql_connect('localhost', 'user', 'password'); if (!$link) { die('could not connect: ' . mysql_error()); } $db_selected = mysql_select_db('dbname', $link); if (!$db_selected) { die ('can\'t use db : ' . mysql_error()); } $date = '2013.01.01'; $sql = "select coalesce((pt17 = 'y'), 0) +coalesce((pt18 = 'y'), 0) +coalesce((pt19 = 'y'), 0) +coalesce((pt20 = 'y'), 0) +coalesce((pt21 = 'y'), 0) +coalesce((pt22 = 'y'), 0) +coalesce((pt23 = 'y'), 0) +coalesce((pt24 = 'y'), 0) num attendance date = '$date'"; $result = mysql_query($sql); if($result === false) { die(mysql_error()); // todo: better error handling } if ($row = mysql_fetch_assoc($result)) { $num_of_present = $row['num']; } echo "no. of presents on 2013.01.01 $num_of_present";
Comments
Post a Comment