CODEWITHSUNDEEP
phpmysqlvariableshtml-tableruntime
B. Desai
B. Desai

Reputation: 16446

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

Upvotes: 0

Views: 70

Answers (3)

JYoThI
JYoThI

Reputation: 12085

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);

Upvotes: 1

Professor Abronsius
Professor Abronsius

Reputation: 33823

I still have PHP 5.3.2 so I do not have array_column available but did find a working function to emulate this on the PHP site - so testing like this seems to yield the desired output.

Assumed that the recordset looks like this

$records=array(
    array('total_rec'=>3,'user_id'=>1,'date_rec'=>'2017-08-05'),
    array('total_rec'=>2,'user_id'=>2,'date_rec'=>'2017-08-05'),
    array('total_rec'=>1,'user_id'=>1,'date_rec'=>'2017-08-06'),
    array('total_rec'=>2,'user_id'=>4,'date_rec'=>'2017-08-06')
);

Then

echo array_sum( array_column( $records, 'total_rec' ) ); //8

Upvotes: 0

sumit
sumit

Reputation: 15464

You can do it from SQL

SELECT * FROM 
    (
    SELECT count(ID) as total_rec ,user_id,DATE(record_time) as date_rec,'user_date' as mode 
    FROM test_record GROUP BY DATE(record_time),user_id 


    UNION 


    SELECT count(ID) as total_rec ,0 as user_id,DATE(record_time) as date_rec,'date_only' as mode 
    FROM test_record GROUP BY DATE(record_time)
    )tmp
    ORDER BY date_rec ,user_id DESC

based on mode you can do something in your view

Upvotes: 1

Related Questions