query for date range from date to day of week?

[orionblue]

Hi all,

I have a notification script I want to run but only for members who sign up during a date range. The script works fine, until I try to 'automate' it using that date range. I'm using mySQL and the date added (v_submitted) col is 'DATE' type yyyy-mm-dd, auto filled with NOW() upon submission of the form.

example - the newsletter goes out on thursday every week. If you sign up between Monday of the previous week or the Sunday (prior) of that week, you get that newsletter. If you sign up later than Sunday, you get NEXT thursday's newsletter.

The logic of this seems to be (I think):

"select * from table where date of thursday minus date record added is between 4 days and 10 days" - for the first part, showing this week's new members... and:

"select * from table where date of thursday minus date record added is greater than 4" - for the second part, showing next week's new members.

SO - what I have is this (incomplete) query:

$thisweek = date("Y-m-d" , (strtotime($v_submitted) - (60*60*24*4  /*  number of days in seconds */)));
  $result = mysql_query(sprintf("SELECT * FROM a_mails WHERE  $thisweek between 4 and 10"));
  while (list($v_id, $v_name, $v_email, $v_submitted) = mysql_fetch_row($result)) { 

I know it's not quite there - I'm at a loss. Can someone please point me towards the solution here? I think I may need to determine the yyyy-mm-dd of Thursday, but that seems like a pretty complicated thing to do for what I want to acheive...

thanks for any suggestions.

[LordShryku]

Okay, let's use this coming Thursday as the example...

<? 
$thurs = "2003-10-09";
/* current thursday will usually be generated by date("Y-m-d");, but we're setting it static for the example
*/
$sunday = date("Y-m-d", strtotime($thurs."-4 days"));
$monday = date("Y-m-d", strtotime($thurs."-10 days"));
$sql = "SELECT * 
        FROM a_mails 
        WHERE  dateSubmitted 
            BETWEEN '".$monday."' AND '".$sunday."'";
$result = mysql_query($sql);

HTH

[orionblue]

thanks for the example, how am I so bad at logic?

I'm getting a mysql_fetch_row() error now but I can figure that out later, I have two things I'm still wondering -

How do I generate a non-static thursday date, and is '-4 days' as in:
$sunday = date("Y-m-d", strtotime($thurs."-4 days"));

an actual, um, code? Or was that part of the example...?

thanks again, you don't know how much it helps to have a concrete example like that, just when you're about to give up and start all over, thinking it's not possible.

[LordShryku]

Well, the code above works(at least for me); I was assuming you would just be running this on Thursdays, so to generate it...

//instead of 
$thurs = "2003-10-09"; 
//generate the current date witth
$thurs = date("Y-m-d");

strtotime is really powerful. It has the ability to take almost any string and formulate a date from it. So saying

$sunday = date("Y-m-d", strtotime($thurs."-4 days")); 

It's taking the timestamp of your thursday variable, actually subtracting 4 days, then formatting the result with date(), in the Y-m-d format.

Should all work...give it a shot.

[orionblue]

If I use the following query, I get an 'mysql_num_rows(): supplied argument is not a valid MySQL result resource ' error - however, if I remove the WHERE clause all rows are displayed correctly. What did I miss this time? BTW - I'm not ignoring the 'Thursday' date thing - i just haven't figured out the best way to make that happen yet, where it needs to pick a date depending on a calendar...

Sooo..... Cloooooose.....

$thurs = "2003-10-09"; 
/* current thursday will usually be generated by date("Y-m-d");, but we're setting it static for the example 
*/ 
$sunday = date("Y-m-d", strtotime($thurs."-4 days")); 
$monday = date("Y-m-d", strtotime($thurs."-10 days")); 
$sql = "SELECT v_id, v_name, v_email, v_submitted FROM a_mails 
        WHERE  $v_submitted 
            BETWEEN '".$monday."' AND '".$sunday."'"; 
$result = mysql_query($sql);

$num=mysql_num_rows($result);

mysql_close();

echo "results:";

$i=0;
while ($i < $num) {

$v_id=mysql_result($result,$i,"v_id");
$v_name=mysql_result($result,$i,"v_name");
$v_email=mysql_result($result,$i,"v_email");
$v_submitted=mysql_result($result,$i,"v_submitted");

echo "<strong>$v_name</strong><br>Email: $v_email<br>Submitted on: $v_submitted";

++$i;
}

😕 Please help if you have time! Again, I really appreciate the guidance.

[LordShryku]

Well, that means there's an error in the SQL. Echo it out to the screen, make sure everything looks okay.

// Change this
$result = mysql_query($sql); 
// To this
$result = mysql_query($sql) OR die(mysql_error());
// to get the mysql error message

[orionblue]

yup, i'm back.

this error is displayed:

You have an error in your SQL syntax near 'BETWEEN 2003-09-28 AND 2003-10-04' at line 2

Neither of those dates is in the table, and when I change any of the $thurs/$sunday variables the date in the error changes accordingly, so it seems that the records are being called but are stopped before displaying. I don't see any errors in the code (as is above, I have not changed the structure of it but for testing) and I don't know what else to do?

[LordShryku]

SELECT v_id, v_name, v_email, v_submitted FROM a_mails  

WHERE  $v_submitted  

BETWEEN '".$monday."' AND '".$sunday."'"; 

Where is $v_submitted coming from? Did you echo the SQL to the screen? If so, post it.

[orionblue]

Hi, thanks for the quick response.

$v_submitted is inserted as NOW() when a new user signs up via a form on the site. It's coming from the col named 'v_submitted' in the table named 'a_mails'. That part is all working fine.

I did echo the SQL to the screen, that error is what I got. When I remove the WHERE clause, I get all of the records in the database, correctly (well, correctly but not what I want 🙁 ).

Is that what you mean? I'm not sure I've answered your query...

[LordShryku]

No, I meant to echo the actual SQL to the screen

$sql = "SELECT v_id, v_name, v_email, v_submitted 
        FROM a_mails  

        WHERE  $v_submitted  

        BETWEEN '".$monday."' AND '".$sunday."'";  

echo $sql."<br><br>";

[orionblue]

NICE!!!

thanks so much - the error was so stupid, $v_submitted should obviously have been v_submitted. BUT - now the query returns nothing, though there are records in the table that should match.

 SELECT v_id, v_name, v_email, v_submitted FROM a_mails WHERE v_submitted BETWEEN 2003-09-28 AND 2003-10-04

I'm sure I'm trying your patience, but looking at that one more time to you have any warning bells?

[LordShryku]

Only thing I see wrong here is that there aren't single quotes around the dats in your query. Should really look like this..

SELECT v_id, 
       v_name, 
       v_email, 
       v_submitted 
FROM a_mails 
WHERE v_submitted 
BETWEEN '2003-09-28' AND '2003-10-04'

[orionblue]

Oh man, thank you SOOOOO so much, it SUCKS when you are trying to figure out a bigger issue and the problem turns out to be some small stupid syntax error.

You rock, thanks for your help LordShryku. Need a hand w design stuff sometime (my forte!) lemme know!

[LordShryku]

No prob bob 😃