How do i get MySQL dbs to talk to each other?

OK, total newby in mysql, learning slowly but surley

I have 2 db's

one holds stock info
one holds client info

I want to make it so when a client buys a product, the supply number on the stock db will go down by how ever many they buy

and when we restock, to enter information to make the stock go up.

So how do i do this.

any good sites to learn will be excellent.

I need to learn this first, rather than 300 pages deep into a book.

I am using MySQL version 3.23.51nt and also using mysql-front

 

Im a bit of a newbie aswell to php/mysql so this is just my recommendation.

Firstly, I dont think you need two databases just the one would be sufficient. In the database you may have three tables; one may have columns for customerid, name, address, etc the second table might have productsid and quantity , and a third might have orders productsid and customerid. When someone orders a product you would just use the following in your script after the product is ordered:

UPDATE database_name quantity = quantity - 1
WHERE productid = 5

The "-1" would have to be a variable returning a number so that if someone orders 2 or 3 then the database is updated respectively.

The "5" would be set to a unique number you give to the product in your database.

To increase the number you use the same sql statment but change the "-" to "+".

Im pretty sure some php/mysql expert will come along soon to give a better explanation

 

might be a good idea to have this info all in one database to limit the number of database connections each time you need an update.

the SQL sytax previosly posted looks like it will work.

 

My Mistake

I do have 1 db, i have 2 tables inside the db

1st table with product info (a unique product id is on off them)
2nd table client info, what they brought, date, ect, for invoices and checking sales

What i wanted was everytime a product is sold, table 2 takes one off the field in table 1.

What i have done now is another query
select the field, add 1 to number, update field.

 

OK, well the code given by the other user:

UPDATE database_name quantity = quantity - 1
WHERE productid = 5

Would do that all in one step

 

dont forget the SET before quantity

 

Good shout dsm56,

UPDATE database_name SET quantity = quantity - 1
WHERE productid = 5

 

ok then

I have 4 columns in the table
su_price, IVA, RE and total_price

Is there a way to get total_price to be
(su_price * IVA / 100)* RE / 100

 

simply follow what l234244 stated, and use

update database_name set quantity= quantity-1 , total_price = your formula

where product...