Counting Value From The Mysql Table (Left Join)

[ariffin246]

SELECT *
FROM fb.order
LEFT JOIN item  on
	order.item_name = item.item_name
WHERE fb.order.order_status = 'Served' and tab_name='A'

I have two different tables "order" and "item" which i joined using left join.

In order table there is a column name called quantity and in item table, there is a column called price.

What i need to do is multiply the quantity from order with the price from item then get a total value.

There will be several order records added together for the final sum...

Can anyone point me, how to code the counting part in php.

[silviuks]

why don't you use PHP?

[d5e5]

You could alter your SELECT statement to explicitly name the columns you want in your result set (instead of *) and include a calculated column which you would specify something like this:

SELECT order.item_name, order.quantity, item.price, order.quantity*item.price AS value 
FROM fb.order
LEFT JOIN item  on
	order.item_name = item.item_name
WHERE fb.order.order_status = 'Served' and tab_name='A'

(But then in PHP you would need to accumulate the value for each row in a variable to get your total value.)

BTW maybe it's not a good idea to name your table 'order' because it is a MySQL reserved word.

[seventeen]

yeah try use name "order_table" or etc, don't use only "order"

[ariffin246]

Thanks you guys, I already change the table name from order to order_item. And figured out the calculation myself.

Once again thanks for the help...