php - how to create a view from multiple select statements in same mysql table? -

php - how to create a view from multiple select statements in same mysql table? -

please bear me if annoying all.

i have 6 queries in page.all queries run in same mysql table different clauses.

currently execute queries 1 one.but need combine sql queries single view or single query display records according material_id.

queries follows,

$a_recieved="select material_id,sum(recieved_quantity-damage_quantity-returned_quantity) re_total_quantity,sum(each_price) re_total_price,sum(purchase_quantity) re_total_purchase purchase_table material_id='".$info['material_id']."' , added_time<now() , item_status='recieved'"; $a_purchased="select material_id,sum(purchase_quantity) total_p purchase_table material_id='".$info['material_id']."' , added_time<now() , item_status='purchase'")); $a_production="select material_id,sum(recieved_quantity) pr_total_quantity,sum(each_price) pr_total_price purchase_table material_id='".$info['material_id']."' , added_time<now() , item_status='production'"; $a_fg="select material_id,sum(recieved_quantity) fg_total_quantity,sum(each_price) fg_total_price purchase_table material_id='".$info['material_id']."' , added_time<now() , item_status='fgoods'"; $a_returned="select material_id,sum(recieved_quantity) rt_total_quantity,sum(each_price) rt_total_price purchase_table material_id='".$info['material_id']."' , added_time<now() , item_status='return'"; $a_transfered="select material_id,sum(recieved_quantity) tr_total_quantity,sum(each_price) tr_total_price purchase_table material_id='".$info['material_id']."' , added_time<now() , item_status='transfer'";

i sorry may not efficient way though.

is there possibility these in single query? or have utilize mysql views this? if please advise me on this. thanks

if 6 queries homecoming same number of columns, , same kind of info (ie. id, quantity, total price) case utilize 1 single query union.

select 'received' operation, id, quantity, cost table1 union select 'purchased', id, quantity, cost table2 union ...

php mysql