Unsure how to proceed

[Maxconnections]

Hello Everyone,

 

I need some advice on how to proceed with my code.

 

What I am attempting to acheive ...

I am writing an out of stock script, all is working fine except for the one thing,

In my Database table I have numerous product id's (prod_id)  from different clients (cus_id)

I would like to write some code that presents to the customer a message when they on on a product page they have marked for notification when back in stock when they are logged in, so the way i see it working is to pad for the cust id, then add all the product id's asscociated with the cust id, then compare it to the prod_id that is being viewed, there is already a page under the account section that shows all items added to the notifications with the options to delete them, but feel this extra bit will top it off.

 

I have tried many different things, and I think it has to be some  kind of loop, but am totally stuck.

Can any of you give me some advice on how to go please?

[Barand]

You need a structure similar to this

+-------------+                                  +-----------+
| product     |                                  | customer  |
+-------------+           +------------+         +-----------+
| prod_id     |----+      |  notify    |   +-----| cust_id   |
| prod_name   |    |      +------------+   |     | cust_name |
| stk_qty     |    |      | cust_id    |>--+     | etc       |
| etc         |    +----< | prod_id    |         +-----------+
+-------------+           +------------+

then you would query the tables to find those products in the customer's notify items that have stock

SELECT 
    p.prod_id
    , prod_name
    , stk_qty
FROM product p
    INNER JOIN notify n USING (prod_id)
WHERE stk_qty > 0 AND n.cust_id = ?

Edited October 3, 2014 by Barand

[Maxconnections]

Hello Barand

Thank you for your response I thought it would involve a loop!

I will go and try this now.

Regards

[Maxconnections]

I tried this and when it did work fine, i am not able to use it to generate a message like "You have this item set to notifiy you once back in stock"

To clarify, I am able to access all information like stock qty, but am unable to put into code that says, this item is in your alert already so I will push a message to the page to alert you. Both for info and to assure the client they have the alert set.

 

I am having real trouble with this but am not sure why as I have coded everything else!

[Barand]

If you know the cust_id and the prod_id and want to know if there is a notification recorded for it then query the notify table to see if there are any records for that prod/cust combination.

SELECT COUNT(*) as total
FROM notify
WHERE prod_id = ? AND cust_id = ?

If the count is > 0 then the there is already a notification in place.

[Maxconnections]

hi Barand

The brain fog is clearing now thanks to you!

I beleive I am onthe right path now.

Thanks for your help.