database query help

[herghost]

Hi all,

 

I am having some problems getting my head around this. I have two table

 

Table 1 - T1

fields(user_id, product_id, quantity)

Row1values(1,5,2)

row2values(1,1,10)

 

Table 2 - T2

fields(product_id, product_name, product_price)

row1values(5, exampleproduct, 5)

row2values(1, otherproduct, 5)

 

Now I want to display all the products that user_id  = 1 has, including there name and total value ie:

 

Basket

 

Product Name - Product Price - Quantity - Total Value

exampleproduct  -  5  - 2 - 10

otherproduct - 5 - 10 - 50

 

Total = 60

 

How would I go about this?

 

Many Thanks

 

[kickstart]

Hi

 

This would get you the list:-

 

SELECT T2.Product_name, T2.product_price, T1.quantity, T1.quantity * T2.product_price
FROM T1
JOIN T2 ON T1.product_id = T2.product_id
WHERE user_id = 1

 

However I can't see an efficient way to add the total into that.

 

You can get the total using another piece of SQL:-

 

SELECT T1.user_id, T1.quantity * T2.product_price
FROM T1
JOIN T2 ON T1.product_id = T2.product_id
WHERE user_id = 1
GROUP BY T1.user_id

 

or you could just manually add them up as you loop around in php to display the results of the first query.

 

If you really did want to combine them then you could use something like:-

 

SELECT T1.user_id, T2.Product_name, T2.product_price, T1.quantity, T1.quantity * T2.product_price, T3.TotalPrice
FROM T1
JOIN T2 ON T1.product_id = T2.product_id
JOIN (SELECT z.user_id, z.quantity * y.product_price AS TotalPrice
FROM T1 z
JOIN T2 y ON z.product_id = y.product_id
GROUP BY z.user_id) T3 ON T1.user_id = T3.user_id
WHERE user_id = 1

 

All the best

 

Keith

[herghost]

Thanks Keith, thats a great starting point. I think I need to do a bit more research on this as I understand very little of that

[herghost]

just a quick one, for the first example, how would I display this??!!

 

Thanks

[kickstart]

Hi

 

Quick hack at it (probably a typo or 2 in there, and you will need the code to connect to the database, etc):-

 

<?php
$UserId = 1; // Or whatever you want to populate it with after validation
$query = mysql_query('SELECT T2.Product_name, T2.product_price, T1.quantity, T1.quantity * T2.product_price AS TotValue FROM T1 JOIN T2 ON T1.product_id = T2.product_id WHERE user_id = '.$UserId)

$TotalValue = 0;
echo '<table><tr><td>Product Name</td><td>Product Price</td><td>Quantity</td><td>Total Value</td></tr>';
while($row = mysql_fetch_assoc($query))
{
echo '<tr><td>'.$row['Product_name'].'</td><td>'.$row['product_price'].'</td><td>'.$row['quantity'].'</td><td>'.$row['TotValue'].'</td></tr>';
$TotalValue += $row['TotValue'];
}
echo "<tr><td>Total Value</td><td></td><td></td><td>$TotalValue</td></tr>";
echo '</table>';
?>

 

All the best

 

Keith

[herghost]

Thanks again Keith, should be able to work it out from here

[herghost]

Hi Keith, Sorry I am getting this error:

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\stockluck\pages\mystocks.php on line 18

 

Line 18 is

while($row = mysql_fetch_assoc($query))

[kickstart]

Hi

 

Suggests I have got an error in that SQL. Can't see anything obvious (although spotted I missed a ; at the end of the query line), unless user_id is not a numeric field.

 

Change it to:-

 

$query = mysql_query('SELECT T2.Product_name, T2.product_price, T1.quantity, T1.quantity * T2.product_price AS TotValue FROM T1 JOIN T2 ON T1.product_id = T2.product_id WHERE user_id = '.$UserId) or die(mysql_error());

 

And see what error it gives you

 

All the best

 

Keith