1

How to select id,total from table_order and create list of id, sum the total according/group by add_by? And then insert the list of id and sum of total into table_bill according to add_by. This what i've so far. 

//get unpaid orders
$sql = mysql_query("SELECT id,total,add_by FROM `order` WHERE status = '1'");
$res = mysql_num_rows($sql);

if($res>=1){
$tot = 0;
$ids = '';
while($row = mysql_fetch_array($sql)){
$ids .= $row['id'].",";
$tot += $row['total'];
$uid = $row['add_by'];
}
$ids = rtrim($ids, ",");

/*echo "<br>List of ID: ".$ids;
echo "<br>Total: ".$tot."<br>";
echo "<br>Add by: ".$uid."<br>";*/

//create bill
$sql_bill = "INSERT INTO `bill` (list,amount,user)  VALUES('$ids','$tot','$uid')";
$query = mysql_query($sql_bill);
}

For example data from table_order: (id | total | add_by)

103 | 350 | 4
104 | 450 | 6
105 | 250 | 6
106 | 400 | 4

then insert into table_bill: (list | amount | user)

103,106 | 750 | 4
104,105 | 700 | 6
user3613026
  • 191
  • 1
  • 16
  • 1
    Please read [Junction Tables](http://stackoverflow.com/a/32620163) if you think this is a great idea of yours. – Drew Jul 20 '16 at 20:24

1 Answers1

1

The query for obtain your result shuold be 

select group_concat(id) ,sum(total) ,add_by
from `order`
group by add_by

and then you could do with a single query command 

insert into table_bill (list , amount , user)
select group_concat(id) ,sum(total) ,add_by
from `order`
group by add_by
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • is it possible to combine another query? UPDATE `order` SET paid_ress = '1' WHERE id IN (group_concat(id)) – user3613026 Jul 20 '16 at 21:12
  • 1
    tipically you can perform a insert/select separated form an update query .. anyway i suggest you of post another proper question and provide the code and sample necessary .. in the new question – ScaisEdge Jul 20 '16 at 21:17