evlj Posted February 29, 2012 Share Posted February 29, 2012 Hello there, i'm not sure if this goes to mysql, or this section, but since it's PHP based, i would say here. So, here's the thing, i have full set up table that reads from mysql database and display it as table. Everything is working just fine, even "order" buttons that i made, now i would like to have below the table some counts, for example: My table name "canonkickoff" has column named "Prevoz" which contain only Yes and No answers, i would like to see below the table how much "Yes" answers are inside the column. Also i have other column called "VelicinaMajice", which contain 6 different answers ( XS, S, M, L, XL, XXL ), i would like to see (again, below the table) how much of every has been answered. Like: There are: XS = 6 S = 1 M = 2 L = 3 And 32 answered with Yes. Could anyone help me out? Here's the full php table: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>CanonKickOff 2012 Tabela.</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" href="table.css" type="text/css"> </head> <body style="margin: 0 0 0 0;"> <? include("passwd.php"); @$start = $_GET["start"]; if($start =='') $start =0; include("lib.php"); $link = mysql_connect($host,$username,$password); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db($db, $link); if (!$db_selected) { die ("Can't use $db : " . mysql_error()); } //total number of records in the table $orderBy = array('Kompanija', 'id', 'ImePrezime', 'Email', 'Prevoz', 'VelicinaMajice', 'SlazemSe'); $order = 'id'; if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) { $order = $_GET['orderBy']; } $res = mysql_query("SELECT * from `$table` ORDER BY '.$order"); $res2 = mysql_query("SELECT * from `$table` "); @$rows = mysql_num_rows ($res2); $result = mysql_query("SELECT * from `$table` ORDER BY $order limit $start,40"); if (!$result) { die('Invalid query: ' . mysql_error()); } echo 'Sortiraj Po: <br>'; echo '<a href="?orderBy=id">ID:</a> '; echo '<a href="?orderBy=ImePrezime">Ime i Prezime:</a> '; echo '<a href="?orderBy=Kompanija">Kompanija:</a> '; echo '<a href="?orderBy=email">Email adresi:</a> '; echo '<a href="?orderBy=Prevoz">Prevoz:</a> '; echo '<a href="?orderBy=VelicinaMajice">Velicina majice:</a> '; echo '<a href="?orderBy=SlazemSe">Slazu se:</a> '; echo "<p align=center class = 'menu'> Ocitana tabela: $table </p>"; $cols = mysql_num_fields($result); $records = mysql_num_rows ($result); echo "<table align='center' width='1200' >"; echo "<tr bgcolor='BBCCDD' class='menu'>"; for ($i = 0; $i < $cols;$i++) { echo "<td align='center'>".mysql_field_name($result,$i)."</td>"; } echo "</tr>"; while ($row = mysql_fetch_array($result, MYSQL_NUM)) { echo "<tr bgcolor='F6F6F6' class='normal'>"; foreach ($row as $value) { echo "<td align='center'>".$value ."</td>"; } echo "</tr>"; } $end = $start + $records; echo "<tr align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols> $start do $end od ukupno: $rows </td></tr>"; echo "<tr align = 'center' class='mylink'><td colspan=$cols> "; if($start != 0) { $prev = $start - 40; echo "<a href='tabela.php?start=$prev'> Prethodna </a> "; } if($start<$rows-10) { $next = $start + 40; echo "<a href = 'tabela.php?start=$next'>Sledeca</a> "; } echo "</td></tr>"; echo "</table>"; ?> </body> </html> Link to comment https://forums.phpfreaks.com/topic/257974-php-table-count-same-values-from-mysql-query/ Share on other sites More sharing options...
AyKay47 Posted February 29, 2012 Share Posted February 29, 2012 This can (and probably should) be done in the query itself. Since all I see are SELECT * from a random php variable, I cannot be precise with the query, so I will give you some pseudo code that you can tweak for yourself. Also, it's a bad habit to get into using SELECT *, you should be selecting only the fields that you are going to be using. Using SELECT * is not optimized unless you actually want to select all of the fields for use. Again I don't know you table set up etc. so this code will not be precise: SELECT COUNT(XS) as XS_count, COUNT(S) as S_count, COUNT(M) as m_count, COUNT(L) as l_count FROM canonkickoff WHERE Prevoz = 'Yes' Depending on how you want to go about this, you may want to incorporate this into an existing query using either a join or subquery. Also, I am expecting that the Prevoz field contains the literal string "yes" and "no", if it is a type BOOL, the query will change to 0 and 1. Link to comment https://forums.phpfreaks.com/topic/257974-php-table-count-same-values-from-mysql-query/#findComment-1322263 Share on other sites More sharing options...
evlj Posted February 29, 2012 Author Share Posted February 29, 2012 1st of all, thanks for response kind Sir. I actually use all the fields from table, that's why i need "*". Thanks for the code, but where should i do that query, should i use another variable and whole new query inside of php, as i don't want my whole table to get screwed, or i need to make a new one and than echo it, if so, could someone please make an example using variables i provided. Thanks in advance, i'm not a pro with mysql, but i would like to learn Link to comment https://forums.phpfreaks.com/topic/257974-php-table-count-same-values-from-mysql-query/#findComment-1322268 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.