Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,506
    Thanks
    72
    Thanked 105 Times in 104 Posts

    Possible to display last post?

    So I finished a script to post user news. They can keep posting new messages, but I'd like to find a way that it always displays the newest one on the main page. This is my current setup:

    id smallint (5) - This is the id of the alliance the users in
    name varchar(30) - This is name of the alliance
    news text - Of course the news to be posted
    deleteid mediumint (10) AUTOINCREMENT - So users can delete their messages one by one

    So how can I check to make sure it's the newest? Finding the highest deleteid matching their alliance's id or name works but should be an easier way.

    Thanks
    Last edited by myfayt; 08-02-2014 at 10:44 PM.
    Been a sign maker for 7 years. My business:
    American Made Signs

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,566 Times in 4,530 Posts
    Bad DB design.

    Why would you put BOTH the ID and the NAME of the user's alliance into this table?

    And what I really don't understand is where you have the person doing the posting defined ANYWHERE in that.

    Generally, you would have done that with AT LEAST 3 tables:
    Code:
    CREATE TABLE alliances (
        allianceid INT AUTOINCREMENT,
        alliancename VARCHAR(30)
    ) ENGINE INNODB;
    
    CREATE TABLE users (
        userid INT AUTOINCREMENT,
        username VARCHAR(30),
        allianceid INT,
        password VARCHAR(20),
        ... etc. ...
        CONSTRAINT FOREIGN KEY allianceid REFERENCES alliances(allianceid)
    ) ENGINE INNODB;
    
    CREATE TABLE news (
        newsid INT AUTOINCREMENT,
        userid INT,
        news TEXT,
        CONSTRAINT FOREIGN KEY userid REFERENCES users(userid)
    )  ENGINE INNODB;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,566 Times in 4,530 Posts
    As for posting the latest news, presumably displaying the name of the user who posted it and the alliance he belongs to:
    Code:
    SELECT u.username, a.alliancename, n.*
    FROM news AS n, alliances AS a, users AS u
    WHERE news.userid = u.userid AND u.allianceid = a.allianceid
    ORDER BY n.newsid DESC
    LIMIT 1
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,506
    Thanks
    72
    Thanked 105 Times in 104 Posts
    The user who posts it doesn't matter because only the members in that alliance can read it and the founder and leader rank is the only ones who can post news.
    The name of the alliance is just for my purpose. When there is over 100 alliances, instead of finding whose alliance is which id, I find it easier to search by name. So I am not using name in a query.
    Been a sign maker for 7 years. My business:
    American Made Signs

  • #5
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,506
    Thanks
    72
    Thanked 105 Times in 104 Posts
    Got it figured out, going to use a timestamp and order by DESC with it, with a limit of 1
    Been a sign maker for 7 years. My business:
    American Made Signs


  •  

    Posting Permissions

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