SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: mysql queries

  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql queries

    Hello,

    I have two questions.

    I am working on a php/mysql driven website. It allows users to make posts and other users to comment on them.

    I am currently working on a notification system where

    the user will be notified whenever somebody makes a post.
    OR
    if the user has commented on a post, the user will be notified of further new comments.

    My current method is a subscription based structure, where when somebody makes a comment or post, their user_id and post_id will be inserted into a subscribed' table. Is this an efficient method?

    Second question:

    how can I stop a duplicate row entry?

    e.g if the table data is:

    John, 102

    It won't duplicate another entry of John, 102.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    Second question:

    how can I stop a duplicate row entry?

    e.g if the table data is:

    John, 102

    It won't duplicate another entry of John, 102.
    In this case (these are the only 2 columns of the table, right?) I'd make them the primary key.
    For columns that are not the primary key of the table, you can add a unique index.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    My current method is a subscription based structure, where when somebody makes a comment or post, their user_id and post_id will be inserted into a subscribed' table. Is this an efficient method?
    yes
    Quote Originally Posted by Jaynesh View Post
    how can I stop a duplicate row entry?
    ordinarily with a UNIQUE constraint but in your case i would recommend the PRIMARY KEY

    since you said the table consists of only user_id and post_id, that should be the PRIMARY KEY

    CREATE TABLE subscriptions
    ( user_id INTEGER NOT NULL
    , post_id INTEGER NOT NULL
    , PRIMARY KEY ( user_id, post_id )
    , INDEX rev ( post_id , user_id )
    );

    the PRIMARY KEY ensures uniqueness, and is used for searches for posts where the user_id is known, while the "rev" index is used for searches for users where the post_id is known

    Edit: curses, sniped again!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more question. How do I remove notifications when the user has viewed them?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    One more question. How do I remove notifications when the user has viewed them?
    could you please be a bit more specific? how does this relate to duplicate rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello, I speak about my notification system.

    "My current method is a subscription based structure, where when somebody makes a comment or post, their user_id and post_id will be inserted into a subscribed' table. Is this an efficient method?"

  7. #7
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    notification system

    Hello

    I am working on a php/mysql driven website.

    A user can make posts and other users can comment on them.

    What is the best way to build a notification system, where a user is notified of when somebody makes a comment and when a user that has commented receives new comments.

    Something similar to facebook notifications, but a lot more basic.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, i see

    well, to remove a notification, you would delete that row in the table

    yes, that's efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •