Hi Guys,

I'm trying to write out a table of data using a mysql query with the restraint of the data being for "this week" based on sunday as the cutoff.

For some reason the mysql isn't evaluating correctly, can someone give me any pointers as to what I'm doing wrong? I've used print_r to look at the vals of the strtotime variables, including an actual value from my database, and the numbers given make sense - ie: my db val is between the two sundays, but it doesn't display the data.

I've tried it with 'BETWEEN' as well, but dropped back to the below for testing just one condition, which is the weirdest part, originally I was having it display nothing when putting the 'less than next sunday' constraint, now it seems to be the other way around (but surely I'm dreaming that!?).

Code is below, thanks,
JM

$date_1 = strtotime("last sunday");
$date_2 = strtotime("sunday");
$link1 = "SELECT * FROM progress WHERE ipg_project_num=$ipg_project_num AND date<$date_2 AND date>'$date_1'";

    By having the single quotes around the variable $date_1 you are turning it into a string, therefore your trying to get a date between an int and a string. Clearly that wont work

      rincewind456 wrote:

      By having the single quotes around the variable $date_1 you are turning it into a string, therefore your trying to get a date between an int and a string. Clearly that wont work

      Hi Mate, thanks for the reply.

      Yeah sorry I cut and pasted the code after I'd been fiddling for a while, you're right that obviously makes it a string and doesn't work (actually makes it display all the time) but without them it doesn't work anyway.

      When I get a sec later I'll post some more info on what the variables come back as etc.

      JM

        Ok got those values now.

        This is what the values equate to from the strtotime vals, and from this you would expect that the sample date from my database would be displayed. (I've put then in order of numbers highest to lowest to show $date_3 fits between the constraint dates)

        $date_1 = strtotime("last sunday");
$date_3 = strtotime("2007-11-26");
$date_2 = strtotime("sunday");

        1195909200 : $date_1 
1195995600 : $date_3
1196514000 : $date_2

        Since it's not, I wonder whether you can't compare it this way - ie: is mysql trying to compare them properly, it's one thing to say when I strtotime all three values my database date is in the middle of the two date ranges ($date_1 and $date_2) but I guess unless mysql is automatically doing a strtotime on my DB date then it's not going to calculate it properly?

          Thinking about this I guess I answered my own question!

          It can't compare a date in the DB in the format yyyy-mm-dd properly with a value derived from stringtotime (or even when that strtotime value is converted to yyy-mm-dd via the date() function, tried that then)

          So instead, I've made the DB field accept normal strings and will change all dates to unix timestamps using strtotime on them before writing to the db, then they can be compared with the 'sunday' variables I've made and displayed correctly.

          Hopefully this helps someone else anyway.

            I would never recommend storing dates/datetimes as anything but those types (okay, maybe an integer column if you really want to use Unix timestamps).

            MySQL can do so many date comparisons if you use the correct column type. If, for example, you still wanted to do your "last sunday" type of comparisons, you could easily compare them:

            SELECT foo FROM myTable WHERE UNIX_TIMESTAMP(date_column) >= $date_1

            Either way, don't forget to mark this thread resolved.

              I was trying to do that actually, but wasn't using UNIX_TIMESTAMP() so it didn't work properly (from memory I was trying to put strtodate in the query by inserting php in the middle of it.

              Would be much better as you say. I'll test that out thanks!

                Works perfectly, thanks brad

                  Write a Reply...