Using data from two different tables

[Venom Rush]

EDIT: I just realized I should have posted this in the MySQL section. If a mod can move it there I'd appreciate it. Thanks :)

Hi all

I have two tables. The first one has the details of each fruit, its code, markup percentage, it's cost price and the amount per bag. The second table is a "cart" where people select what fruit they want to buy. All that is stored in that table is the fruits code, how many units of that fruit they want and their session code.

What I need to do is calculate prices based on the information from Table Fruit with the items someone has selected in Table Cart.

What I'm doing at the moment is selecting all the information from Table Fruit and assigning it to variables. The problem is that the last row selected always overrides the variables. So I tried creating arrays to store each column of data in but my page doesn't display when I assign the arrays. Example below:

$name = array;

// select statement goes here

while($row = mysql_fetch_row($result))
{
    $name += $row[0];
}

I then looked up JOINS and that doesn't help me because there are going to be multiple different session values in Table Cart.

Example: I need to take row 1 in Table Cart and get the price, markup and amount from Table Fruit in order to work out how much the person's order of apples is going to cost them. The same goes for the rest of their order.
So the person has seleceted 3 packets of 10 apples each with a 10% markup on the cost price which is 50.5.

(($price + ($price * $markup)) * $amount) * $units;

Total should be 1666.5 if the way I'm calculating it is correct.

Below you'll see examples of two tables that I have.

Table Fruit:
name | code | markup | price | amount
Apples | A10 | 10 | 50.5 | 10
Bananas | B10 | 5 | 50.5 | 10
Pineapples | P10 | 5 | 25.5 | 10
Melons | M1 | 5 | 100.5 | 1

Table Cart
session | units | code
abcdef | 3 | A10
abcdef | 1 | B10
abcdef | 5 | M1

Thanks in advance for any help ;)

[cwarn23]

When combining tables to make one big table, you need to have a common column to pair with which in your case appears to be the column named "code". A tutorial can be found at http://www.tizag.com/mysqlTutorial/mysqljoins.php and below is a query example:

SELECT *
FROM fruit, cart
WHERE fruit.code = cart.code

[Venom Rush]

When combining tables to make one big table, you need to have a common column to pair with which in your case appears to be the column named "code". A tutorial can be found at http://www.tizag.com/mysqlTutorial/mysqljoins.php and below is a query example:

SELECT *
FROM fruit, cart
WHERE fruit.code = cart.code

Hi cwarn

I've never gone further than doing simple select, update and delete requests but if I understand this correctly JOINS join two tables together, so when I do the while loop for the initial JOIN then I nest another MySQL statement looking for the relevant info in order for me to calculate the total amounts?

[cwarn23]

Hi cwarn

I've never gone further than doing simple select, update and delete requests but if I understand this correctly JOINS join two tables together, so when I do the while loop for the initial JOIN then I nest another MySQL statement looking for the relevant info in order for me to calculate the total amounts?

From what I can understand of that yes that is correct. So basically it just joins the columns of the two tables so there is one big table with lots of columns then the new data joins with the existing table data where those two columns are the same if you get what I mean. So have a play around with it and you'll see what I mean.

[Venom Rush]

Hmmm, I've tried joining the two tables with a normal join, left join and a right join but they all come back with the error of "Column 'code' in field list is ambiguous"

I think this may be because the code column in Table Cart has multiple entries of the same code due to multiple people selecting items.

[davidn]

Hmmm, I've tried joining the two tables with a normal join, left join and a right join but they all come back with the error of "Column 'code' in field list is ambiguous"

I think this may be because the code column in Table Cart has multiple entries of the same code due to multiple people selecting items.

If you want to do all the math through just SQL, you can do something similiar to the following:

[b]SELECT[/b] session, [b]SUM[/b]((markup/100.0* price + price)* amount * units) [b]AS[/b] total [b]FROM[/b] cart, fruit [b]WHERE[/b] fruit.code = cart.code [b]GROUP BY[/b] session;

This will return a table with two columns consisting of session name and total that you can then play with
-----------------------------------------------------------
http://www.raima.com

[Venom Rush]

Thanks davidn. I managed to sort out my problem with a join though. But I'll definitely keep your method in mind the next time I run into something similar. ;)

@ cwarn23
Thanks for the help. Much appreciated ;)