Two or 3 ids are coming in $pks as follows and I am sending these id values in sql. For example, when this for runs 3 times, total_ticket and from_user_id values will come in 3 different arrays. How can I group these 3 arrays according to from_user_id value? So I want to make it into a single array
so I want to group the 2 or 3 $props returned by the value from_user_id
$pks = $request->input('pkids');
for ($i = 0; $i < count($pks); $i ) {
$pk = PK::findOrFail($pks[$i]);
$table_ym = date('Ym', $pk->create_time);
$table = 'video_' . $table_ym;
$props = DB::select('select sum(total_ticket) as total_ticket, from_user_id from '
. $table . ' where pk_id=' . $pk->prop_pk_id . ' group by from_user_id');
}
CodePudding user response:
Essentially this is all you have to do:
$pks = $request->input('pkids');
$pks = array_filter(array_map('intval', $pks)); //int's above 0 only
$results = DB::select('
SELECT
SUM(total_ticket) AS sum_total_ticket,
MIN(create_time) AS min_create_time,
from_user_id
FROM
'.$table.'
WHERE pk_id IN ('.implode(',', $pks).')
GROUP BY from_user_id');
This will return 1 row per from_user_id
with the total_ticket
as that "groups" sum. I also included the min create time which will be the lowest value of that group for that column. Or the first (earliest) created date.
All you have to do is retrive the results which should look like this
sum_total_ticket min_create_time from_user_id
53 2022-08-20 00:00:00 1
224 2021-08-20 00:00:00 18
Make sense.