MySQL select from a table and check in another table if the same value exists

Question

Okay, I am creating a digital store.

Need some help on this part.

First my database is looking like this:

products prod_id, prod_file_id, prod_status

files file_id

So what I want to do is after they paid with paypal(already got the ipn working), it should plus the "prod_file_id" with one, and then check if the "file_id" exists in "files". If not it should put "prod_status" = 0.

Cant figure out how to do that, as im still new to coding. Hope you are able to help me out, thank you.

Answer 1

Try the following:

plus the "prod_file_id" with one:

update products 
set prod_file_id = prod_file_id + 1
where prod_id = 'the_prod_id_that_have_been_paid'

check if the "file_id" exists in "files". If not it should put "prod_status" = 0

update products set prod_status = 0
where prod_id = 'the_prod_id_that_have_been_paid' 
    and prod_file_id not in (select file_id from files)

Note: I assumed that prod_file_id is foreign key for file_id so that products and files table have proper relationship.

UPDATE : I updated the second sql command. It should update prod_status if file_id not exists, so I make it prod_file_id NOT in (select..

Answer 2

Try join products and files on prod_file_id + 1 = file_id