As the title says, I have an added_date row in my db. I was reading through some sites and it said something about using INTERVAL.

What I want to do is just add a simple text that says NEW, beside each record that was added within 15 days.

How would I go abouts doing that??

Thanks in advance!

    Have a look at the date functions DATE ADD & DATE SUB . You would use either of them with today's date to calculate 15 days ago and then compare that against your date column.

      So after reviewing that I added this to my php file:

      $new = $db->Execute("SELECT * FROM `{$tables['category']['name']}` WHERE `DATE_ADDED` >= CURDATE() AND `DATE_ADDED` =< CURDATE() - INTERVAL 15 DAY");
      $tpl->assign($newadded, 'new');

      and this to my tpl file:

      {if $newadded}New{/if}

        UPDATE:

        It displays NEW, but for every record. I changed the interval to 1, and it still displays new to everything, even if it's not within one day.

          just tried that as well, but still nothing 🙁

            Why do you require:

            WHERE DATE_ADDED >= CURDATE()

            First: the date added will not be more or the same as curdate(), which drops out the rest of your query.

            Furthermore, you require them to be less then 15 days old, which is enough of a limit?

            Anyway.. I would personally do this at php output level, unless you ONLY want to display those which are new.. (Because you will now run two queries: First finding those which are new, second findiing all relevant records, and then you cross match them. Sounds like a query too many to me, not to mention memory usage)

            Otherwise:
            -Select all records which you want to display
            - As you loop through the records to display them, you check whether the date it less then 15 days old, and echo out 'new', or something, else not.

            Just a thought

              My objective is to display a text saying new, next to all of the newly added records. I'm running the sql query in a php file, but displaying it in a tpl file.

                I take it that your mysql version is greater than 3.23 then if you are using that syntax.

                 $new = $db->Execute("SELECT * FROM {$tables['category']['name']} WHERE DATE_ADDED >= CURDATE() - INTERVAL 15 DAY");

                should return everything added in the last 15 days.

                  It's weird, I tried that, but still nothing. Should INTERVAL be within a function? or does it not matter?

                    It matters which version of mysql you are using, so try this

                     $new = $db->Execute("SELECT * FROM {$tables['category']['name']} WHERE DATE_ADDED >= DATE_SUB(CURDATE(), INTERVAL 15 DAY)");

                    Personally I've never used the shorthand syntax, just habit as I started with early versions of mysql - pre 3.23

                      Write a Reply...