0
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.

4
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by ariffin246
0

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.

Edited by d5e5: 'order' is capitalised by the [code][/code] tags?

0

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...

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.