B. Desai B. Desai - 1 year ago 64
MySQL Question

Run time dynamically bind variable in php

I need to bind value of variable at run time. Here I am describing my criteria what I needed.

I have one table : test_record

ID user_id details record_time
1 1 xyz 2017-08-05 09:00:30
2 2 abc 2017-08-05 09:05:32
3 1 pqr 2017-08-05 09:06:25
4 1 lmn 2017-08-05 09:08:56
5 2 def 2017-08-05 09:08:59
6 1 xyz 2017-08-06 09:00:30
7 2 abc 2017-08-06 09:05:32
8 2 pqr 2017-08-06 09:06:25
9 2 lmn 2017-08-06 09:08:56
10 2 def 2017-08-06 09:08:59


Now I want to get total typing group by date then user.

So I use this query :

SELECT count(ID) as total_rec ,user_id,DATE(record_time) as date_rec
FROM test_record GROUP BY DATE(record_time),user_id
ORDER BY DATE(record_time),user_id


Result I am getting is:

total_rec user_id date_rec
3 1 2017-08-05
2 2 2017-08-05
1 1 2017-08-06
2 4 2017-08-06


In php I am getting this on array and I traverse it in loop to diaplay it in tabel:

<table>
<tr>
<th>Total test records</th>
<th>User ID</th>
<th>Date</th>
</tr>
<?php
$rec_date = ""
foreach($records as $key=>$value )
{
//I have done this for grouping so that each date can highlighted with new row.
if($rec_date != $value['date_rec'])
{
$rec_date = $value['date_rec'];
echo "<tr><td colspan='3'>$rec_date</td></tr>";
}
echo "<tr>";
echo "<td>".$value['total_rec']."</td>";
echo "<td>".$value['user_id']."</td>";
echo "<td>".$value['date_rec']."</td>";
echo "</tr>";
}
?>
</table>


Every thing is working fine. Now what I want is I need sum of
total_rec
date wise i.e. total of all users also. Which I want to display with Date which I have separated in if condition.
I know I can get it in loop like

$total_dt = 0;
foreach($records as $key=>$value )
{
if($rec_date != $value['date_rec'])
{
$rec_date = $value['date_rec'];
echo "<tr><td colspan='3'>$rec_date</td></tr>"; // I want to display total here with date
}
$total_dt + = $value['total_rec'];
.
.
.
}


But I want it in if condition
if($rec_date != $value['date_rec'])
i.e. before displaying all data of particular date. I know I can get it at last. But I want to display before

Answer Source

1st : You can loop the array and make the total array datewise like this

$new_array=array();
foreach($array as $key=>$val){

    $new_array[$val['date_rec']]=isset($new_array[$val['date_rec']])? $new_array[$val['date_rec']]+$val['total_rec'] : $val['total_rec'];
}

2nd : Echo the total for specific date from the array like this .

echo "<tr><td colspan='3'>$rec_date</td><td>".$new_array[$value['date_rec']]."</td></tr>";

update 1 : You can use array_walk()

$new_array=array();
array_walk($ss,function($v) use(&$new_array){  $new_array[$v['date_rec']] = isset($new_array[$v['date_rec']])? $new_array[$v['date_rec']]+$v['total_rec'] : $v['total_rec'];  });

print_r($new_array);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download