So i have two tables:
order_product
--------------------------------------------------
| ProductID | Quantity
--------------------------------------------------
products
-------------------------------------------------------------
| ProductID | productname | Desc | Price | Stock | Image
------------------------------------------------------------
and i need to get all of the same product ID, show their quantity then times that by their price and show the grand total of all.
My problem is i'm trying to show a checkout page which shows everything in a list, but how do i combine the two tables? Also, there are no foreign keys for the first table.
I need this in an sql statement as well preferably, like:
$sql = 'SELECT...'
Would this work?
$sql = "SELECT * FROM order_products
UNION
SELECT * FROM products"
If so, how do i know which row is which?
My desired output is all entries, now looking like this:
ProductID | Quantity | Productname | Desc | Price | Stock | Image
You need a classical JOIN clause:
SELECT *
FROM products
LEFT JOIN order_products on products.ProductId = order_products.ProductId
select table1.ProductID
, table1.Quantity
, table2.Productname
, table2.Desc
, table2.Price
, table2.Stock
, table2.Image
FROM table1
JOIN table2 ON table1.productid=table2.productid
My problem is i'm trying to show a checkout page which shows everything in a list, but how do i combine the two tables?
You need to simply use mysql JOIN to show cart items.
Your table data should be like as i added in demo.
See SQL Fiddle Demo
SELECT
o.id 'orderId',
o.ProductID 'pid',
SUM(o.Quantity) 'qty',
p.productname 'product',
p.`Price` 'price'
FROM
order_product o
INNER JOIN products p
ON p.`ProductID` = o.`ProductID`
Edit Required output
My desired output is all entries, now looking like this:
ProductID | Quantity | Productname | Desc | Price | Stock | Image
Modify above query's SELECT part SELECT tablename.your_column .....
Not sure exactly what's the output you wish to get is but you should use something like this
SELECT o.ProductID, o.Quantity, p.Price, o.Quantity * p.Price
FROM order_product o
LEFT JOIN products p ON o.ProductID = p.ProductID
Use inner join.
Select * from order_products op INNER JOIN products p ON p.ProductID = op.ProductID;
w.r.t. your need, the above query needs to be modified to:-
"SELECT *,
SUM(op.quantity * p.price) AS grandTotal
FROM order_products op
INNER JOIN PRODUCT p ON p.ProductID = op.ProductID WHERE p.ProductID =".$prodId(your php variable of product id);