Hi,

I'm using PHP with a MySQL backend, basically this part of my system allows the user (student) to purchase items. this will then update the quantity of that product so something like:



and then update the students new balance so:



Obviously this will not work and requires a join of some sort but am pretty new to MySQL and can't think how the join will work exactly...

Any help with this will be much appreciated,

Regards,

Simon.

Simon,
I posted on the other site. I'm not clear on your design.

I also wanted to say, when you create your query, you have understand what you want back. Very often when you have a 'parent - child' relationship, you'll write a left join query that brings back a result set of both tables. You only display

For example, if you displayed ALL the orders student #49 has, and lets say they have three orders.

SELECT std.std_id, std.f_nm, std.l_nm, std.addr, std.bal, ord.ord_id, ord.std_id, ord_total FROM student std LEFT JOIN order ord on ord.std_id = std.std_id WHERE std.std_id ='49'


This type of query returns three rows, each of those rows has the exact same student id, student first namd, student last name (std_id, f_nm, l_nm). So when you loop through this you'll retrieve the student information once and each unique order this student has.

Hope that helps
Good Design, Saves Time

Hi yeah sorry guess I didn't explain very well.

Ok, the purpose of this query is simply to update the students balance by taking the current balance and deducting the cost of the product. So we need to take the current "BALANCE" of the student (eg. studentid: 1) from table "STUDENTS" and deduct it by the "PRICE" of the product (eg. productid: 1) from table "PRODUCTS".

Table structure is shown below:

PRODUCTS.
productid, name, description, price, quantity.

STUDENTS.
studentid, name, userid, balance.


I have a seperate query which updates the quantity of the stock. And another seperate query which adds all the required details into table TRANSACTIONS. But these two I think i've sorted out.

Hope this helps, thanks for the response and any futher help will be much appreciated.

You're mixing PHP and SQL in your thoughts, I dare say ...
where does PHP-variable "$price" come from in the second query ?
Still then , there is no stockid in the students table - or is there ?

Try it this way:

$sql="update student set balance = balance -
(select price from stock where stkid = $id)"

<edit or better>

$sql="update student set balance = balance -
(select price from products where productid = $id)"



..should work if stkid is unique within stock resp. productid in products .. which I suppose it is ...