example:

SELECT * FROM `table`
WHERE `date_time`> date_add(NOW() , INTERVAL -30 DAY)

records in 30day.

    thank you so much! I will try this right away!

      unfortunately that didn't seem to work...

      In other words I see events that are much older than 1 month...

      is that comma supposed to be there? I don't see it in any of the examples on the mysql documentation page...

      My query right now:

        $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "webcal_entry.cal_date < DATE_ADD(NOW(), INTERVAL -30 DAY)";

        then try to change the relation, as you see i changed the example...

        jjozsi

          I am not sure what you mean by changing the relation...

              $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "webcal_entry.cal_date > DATE_ADD(NOW(), INTERVAL -30 DAY)";

            i mean the ( > ) greater then mark should be used.

              of I see... I had not noticed that...
              I will give this a try right now

                in the Mysql manual you will find at DATE_ADD() function, where you can add or substract two dates. LEts try the examples on that page, its very useful.

                jjozsi

                  Still doesn't work...
                  I looked at the examples and it seems that all examples have date formats with dashes or something... my date format is 20090706... do I need to convert it first?

                    oh, i see.
                    mysql can handle strings as dates if you add the correct format.

                    STR_TO_DATE(`date_field`,'%Y%m%d') AS datefield

                      So the correct MySQL syntax would be the following?

                        $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') AS datefield AND ".
    "datefield > DATE_ADD(NOW() INTERVAL -30 DAY)";

                      or do I need to use STR_TO_DATE with SELECT like this?

                        $sql = "SELECT webcal_entry.cal_id, webcal_entry.cal_name " .
    ", webcal_entry.cal_priority, webcal_entry.cal_date " .
    ", webcal_entry.cal_time " .
    ", webcal_entry_user.cal_status, webcal_entry.cal_create_by " .
    ", webcal_entry.cal_access, webcal_entry.cal_duration " .
    ", webcal_entry.cal_description " .
    ", webcal_entry_user.cal_category " .
    ", STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') AS datefield " .
    "FROM webcal_entry, webcal_entry_user ";

  $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "datefield > DATE_ADD(NOW() INTERVAL -30 DAY)";

                        the second option should be tested in phpmyadmin

                        is there a reason you build the queries in several rows concated with
                        ".............".
                        "............."; ?

                        its allowed to place enters in you query.

                          I tried running the following query in PHPMyAdmin:

                          SELECT webcal_entry.cal_id, webcal_entry.cal_name, webcal_entry.cal_priority, webcal_entry.cal_date, webcal_entry.cal_time, webcal_entry_user.cal_status, webcal_entry.cal_create_by, webcal_entry.cal_access, webcal_entry.cal_duration, webcal_entry.cal_description, webcal_entry_user.cal_category, STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') AS datefield FROM webcal_entry, webcal_entry_user WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry_user.cal_login = 'Marc' AND datefield > DATE_ADD(NOW() INTERVAL -30 DAY)

                          and I got the following error:

                          #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERVAL -30 DAY)
                          LIMIT 0, 30' at line 1

                          The I tried the same query with the comma that you had originally inserted after NOW() and the error became:

                          #1054 - Unknown column 'datefield' in 'where clause'

                          Because I don't know MySQL syntax I didn't know whether what I did was allowed (using datefiled as a variable)... so I am really confused and don't know how to go about this.

                          To answer your question, I didn't write this script, I am just making this modification, so I am just going by how the original developer coded it, that's all.

                            SELECT webcal_entry.cal_id, webcal_entry.cal_name, webcal_entry.cal_priority, webcal_entry.cal_date, webcal_entry.cal_time, webcal_entry_user.cal_status, webcal_entry.cal_create_by, webcal_entry.cal_access, webcal_entry.cal_duration, webcal_entry.cal_description, webcal_entry_user.cal_category FROM webcal_entry, webcal_entry_user WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry_user.cal_login = 'Marc' 
AND STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') > DATE_ADD(NOW() , INTERVAL -30 DAY)

                            I couldn't test this version, the comma needed between the NOW() (current date-time) and the interval keyword.

                              That didn't work either, however I had an idea... it might not be the most correct way to approach the problem but it could avoid me dealing with MySQL syntax...
                              Since the database holds dates as strings in the form of yyyymmdd, I figured the higher the number the more recent the date, so I could just write the query like this:

                                $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "webcal_entry.cal_date > '" .date('Y').'0'.(date('n')-1).date('d'). "'";
    // I use 'n' which is month digit without leading zero because if I used the double digit 'm' modifier once I take -1 off the zero would disappear anyway

                              What do you think?
                              it seems to work (I don't see events older than june 9th today, which is July 8th) but I don't see events in sept so I am wondering what the deal is with that...

                                string and date format has differences.

                                use the date to string converter first, as i suggested,
                                or migrate the existing date field into new date formatted one.

                                read the manual about the date formats.

                                if you can't coop with this issue, send the mysql dump with xample data,
                                and i can send you a tested sql query for the records in 1 month interval.

                                  I am aware that string and date are different but if the date is saved as a string (or I just assume it is) I should be able to handle it as a string...
                                  I can't alter the structure of the dB.
                                  If you tell me how to get you a mysql dump I'll be glad to send it to you.You want me to simply export some newly created data and export the structure as well? Is that what you mean
                                  Thanks for offering to help

                                    yes, dump is an export from your database.
                                    In phpmyadmin you can make it.

                                      6 days later

                                      Sorry it took me a while, I was away on business.
                                      Here's the dump (attached) that you requested with some data in it. Let me know if you need more data. Thanks for helping me out and being willing to look into this.

                                        22 days later

                                        Hello, I never heard back from anyone and I was wondering if djjjozsi was able to take a look at my attachment.
                                        Thanks so much