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

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -