Skip to main content
W
wycks
Inspiring
August 5, 2010
Question

two questions

  • August 5, 2010
  • 1 reply
  • 387 views

I'm still a beginner but have gotten to the point where I have done everything I planned to do, but have a couple extra things that I'd like to do but can't figure out how to do them.

First, I'm trying to set up a way so users can follow others by having the posts of people they follow shown on their profile. I've set up a table in my database with the primary key column, a follower_id colum (which is another name for their user_id for their accounts/posts in other tables) and the followed_id.  I can't figure out how to get the posts made in other tables to show up on their profile... i'm confusing myself even as I type this.

The second, which I realize I'll have to learn more to do (but would like to be pointed in the right direction) is how to set up a system where it shows only the private messages that they haven't read instead of all the ones they have in a dynamic table.

Thanks

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    August 5, 2010

    Regarding this:

    I've set up a table in my database with the primary key column, a  follower_id colum (which is another name for their user_id for their  accounts/posts in other tables) and the followed_id.

    I'd do it differently.  I'd make follower_id and followed_id the primary key for that table.  You may not need any other columns, it depends on your requirements.  But to get to:

    trying to set up a way so users can follow others by having the posts of people they follow shown on their profile

    You would need a query along these lines.

    select some posts

    from the post table t1 join the many to many table t2 on t1.poster_id = t2.followed_id

    where t2.follower_id = the user who is going to read those posts.

    To do this:

    set up a system where it shows only the private messages that they  haven't read instead of all the ones they have in a dynamic table

    You will have to start by determining how to store the data that indicates a post is private and who has read it.  My initial thoughts are that it will be another many to many table, but with more data in it.

    You seem to be off to a good start for someone who claims to be a beginner.

    W
    wycksAuthor
    Inspiring
    August 6, 2010

    Thank you for the response.

    I'm about to work on the first issue--the follower/fellowed stuff... is that a query thing or something I have to do with the database tables themselves outside of coldfusion code?  I've been using NaviCat to create my databases so I haven't really learned as much about databases as I should--i know little about foreign keys, indexes, different kinds of relationships and multi-table relationships and have been working without them which has made me short term process easier but now that i'm to some of the more complicated parts of my site, things are probably more difficult than they should be at this point.)

    but as for the second part--

    if i create a new database, what kind of code is needed to populate the new database that keeps track of read/unread pages?  If I created a new databse, there would be the page_id as the primary key, user_id (Which is determined by a session variable that's already set up and working in other ways on my site) and then the third column would be the URLs I guess (not sure if that's the best way to do it.)_

    WHat I already tried doing was having a column in my Private Message's table that is read/unread (yes/no).  I tried creating an update record form where every field was hidden and given the value it already had except for the read/unread column, which I tried to change to 'yes.'  But that didn't work and I'm sure its because of more than one reason.

    To keep track of the users pages they've already seen, I figure there would need to be some code put into Application.cfc, but I don't know how to insert into a table without having a form to do the work.  (this is why I'm a beginner--i've been learning as i go which means I haven't really learned the best and most efficient ways to do things).

    D
    Dan_Bracuk
    Inspiring
    August 6, 2010

    A well designed database will make your life a lot easier.  To learn more, I've heard good things about the book, Database Design for Mere Mortals.

    Your post suggests creating new databases.  Unless NaviCat uses the principle that each table is a separate database, I'm not sure why you need more than one.

    I assume that general pages are separate than private messages.  You might find it simpler to make private messages a subset of general pages.  That's done at the database design phase.  Also at the database design phase, you have a table that has the followed_id, follower_id, page_id, and read.

    When someone posts a general page, the following things happen:

    It's entered into a database which gives you a page_id.

    The table mentioned earlier is queried to get all the follwer_id's.

    This third table is populated.  The default value of read will be No, or however you want to do it.

    If it's a private message, the second step is replaced by something to get the follower_id of the intended recipient.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices