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 14 of 14
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    How to display NEW info from database table?

    I am writing a query to display the last 5 results that were entered into one of my tables. The problem is, I only want to display them only if they are new (don't exist). I am stumped and cannot think it through. Here is the code for the query

    $query = "SELECT DISTINCT `name` FROM `title` ORDER BY `titleID` DESC LIMIT 5";
    $result = mysql_query($query);
    while ($row = mysql_fetch_assoc($result)) {
    echo '<a href="home.php">'.$row['name']. '</a><br>';
    }
    Im not sure what to do next. The only thing this reminds me of is searching when a user inputs a piece of data into the table, but since this is just grabbing data that exists, I am confused
    Last edited by <?php???>; 01-24-2012 at 05:09 AM.

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    That code will display the last five entries from the table (or all if there are less than 5) all as links to home.php

    If the entries don't exist then of course the query will be unable to return them.

    Perhaps what you need to determine if they are new is to store the date and time with each entry and add a WHERE clause that tests for less than however old they are allowed to be and still be considered new.

    If you have some other criteria for what "new" means then you'll need to be a bit more specific (what is it that doesn't exist for new entries?)
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    That code will display the last five entries from the table (or all if there are less than 5) all as links to home.php

    If the entries don't exist then of course the query will be unable to return them.

    Perhaps what you need to determine if they are new is to store the date and time with each entry and add a WHERE clause that tests for less than however old they are allowed to be and still be considered new.

    If you have some other criteria for what "new" means then you'll need to be a bit more specific (what is it that doesn't exist for new entries?)
    Yes I explained it wrong, the more I think about it the more I think am asking the wrong question.

    As of now, any user can add a title, stored in the title table. Since each user can have a title with the same name, the title table will have duplicates. DISTINCT makes sure it doesn't show the same name, BUT say someone just submitted a title, I want to know if that is new or already exists. If it is new, I want it to be displayed. If it already exists, I don't want it to. Basically what I want to accomplish is show the most recent NEW titles.

    I know I am explaining it terribly so i will give an example: Say this is the current list:

    Phones
    Computers
    Baseball
    Snakes
    Bears

    Now if someone submits a list titled "Computers", it would go back to the top of the list. That is what I want to avoid. If its already in the database, I want it to be left out. But if Someone submits a list 'Hats' AND is not in the database, I want it on the list.

    I hope that helps.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,565 Times in 4,529 Posts
    Well, once the data s in the table, the only way to distiguish new from old would be a timestamp of some kind.

    You *could* exclude any titles that occur more than once, though. Is that what you want?

    Code:
    SELECT name, COUNT(*) AS howMany
    FROM title
    GROUP BY name
    HAVING howMany = 1
    ORDER BY titleID DESC 
    LIMIT 5
    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.

  • Users who have thanked Old Pedant for this post:

    <?php???> (01-24-2012)

  • #5
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Well, once the data s in the table, the only way to distiguish new from old would be a timestamp of some kind.

    You *could* exclude any titles that occur more than once, though. Is that what you want?

    Code:
    SELECT name, COUNT(*) AS howMany
    FROM title
    GROUP BY name
    HAVING howMany = 1
    ORDER BY titleID DESC 
    LIMIT 5
    This query is getting no rows back, maybe I am doing something wrong. But excluding titles that occur more than once won't help me I don't think because it it will still pull up the first item, THEN exclude the rest where I want to ONLY show it IF there is no another item that matches it.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,565 Times in 4,529 Posts
    That query should have done just what you said.

    But there's an easy way to find out what the situation really is.

    Do this:
    Code:
    SELECT name, COUNT(*) AS howMany
    FROM title
    GROUP BY name
    ORDER BY howMany ASC
    Do that in a query tool, not in PHP code.

    See what that shows you.

    If you have any names that only occur once, they will show up first in the list.

    But if the first record you see from that query shows a howMany value that is greater than 1, then you simply don't have any names that qualify: Every name has two or more matches.
    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.

  • Users who have thanked Old Pedant for this post:

    <?php???> (01-24-2012)

  • #7
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    That query should have done just what you said.

    But there's an easy way to find out what the situation really is.

    Do this:
    Code:
    SELECT name, COUNT(*) AS howMany
    FROM title
    GROUP BY name
    ORDER BY howMany ASC
    Do that in a query tool, not in PHP code.

    See what that shows you.

    If you have any names that only occur once, they will show up first in the list.

    But if the first record you see from that query shows a howMany value that is greater than 1, then you simply don't have any names that qualify: Every name has two or more matches.
    Two problems i see with this ( if I am understanding it correctly): it is not ordering by 'howMany'. When I do that query, it shows how many but the second row has a howMany value of 3. Also, I feel like even if it did return correctly, it would give the value of the new one a 1, which would put it behind the other "1"'s. So I guess that would be a good time to add the time stamp?


    EDIT: Wait I'm sorry, I think in theory that should work and I wouldn't need a time stamp, because it will get a 1 when it comes in and there are no others...so if I order it by DESC then it will be the only 1 there. Now I just have to figure out why it is not ordering by howMany.
    Last edited by <?php???>; 01-24-2012 at 03:13 AM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,565 Times in 4,529 Posts
    Once again, read what *YOU* wrote:
    I want to ONLY show it IF there is no another item that matches it.
    The *ONLY* way for that to happen is if a given name occurs ONE TIME AND ONE TIME ONLY in your table.

    As soon as a name has a count of 2, it does *NOT* fulfill the qualifications that you yourself set forth.

    So if you don't see a name that has a howMany field value of 1, then you have NO NAMES AT ALL in that table that meet your "IF there is no another item that matches it" criterion.
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,565 Times in 4,529 Posts
    Quote Originally Posted by <?php???> View Post
    Now I just have to figure out why it is not ordering by howMany.
    And I contend that it *IS* doing so. I contend that you simply do not have even one single name in the table that only occurs once in the table.

    But there's another easy way to find out: If you think there is a name that occurs only once, then just do this:
    Code:
    SELECT * FROM title WHERE name = 'xxxx'
    Just replace the xxxx with the name you expect to find only once.

    If you get back only one record from that SELECT, then you are right and I am wrong.

    But I'll bet a latte that I am right.
    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.

  • #10
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    And I contend that it *IS* doing so. I contend that you simply do not have even one single name in the table that only occurs once in the table.

    But there's another easy way to find out: If you think there is a name that occurs only once, then just do this:
    Code:
    SELECT * FROM title WHERE name = 'xxxx'
    Just replace the xxxx with the name you expect to find only once.

    If you get back only one record from that SELECT, then you are right and I am wrong.

    But I'll bet a latte that I am right.
    :/ I do though. "poptarts" only comes up once. You are a funny guy, and a coding genius, but I am bad at explaining myself. I modified the code a little to get it to work the way I *said* I wanted it to work (edit-I thought I modified it but its the same code you used in the fist place, I think I messed up with the ('')'s)

    Code:
    SELECT `name`, COUNT(*) AS 'howMany'
    FROM `title` 
    GROUP BY `name` 
    HAVING 'howMany' = 1
     ORDER BY `titleID` DESC 
    LIMIT 5
    So this is where I have to feel like an idiot: As soon as someone enters the same title, it will get a 2 and disappear from the list (which it does what its supposed to do)...BUT what I want is for the newer(and unique) titles to be on top until its replaces by a newer one.

    So say new and unique 5 look like this:

    1.poptarts
    2.soda
    3.xxxx
    4.yyyy
    5.xxxxx

    because soda is on the list, I dont want it to disappear when someone submits a list called soda, i want it to remain until a new unique title replaces it. My apologies for all of this...
    Last edited by <?php???>; 01-24-2012 at 04:03 AM.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,565 Times in 4,529 Posts
    See, that's what I thought you probably wanted all along, but you kept using words that convinced me I was wrong.

    One last question: Say that "soda" is in the list. When somebody adds "soda" again, does that bump "soda" to the top of the list (that is, do we used the latest time added as the "TOP 5" qualifier) or do we ignore the latest time added and only use the FIRST time added?

    Let's keep it simple. Say we only wan the top 2.

    Say we have these names with the "whenAdded" dates shown:
    Code:
    apple    1/1/2012
    banana  1/2/2012
    canteloupe 1/3/2012
    TO show the top 2 we would show:
    Code:
    canteloupe 1/3/2012
    banana  1/2/2012
    Okay so now somebody comes along and adds another "apple", so the full list is this:
    Code:
    apple    1/1/2012
    banana  1/2/2012
    canteloupe 1/3/2012
    apple   1/4/2012
    *NOW* what should we show for the top 2???

    Should it still be
    Code:
    canteloupe 1/3/2012
    banana  1/2/2012
    or should it change to
    Code:
    apple   1/4/2012
    canteloupe 1/3/2012
    ???

    Answer that, and I think we can finally start over and write the correct query.

    NOTE: If you have an AUTO_INCREMENT field on this table, you don't need a TIMESTAMP field.

    If you have a TIMESTAMP (whenAdded) field, you don't need the AUTO_INCREMENT field (but it can't hurt to have both...but you must have one of the other).
    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.

  • Users who have thanked Old Pedant for this post:

    <?php???> (01-24-2012)

  • #12
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    See, that's what I thought you probably wanted all along, but you kept using words that convinced me I was wrong.

    One last question: Say that "soda" is in the list. When somebody adds "soda" again, does that bump "soda" to the top of the list (that is, do we used the latest time added as the "TOP 5" qualifier) or do we ignore the latest time added and only use the FIRST time added?

    Let's keep it simple. Say we only wan the top 2.

    Say we have these names with the "whenAdded" dates shown:
    Code:
    apple    1/1/2012
    banana  1/2/2012
    canteloupe 1/3/2012
    TO show the top 2 we would show:
    Code:
    canteloupe 1/3/2012
    banana  1/2/2012
    Okay so now somebody comes along and adds another "apple", so the full list is this:
    Code:
    apple    1/1/2012
    banana  1/2/2012
    canteloupe 1/3/2012
    apple   1/4/2012
    *NOW* what should we show for the top 2???

    Should it still be
    Code:
    canteloupe 1/3/2012
    banana  1/2/2012
    or should it change to
    Code:
    apple   1/4/2012
    canteloupe 1/3/2012
    ???

    Answer that, and I think we can finally start over and write the correct query.

    NOTE: If you have an AUTO_INCREMENT field on this table, you don't need a TIMESTAMP field.

    If you have a TIMESTAMP (whenAdded) field, you don't need the AUTO_INCREMENT field (but it can't hurt to have both...but you must have one of the other).
    Okay, I want the first case to be true, where the apple added on 1/4/2012 does not show, only the first instance of it(which would show at #3 if we didnt limit it to 2). And titleID is the primary key so I dont think we should need the TIMESTAMP...but you are right it would be better to have one anyway.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,565 Times in 4,529 Posts
    No need for the TIMESTAMP, really.

    Okay this is easy:

    Code:
    SELECT name, MIN(titleID) AS firstOccurrence
    FROM title
    GROUP BY name
    ORDER BY firstOccurrence DESC'
    LIMIT 5
    You see it? By picking the first auto_increment value for each name, using MIN(), we are effectively ignoring all later additions with that same name.

    Presto.
    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.

  • Users who have thanked Old Pedant for this post:

    <?php???> (01-24-2012)

  • #14
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    No need for the TIMESTAMP, really.

    Okay this is easy:

    Code:
    SELECT name, MIN(titleID) AS firstOccurrence
    FROM title
    GROUP BY name
    ORDER BY firstOccurrence DESC'
    LIMIT 5
    You see it? By picking the first auto_increment value for each name, using MIN(), we are effectively ignoring all later additions with that same name.

    Presto.
    Its depressing thinking about how much I need to learn. But thank you for your help once again! I am going to be re-examining my database structure soon if I have any questions I'm sure it will be a little more challenging for you


  •  

    Posting Permissions

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