1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Filter and Show records changed

Discussion in 'MySQL' started by piropeator, Oct 31, 2016.

  1. #1
    Hi. I have a table : code, name and date fields, sorted by date.
    100 Luiz 2016-03-31
    100 Luiz 2016-04-30
    100 Luis 2016-05-31
    100 Luis 2016-06-30
    200 Juam 2016-04-30
    200 Juan 2016-05-31
    300 Rosa  2016-04-30
    300 Rosa  2016-05-31
    300 Rossana 2016-06-30
    HTML:
    Some records of some codes was changed. I need to filter and show that records changed.
    Like this:
    100 Luis 2016-05-31
    200 Juan 2016-05-31
    300 Rossana 2016-06-30
    HTML:
    I tried to use this:
    select code, distinct name, date
      from table
    group by code
    PHP:
    But does not work.
    I accept suggestions.
    SEMrush
     
    Solved! View solution.
    piropeator, Oct 31, 2016 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #2
    How do you know which ones have changed?
     
    PoPSiCLe, Oct 31, 2016 IP
  3. homemadejam

    homemadejam Active Member

    Messages:
    137
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    65
    #3
    Like PoPSiCLe said, from the information you have provided, you are unable to tell if a field has changed or not.
    The method I would use is something like:

    id | code | name | created_at | updated_at

    When you create each individual entry, update the created_at to NOW().
    Each time you update a record, updated the created_at with NOW().

    This way you will know when each record was last updated, and are able to query the results to show the last modified entries, or give it a range, eg updated between X and Y dates.
     
    homemadejam, Nov 1, 2016 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #4
    But that will still not tell if something has been updated more than once. A better approach would be creating a separate table with the id for the data, and an update-date. Of course you could do a check for this based on current date, and query for updates happening the last 5 days or so, but the whole construction is a bit silly.
     
    PoPSiCLe, Nov 1, 2016 IP
  5. piropeator

    piropeator Active Member

    Messages:
    193
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #5
    This table contains monthly movements. It is in chronological order.
    And the only way to know whether or not renamed is compared with the previous name.
    You can see that in the example I gave.
     
    piropeator, Nov 2, 2016 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #6
    Can't you just fetch the latest entry per ID? That won't tell you about changes, but it will list the current/updated content?
     
    PoPSiCLe, Nov 3, 2016 IP
  7. piropeator

    piropeator Active Member

    Messages:
    193
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #7
    Use something like that:
    SELECT code, name, max(date) FROM tabla  group by code
    PHP:
    ?? This show the last name in the last date. Dont you?
    I need the date when the name was change.
    100 PAUL 2016-03-31
    100 PAUL 2016-04-30
    100 ROBERT 2016-05-31  <-- Here the name was change, so this date is what I need.
    100 ROBERT 2016-07-31
    HTML:
     
    piropeator, Nov 4, 2016 IP
  8. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #8
    Then you will have to do something else, because that table-construct is not working for your needs. You should really have a check for changes, and mark the changed ones. Something like (when it's being updated), *if name != $SQL-name // set updated = 1* (pseudo-code). If you don't have that, you will have to pull the data, and parse it, most likely.
     
    PoPSiCLe, Nov 4, 2016 IP
  9. piropeator

    piropeator Active Member

    Messages:
    193
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #9
    Like this?
    UPDATE table
    SET flag = 'X' where name<> (SELECT name FROM table table GROUP BY code ORDER BY date)
    
    PHP:
    or something that?
     
    piropeator, Nov 5, 2016 IP
  10. #10
    Yes, something like that, yes - That way you can pinpoint the latest changes, or if there has been a change.
     
    PoPSiCLe, Nov 6, 2016 IP