Php / Mysql Notification Type Idea

[mds1256]

Hi

 

I am working on a hobby project (to learn more about PHP and MySQL) and have the following idea but not sure the best way to execute the idea.

 

The scenario:

 

When an item is inserted into the database under a certain category (e.g. Furniture). Everyone that is signed up to the website that has an interest in the category 'Furniture' will see this item in their inbox area on the site. This works fine and uses the category column on the inserted data. (SQL - select * from item where category = 'furniture' ---- not actual script but you get the idea).

 

So my next idea is to create a notification type feature. So on the website there is a button called 'Inbox for Items' with a number beside of this button which is actually the number of items for their category (furniture). This works fine too using a count SQL query. But i want it to be a bit more intelligent and know which items they have looked at so it only shows the count notification for how many they have not looked at.

 

Obviously there are going to be multiple users of this site so I cannot just add a column onto the table where the item is either acknowledged or not as their will affect all users and not just that user who has acknowledged that one item.

 

Any ideas on the best way to accomplish this?

[twistedvengeance]

ASP and Ruby.

PHP will not do this to the full extent you want.

[Pikachu2000]

Really? Please, do elaborate as to why php won't do this.

[kicken]

You'd need to store a map of items the user has viewed and compare that to the list of items.  Then grab a count of the unviewed items.  You can do this with a map table and a left-join, eg:

create table user_items_viewed ( userId INT NOT NULL, itemId INT NOT NULL);

 

Whenever the user views an item, add that item's ID and the user's ID into the above table as a new row.  To do a count of the unread items, you select from the items table, left-join the above table, and then count the rows where there is no entry in the above table (ie the columns are null)

 

SELECT
  COUNT(*) as unviewed
FROM items i
LEFT JOIN user_items_viewed v ON i.itemId=v.itemId AND v.userId=123
WHERE
  v.userId IS NULL

 

[mds1256]

Hi

 

Thanks, I was thinking about that but when the table starts getting 1000's of mapping rows and the other table starts getting lots of items will this not slow right down, I know I will need to put indexes on the relevant columns.

 

 

[kicken]

You'd be getting up to a few million rows before you notice any real slow down.  If you get to that point then you can look into partitioning to spread the data out for quicker access.

 

[twistedvengeance]

I may have been confused as to what you were planning on doing. I assumed you wanted it to pop up, like Facebook, when the event happens. PHP can't do that, since it would be active-reload. My apologies.

[mds1256]

I may have been confused as to what you were planning on doing. I assumed you wanted it to pop up, like Facebook, when the event happens. PHP can't do that, since it would be active-reload. My apologies.

 

Hi, basically I am not to worried about it being real time as such, more about on the next page click the query will re-run and do a count on new items which haven't been acknowledged by the current user.

[Christian F.]

Twistedvengeance: Not only did he never once mention something about anything popping up, nor Facebook, but both ASP (or rather VB as I assume you meant) and Ruby are server-side languages as well. Meaning they'd have to do exactly the same as PHP to update the notification screen: To check on each page load, or use AJAX to periodically poll the server.