two tables need help subtracting

delightful

i have two tables, one a travel table and another the member table,,,

i want to subtract the value of a destination in travel table from the monetary value in the member table. I am unsure how to go about it, i use dreamweaver as well if that is easier to use.

Please help me, i have been pulling my hair out for the last few days.

Thanks

Sarah

rachel2004

You didn't post the table structures for either table, but from what you've described, this is something that could be done with a WHERE clause if you have a column value that each table shares:

SELECT (m.value - d.value) AS difference
FROM money_table AS m
LEFT JOIN destination_table AS d
ON (m.common_column = d.common_column)
WHERE m.id = 'some value';

delightful

travel table:
Field Type Null Default
America decimal(6,0) No 450

Europe decimal(6,0) No 1570

Russia decimal(6,0) No 2500

Asia decimal(6,0) No 3000

Canada decimal(6,0) No 600

Australia decimal(6,0) No 3500

member table:
loginName varchar(25) No

password varchar(255) No

email varchar(50) Yes NULL

firstName varchar(40) Yes NULL

lastName varchar(50) Yes NULL

street varchar(40) Yes NULL

city varchar(40) Yes NULL

zip varchar(12) Yes NULL

country varchar(20) Yes NULL

money int(17) No 1000000

guns varchar(20) Yes NULL

protection varchar(20) Yes NULL

rank varchar(18) Yes scum

bank int(17) No 0

bullets int(8) Yes 3000

createDate

these are my table structures, so basically, when the user clicks on the travel webpage and say clicks the europe button, i want the europe value from this travel table subtracted from the members money in the member table.

if you see what i mean, the query you have displayed may work, thank you, if the above helps with the query let me know more.

thanks

sarah

rachel2004

I'm not sure what decimal(x,x) No xxx is in your travel table. Also, what are you using to relate the two tables together? If you can straighten out your travel table and determine how the tables are related, you should be able to use my sample query.

delightful

the tables arent linked as yet, i am having some trouble with it.

the tables above is from my phpmyadmin that shows the tables as such.

so the value of america in that table is £450.
this is an example. i have £4500000 in my member account, i am on the travel page and wanting to travel to america and its going to cost me £450. This needs to be deducted from my cash within the database.

like i say i havent as yet joined or linked the two tables, i need some help in doing this if possible.

Thanks

Sarah

rachel2004

You know your tables better than anyone, so I don't know how much help I'll be as far as joining your tables. You need to look over your travel table and your member table and determine how one record in the travel table is associated with a record in the member table. If you haven't worked with database design before, you may want to do a Google search on the topic or do a search through the phpbuilder forum archives. One way to linking tables is to use the primary key (the record ID) from one table as a column in the second table. So now if you have member ID #20, there will be a column in your travel table called (for example) 'memberID'. If both the travel table and the member table have a member id value of '20', then we know we are talking about the same person (member) who went on such-and-such travel. This is only one way of doing it. Like I said, you may want to Google on a few online tutorials. After you get that down pat, my sample query will be more useful in solving your intial post/problem.