Cron Job for subscriptions setup help

Said that I don't know how Difference function works, I don't see any syntax error in your code. You should test it by creating a test group in your table with two test member with your email address and two different subscription dates, one which implies the script sends an email and the other which doesn't require email sending. Then you can see if email has been sent passing the test group id to your query in the cron script.

Thank you Robert! I'm trying to test this to see if it's working correctly. I've changed the SQL statement slightly so that it calls from the package id rather than the group id:

$query = mysql_query("SELECT full_name, email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.full_name = s.uname AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ");

Select allOpen in new window

I've put a test entry in the database that I thought should match this but it is returning no results and tried echoing the result on the screen:

echo $query ;

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/clear555/public_html/subscribe_reminder.php on line 12

What should I change the test entry date to in order to see that the SQL statement works?
subscribe.jpg

Can you try this query directly in the database? It should show you the result that date returns, it might be 0 or 2.

SELECT full_name, email, DATEDIFF(NOW(), s.cus_timestamp)
FROM phpfox_user AS u, phpfox_subscribe_purchase AS s
WHERE u.full_name = s.uname AND s.package_id= '2'

Select allOpen in new window

Maybe the query should be changed for example take the difference in hours and check that it's between x times 24 and (x+1) times 24 (for x days old).

Hi Derek.

First, let me highlight what Robert said: in php the single = sign means assignement, so $value = 1 assigns to the variable $value the value of 1. To check if two values are equal you must use two equal signs: if ($value1 == $value2).

Second, when you're developing it's better run queries with a die statement which can tell you what is wrong:

$query = "SELECT full_name, email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.full_name = s.uname AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ";
$result = mysql_query($query) or die ("Error on query " . $query . PHP_EOL . "Error was: " mysql_error());

Select allOpen in new window

This way you'll get a lot of info to debug your queries :-)

Third. In your query you're selecting package_id 2 which in your table has the datetime set to 0000-00-00 00:00:00. Perhaps set a datetime value could help.

Anyway, I suggesto you to run the query using my code and post here (or analyze) the error message you get and check if the query is what you expect it to be.

Cheers
Marco

Hi guys,

Thanks for the info. I've tried both of your queries in MYSQL ADMIN but it doesn't like the syntax. When I add your code, Marco, to the file it gives the error:

Parse error: syntax error, unexpected T_STRING in /home/clear555/public_html/subscribe_reminder.php on line 11

Select allOpen in new window

which is the start of the query line.

Thanks,

D

You could also try your original query but ending in "= 0" instead of "= 1" just for testing. What I meant was the difference in days between two timestamps on the same day may get rounded down to 0 for example.

Hi Robert,

Yes, it would be good if it wasn't rounded off to zero like this as we will need to make sure that it sends these emails correctly. Here is some code we wrote for another script that rounds some of this, would it be possible for you to assist me in adapting this for this script so that it correctly identifies the correct users to send this email to?

It's for a 30 day trial membership programme and after 30 days the user is automatically moved to a different user group so the number of days will never get above this.

Thanks so much,

D

 function Difference($created_time)
    {
        $str = strtotime($created_time);
        $today = strtotime(date('Y-m-d H:i:s'));
        
        // It returns the time difference in Seconds...
        $time_differnce = $today-$str;
        
        // To Calculate the time difference in Years...
        $years = 60*60*24*365;
        
        // To Calculate the time difference in Months...
        $months = 60*60*24*30;
        
        // To Calculate the time difference in Days...
        $days = 60*60*24;
        
        // To Calculate the time difference in Hours...
        $hours = 60*60;
        
        // To Calculate the time difference in Minutes...
        $minutes = 60;

        if(intval($time_differnce/$years) > 1)
        {
            return (int)($time_differnce/$years * 365);
        }else if(intval($time_differnce/$years) > 0)
        {
            return (int)($time_differnce/$years * 365);
        }else if(intval($time_differnce/$months) > 1)
        {
            return (int)(($time_differnce/$months) * 30);
        }else if(intval(($time_differnce/$months)) > 0)
        {
            return (int)(($time_differnce/$months) * 30);
        }else if(intval(($time_differnce/$days)) > 1)
        {
            return intval(($time_differnce/$days));
        }else if (intval(($time_differnce/$days)) > 0) 
        {
            return intval(($time_differnce/$days));
        }else if (intval(($time_differnce/$hours)) > 1) 
        {
            return 0;
        }else if (intval(($time_differnce/$hours)) > 0) 
        {
            return 0;
        }else if (intval(($time_differnce/$minutes)) > 1) 
        {
            return 0;
        }else if (intval(($time_differnce/$minutes)) > 0) 
        {
            return 0;
        }else if (intval(($time_differnce)) > 1) 
        {
            return 0;
        }else
        {
            return 0;
        }
    }

Select allOpen in new window

Sorry for ignoring your specific request for the moment but using that function would mean going back to the original method of retrieving all data and checking in php. It's ok if you want to, but it would not be my preference and I think I thought of a more logical way to query this. To see what I mean try this query in the admin interface:

SELECT full_name, email, now(), s.cus_timestamp, DATE_ADD(s.cus_timestamp, INTERVAL 25 DAY), DATE_ADD(s.cus_timestamp, INTERVAL 26 DAY)
FROM phpfox_user AS u, phpfox_subscribe_purchase AS s
WHERE u.full_name = s.uname AND s.package_id= '2';

Select allOpen in new window

And to check in the code itself use this:

$query = "SELECT full_name, email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.full_name = s.uname AND s.package_id= '2' AND NOW() > DATE_ADD(s.cus_timestamp, INTERVAL 25 DAY) AND NOW() < DATE_ADD(s.cus_timestamp, INTERVAL 26 DAY) ";

Select allOpen in new window

This then all assuming it runs smoothly every day at the same time. Earlier remarks about 'keeping score' still apply since you can never be 100% sure this will never skip a record.

Thanks Robert, I'll test this and good advice about the extra table to check this too. I will setup this as well.

Sorry, my bad. I was taking this from another query and should have been using the user_id in both tables to check to make sure they are the same.

I have the query updated to this now and pretty sure the fields are correct now:

$query = "SELECT user_id, email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ";
$result = mysql_query($query) or die ("Error on query " . $query . PHP_EOL . "Error was: " mysql_error());

echo $result ;

Select allOpen in new window

It's still giving an error though.

Parse error: syntax error, unexpected T_STRING in /home/clear555/public_html/subscribe_reminder.php on line 11 (the second line of the code here)

What should I query with now to test this based on the user_id correct information I have provided?

Thanks again,

D

First things first: you're missing a dot:

"Error was: " mysql_error()

Select allOpen in new window

should be

"Error was: " . mysql_error()

Select allOpen in new window

Thanks Robert,

It's now giving the error:

Error on query SELECT user_id, email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 Error was: Column 'user_id' in field list is ambiguous

Select allOpen in new window

Try prefixing the column names: SELECT u.user_id, u.email FROM ...

Thanks, it's now printing this and giving this error:

Resource id #2
Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in /home/clear555/public_html/subscribe_reminder.php on line 15

Select allOpen in new window

I think you're mixing thing up now. Marco earlier gave you some code which separated $query and $result. You have to use the latter now.

Hi Derek.
About your post ID: 38821116 you can't use

echo $result since $result is the result of a query and has to be fetched correctly.
Please, post here the actual code you're using, so we can do the point of the situation (italian expression: I don't know the english one).

Cheers

Thank you Marco, sorry if I'm confusing you guys with it.

Here is the code I currently have on the page now:

<?php

//Connect to DB

mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

//takes trial users from DB

$query = "SELECT u.user_id, u.email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ";
$result = mysql_query($query) or die ("Error on query " . $query . PHP_EOL . "Error was: " . mysql_error());

echo $result ;

while ( $user = mysql_fetch_array($query) )
{
	// Grabbing the username
	$username = $user["user_id"];
	
	echo 'found one: '.$username.'<br>'.PHP_EOL;

	// Grabbing the email address of the user
	$email = $user["email"];

	//Split username into first name
	$pieces = explode(" ", $username );

	// Preparing the e-mail
	$to = $email;
	$subject = "$pieces[0], It's time to Exercise";
	$message = "Greetings $pieces[0], \n\n As you'd asked us before, we're reminding your subscription is almost up. Visit here to upgrade visiting \n\n http://www.clear.com/exerstats/";
	$headers = "From: noreply@clear.com";
	
	// Sending the e-mail
	mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
}

?>

Select allOpen in new window

I'm not so aknowledged in MySql, but since your problem is now that your query doesn't work I would try to use a 'for dummies' query like this:

$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s
  WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ";

Hi Marcus, when I try to run this in the PHPMYADMIN it gives the error:

import.php: Missing parameter: import_type
import.php: Missing parameter: format

Are you running it from the SQL window of phpMyAdmin? I don't know what the program wants, but it seems you're using IMPORT instead of SQL.

I would run it from a script...

Thanks Marco, I have it working in PHPMYADMIN now. It gives an empty recordset though when I run it. I've entered fields with a timestamp from all of the last 4 days (as it should check with a day difference of 1 in our example) but it keeps returning an empty recordset.

Try to not use DATEDIFF and see if it returns something

$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s
  WHERE u.user_id = s.user_id AND s.package_id= '2' ";

So we'll know if the problem is there or in the first part of the query. When I have this kind of problems, I start by the simplest query which runs and I go up step by step to identify the problem.

Yes, this works and returns values :)

Well, I now tell you what I would do at this point: I would prepare a little script to run the query and print the exact query as it is passed to mysql_query function

<?php
$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s 
  WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ";
echo $query."<br>";
$result = mysql_query($query) or die(mysql_error());
while ( $user = mysql_fetch_array($result) )
{
	// Grabbing the username
	echo $user["user_id"] . "<br>";
}
?>

Select allOpen in new window

In the last code you posted of your cron script I noticed now a big error: you use

$result = mysql_query($query) or die(mysql_error());
while ( $user = mysql_fetch_array($query) )

instead of

$result = mysql_query($query) or die(mysql_error());
while ( $user = mysql_fetch_array($result) )

so I suggest to fix this immediately, then prepare the script above and run it and post here all it's printed on the screen.

Cheers

Thanks mate,

here is what I have on the page now:

<?php

//Connect to DB

mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

//takes trial users from DB

$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s 
  WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 ";
echo $query."<br>";
$result = mysql_query($query) or die(mysql_error());
while ( $user = mysql_fetch_array($result) )
{
	// Grabbing the username
	echo $user["user_id"] . "<br>";
}
?>

Select allOpen in new window

but all it printed on the page was:

SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 1 

Select allOpen in new window

I see in the screenshot you posted above that the cus_timestamp of package_id is either 0000-00-00 00:00:00 or 2012-01-23 03:00:00: in both cases the DATEDIFF function would not return 1 so the quetion is: have you updated the table?

Hi Marcus, here is what is in the table at the moment and I have not updated it since we had the query working on the DB:

Here now is 2013/01/26, so 26 - 24 = 2...

When I tried the timestamp with 3 I got a return value:

SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.user_id = s.user_id AND s.package_id= '2' AND DATEDIFF(NOW(), s.cus_timestamp) = 3 
40

Select allOpen in new window

Which record has been returned?

but when I try the timestamp with a value of 4, I get no result. No result with a value of 1 or 2 either.

record 40

Let me know if you want me to put any other test data in the database for you.

I'll try to understand what happens here...

Wow, I see I'm a bit late to the party but one important thing: at some point you went back to

AND DATEDIFF(NOW(), s.cus_timestamp) = 1

Select allOpen in new window

after I suggested an alternative to cover a day around 25 days since subscription:

AND NOW() > DATE_ADD(s.cus_timestamp, INTERVAL 25 DAY) AND NOW() < DATE_ADD(s.cus_timestamp, INTERVAL 26 DAY)

Select allOpen in new window

At that point I used a date of january 1st 2013 in my db and got the expected results.

You should consider reporting any MySQL errors to your email.

Thank you both! Robert I tried your SQL change but it was returning the same record value when I changed the timestamp number from 2 to 3 so I'm not sure what's going on with that.

Marcus, I've tested it with the change you suggested for the package id and its working great now. Just one question with this, If I run the cron script every 24 hours can I be sure this way that the system will only find this record once (thus only sending 1 email to the user) ?

Robert, I really like your suggestion about setting up a table that records the sent emails. I would also like the script to send the admin a report showing how many emails were sent that day. What would be the best way to go about doing this?

Thank you,

Derek

Well, I can't ensure nothing, I didn't see anything, I was not here and if I were I slew ;-)

I  don't see any reason why the script should send more than one email, but some time ago I set up a newsletter system for one my site and I had to contac the provider to stop the flood of email sent all over the world: now I test my scripts one time, then another time, then another time again: do it you too! Create a table with only two or three records, with three your email addresses and change accordingly the script, then... try it! Maybe you can run the script once per hour, so you have a reasonable time to test without spend days to doing it.
This is the only serious thing I can suggest you, even if the expert were Mr. Php himself!

For the sent emails report, simply create a table like 'sent_emails' and add to your cron script the query tom insert a record everu time an email is sent. Last, set up a little script to sent a report from that table a makew it run with cron let's say every week or so... For more details, open a new question: it's not a trwo minutes work ;-)

Cheers
Marco

Cheers

Thank you so much Marco, I'll do the testing this way for sure. So is the rest of the script I had before correct? I will then open a new question about the table part if I'm having trouble but this part should be easy enough.

Here is what I have in the entire script now:

<?php

//Connect to DB

mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

//takes trial users from DB

$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s 
  WHERE u.user_id = s.user_id AND s.package_id= 2 AND DATEDIFF(NOW(), s.cus_timestamp) = 2 ";
echo $query."<br>";
$result = mysql_query($query) or die(mysql_error());
while ( $user = mysql_fetch_array($result) )
{
	// Grabbing the username
	echo $user["user_id"] . "<br>";
}

while ( $user = mysql_fetch_array($query) )
{
	// Grabbing the username
	$username = $user["user_id"];
	
	echo 'found one: '.$username.'<br>'.PHP_EOL;

	// Grabbing the email address of the user
	$email = $user["email"];

	//Split username into first name
	$pieces = explode(" ", $username );

	// Preparing the e-mail
	$to = $email;
	$subject = "$pieces[0], It's time to Exercise";
	$message = "Greetings $pieces[0], \n\n As you'd asked us before, we're reminding your subscription is almost up. Visit here to upgrade visiting \n\n http://www.clear.com/exerstats/";
	$headers = "From: noreply@clear.com";
	
	// Sending the e-mail
	mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
}
?>

Select allOpen in new window

We're almost finished this project now and excited about it. I'm not sure how familiar you are with it but there is a javacript function I'm having some trouble with and wonder if you could have a look at it, no worries if it doesn't suit you.

https://www.experts-exchange.com/questions/28009982/Interval-measurement-incorrect-on-javascript-function.html


My Italian roommate is leaving Canada in a couple of days and we're having a house party for him tonight :)

The script is ok, but delete or comment any echo:

<?php

//Connect to DB

mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

//takes trial users from DB

$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase AS s 
  WHERE u.user_id = s.user_id AND s.package_id= 2 AND DATEDIFF(NOW(), s.cus_timestamp) = 2 ";
//echo $query."<br>";
$result = mysql_query($query) or die(mysql_error());
//while ( $user = mysql_fetch_array($result) )
//{
	// Grabbing the username
//	echo $user["user_id"] . "<br>";
//}

while ( $user = mysql_fetch_array($query) )
{
	// Grabbing the username
	$username = $user["user_id"];
	
//	echo 'found one: '.$username.'<br>'.PHP_EOL;

	// Grabbing the email address of the user
	$email = $user["email"];

	//Split username into first name
	$pieces = explode(" ", $username );

	// Preparing the e-mail
	$to = $email;
	$subject = "$pieces[0], It's time to Exercise";
	$message = "Greetings $pieces[0], \n\n As you'd asked us before, we're reminding your subscription is almost up. Visit here to upgrade visiting \n\n http://www.clear.com/exerstats/";
	$headers = "From: noreply@clear.com";
	
	// Sending the e-mail
	mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
}
?>
                                            

Select allOpen in new window

I wish you best of luck with this project: it's beautyful to be young and I hope you have success with this job.

I'll take a look at your javascript as soon a s possible.

Finally... don't drink too mutch! ;-)

Good party!!!

Thank you very much :)

Just one other quick question about this script if it's okay. I have setup a test table for it and here"s what I have now:

<?php

//Connect to DB

mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

//takes trial users from DB

$query = "SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase_test AS s 
  WHERE u.user_id = s.user_id AND s.package_id= 2 AND DATEDIFF(NOW(), s.cus_timestamp) = 2 ";
echo $query."<br>";
$result = mysql_query($query) or die(mysql_error());
while ( $user = mysql_fetch_array($result) )
{
	// Grabbing the username
	echo $user["user_id"] . "<br>";
}

while ( $user = mysql_fetch_array($result) )
{
	// Grabbing the username
	$username = $user["user_id"];
	
	echo 'found one: '.$username.'<br>'.PHP_EOL;

	// Grabbing the email address of the user
	$email = $user["email"];

	//Split username into first name
	$pieces = explode(" ", $username );

	// Preparing the e-mail
	$to = $email;
	$subject = "$pieces[0], It's time to Exercise";
	$message = "Greetings $pieces[0], \n\n As you'd asked us before, we're reminding your subscription is almost up. Visit here to upgrade visiting \n\n http://www.clear.com/exerstats/";
	$headers = "From: noreply@clear.com";
	
	// Sending the e-mail
	mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
}
?>

Select allOpen in new window

I have it finding one value from the DB (prints this on the screen:

SELECT u.user_id, u.email, s.user_id, s.package_id, s.cus_timestamp FROM phpfox_user AS u, phpfox_subscribe_purchase_test AS s WHERE u.user_id = s.user_id AND s.package_id= 2 AND DATEDIFF(NOW(), s.cus_timestamp) = 2
1

but it does not send any email to the user?

I can open a new question about this if you like?

Thanks,

Derek

Delete the first while loop, that's been left over from testing it seems.

That's it!!! Thank you Robert!

Hey guys,

I wonder would either of you have any experience with export the contents of a search query  to a pdf document? I am working on it here but have no experience with their syntax so am struggling with it :)

No worries if not :)

https://www.experts-exchange.com/questions/28010887/PHP-if-statement-as-one-variable.html

Thanks,

Derek

Not really my area of expertise but @hielo's answer seems to be ok.

Your own first post on that question says: "you can see the $text variable here..."

That is what he did: make a $text variable that you can print to the PDF. Put his code before the PDF code. Maybe the html has to be taken out, that is what Ray was hinting at, you need 2 versions of the same code. There may be a better way. Then again, maybe printing the html completely to the PDF is what works.

By the way, if I understand it correctly, julianH did the same. His way could actually save you work: what you should be able to do is make a 'print to pdf' page that includes your original page. It may take some fiddling but that could be a nice solution (if the pdf writer takes full html as input, which is what I've worked with in .NET myself)...

Thank you Robert, I will continue to test both of their solutions.

I'm having an issue with another cron script and I think it's the date difference function the same as last time that is causing it. Would you mind taking a look at it?

https://www.experts-exchange.com/questions/28011676/Cron-Job-PHP-int-Difference-issue.html