Sol Sol - 7 months ago 19
SQL Question

Have PHP count rows with a query that is already using count(*)?

I have a subquery and I want to count the rows in PHP for MySQL. I am trying to fix old code and know that PDO is better and more secure and we will eventually rewrite all this code, but for now I need to just make it work. My problem is figuring out the command for the $total_employees to count the rows. This number will be used in a formula later. Is there a way to do it as 2 subqueries or rewriting it in the SQL statement other than just using php and mysql_fetch_row? I am trying to avoid multiple while loops. This is condensed from a bigger query for easier viewing.

while($rows=mysql_fetch_array($sqls)){
$cycle_id = $rows[cycle_id];

$sqls=("select subb.sqlcal AS sqlcalemp from
(select count(*) as sqlcal from dialogue_employees d_e,
dialogue_leaders d_l where
d_l.leader_group_id = d_e.leader_group_id and
d_l.cycle_id = $cycle_id) as subb");

$total_employees += $rows[sqlcalsemp];


This was the older code that worked before trying to update it:

while($rows=mysql_fetch_array($sqls)){
$cycle_id = $rows[cycle_id];
$sqlcalcemp=mysql_query("select count(*) from dialogue_employees d_e,
dialogue_leaders d_l where
d_l.leader_group_id = d_e.leader_group_id and
d_l.cycle_id = $cycle_id") or die(mysql_error());

$rowtotal = mysql_fetch_row($sqlcalcemp);
$total_employees += $rowtotal[0];

Answer

your looping through and looking at each cycle_id... maybe try something like this to grab all counts for each cycle_id at once

select SUM(d_e.leader_group_id IS NOT NULL) as sqlcalemp,d_l.cycle_id
from dialogue_leaders d_l
left join dialogue_employees d_e
on d_l.leader_group_id = d_e.leader_group_id
group by d_l.cycle_id

http://sqlfiddle.com/#!9/4995b/4

Comments