Solved

Cron Job for subscriptions setup help

Posted on 2013-01-25
50
506 Views
Last Modified: 2013-01-28
Hi everyone,

I would like to setup a little cron job on our server that checks how many days a user has left on their trial subscription and sends them out an email with 5 days left to go on it.

I have setup cron jobs in the past and have a lot of this done already so if someone could go through it and make any necessary changes to it that would be greatly appreciated. I’m slowly learning PHP but I’m still a ways off :)

Here's the bones of what I have so far, the db fields are correct:

//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 = mysql_query("SELECT * FROM phpfox_user WHERE  user_group_id= '11' ");


while ( $user = mysql_fetch_array($query) )
		{
			// Grabbing the username
			$username = $user["full_name"];
			
			// Grabbing the email address of the uesr
			$email = $user["email"];

//Checks DB for the date the user subscribed 
$query2 = mysql_query("SELECT cus_timestamp FROM phpfox_subscribe_purchase WHERE uname = '$username' ");


//How many days left on user subscription
$usersub = (int)Difference($query2);

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

if ( $usersub= 5 )
			{
				// 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!");
			}

Open in new window

0
Comment
Question by:TLN_CANADA
  • 24
  • 14
  • 11
  • +1
50 Comments
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38817896
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.
0
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 200 total points
ID: 38818155
Be careful with this, it is an error (although not syntactical):
if ( $usersub= 5 )

Open in new window

Apart from that, wouldn't it be easier to select only those records from the database (in 1 query) that you need instead of checking all users in php code?
<?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 = mysql_query("SELECT full_name, email FROM phpfox_user AS u, phpfox_subscribe_purchase AS s WHERE u.full_name = s.uname AND u.user_group_id= '11' AND DATEDIFF(NOW(), s.cus_timestamp) = 5 ");

while ( $user = mysql_fetch_array($query) )
{
	// Grabbing the username
	$username = $user["full_name"];
	
	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!");
}

Open in new window


One more thought in general is that you may want to make sure that you can run it again manually if it fails for some reason, especially since you let it die on an error in the loop. Maybe just a table/field of your own that contains a boolean or date sent regarding this mail linked to the user.
0
 

Author Comment

by:TLN_CANADA
ID: 38820220
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 ");

Open 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
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38820254
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'

Open 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).
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38820284
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());

Open 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
0
 

Author Comment

by:TLN_CANADA
ID: 38820624
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

Open in new window


which is the start of the query line.

Thanks,

D
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38820701
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38820726
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;
        }
    }

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38820904
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';

Open 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) ";

Open 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.
0
 

Author Comment

by:TLN_CANADA
ID: 38820909
Thanks Robert, I'll test this and good advice about the extra table to check this too. I will setup this as well.
0
 

Author Comment

by:TLN_CANADA
ID: 38821116
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 ;

Open 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
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38821126
First things first: you're missing a dot:
"Error was: " mysql_error()

Open in new window

should be
"Error was: " . mysql_error()

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 38821133
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

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38821153
Try prefixing the column names: SELECT u.user_id, u.email FROM ...
0
 

Author Comment

by:TLN_CANADA
ID: 38821196
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

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38821222
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.
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821450
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
0
 

Author Comment

by:TLN_CANADA
ID: 38821550
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!");
}

?>

Open in new window

0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821585
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 ";
0
 

Author Comment

by:TLN_CANADA
ID: 38821607
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821616
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...
0
 

Author Comment

by:TLN_CANADA
ID: 38821659
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.
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821676
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38821691
Yes, this works and returns values :)
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821709
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>";
}
?>

Open 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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:TLN_CANADA
ID: 38821728
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>";
}
?>

Open 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 

Open in new window

0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821733
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?
0
 

Author Comment

by:TLN_CANADA
ID: 38821739
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:

Sub Table
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821747
Here now is 2013/01/26, so 26 - 24 = 2...
0
 

Author Comment

by:TLN_CANADA
ID: 38821756
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

Open in new window

0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821760
Which record has been returned?
0
 

Author Comment

by:TLN_CANADA
ID: 38821764
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38821770
record 40
0
 

Author Comment

by:TLN_CANADA
ID: 38821771
Let me know if you want me to put any other test data in the database for you.
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38821895
I'll try to understand what happens here...
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38821955
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

Open 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)

Open in new window

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

Expert Comment

by:Mohamed Abowarda
ID: 38821957
You should consider reporting any MySQL errors to your email.
0
 
LVL 30

Accepted Solution

by:
Marco Gasi earned 300 total points
ID: 38822328
I did several tests, recreating two table in my environment and the query runs correctly for each time interval I tried.

The only thing I can think to is that you must avoid to use quotes for package_id value: in your query you have

AND s.package_id = '2'

well, try

AND s.package_id = 2

I did this and I had all expected results.

Cheers
0
 

Author Comment

by:TLN_CANADA
ID: 38822764
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38822811
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
0
 

Author Comment

by:TLN_CANADA
ID: 38822836
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!");
}
?>

Open 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.

http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28009982.html


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

Expert Comment

by:Marco Gasi
ID: 38822859
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!");
}
?>
                                            

Open 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!!!
0
 

Author Comment

by:TLN_CANADA
ID: 38822953
Thank you very much :)
0
 

Author Comment

by:TLN_CANADA
ID: 38823010
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!");
}
?>

Open 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
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38823062
Delete the first while loop, that's been left over from testing it seems.
0
 

Author Comment

by:TLN_CANADA
ID: 38823068
That's it!!! Thank you Robert!
0
 

Author Comment

by:TLN_CANADA
ID: 38828218
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 :)

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28010887.html

Thanks,

Derek
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38828341
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.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38828405
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)...
0
 

Author Comment

by:TLN_CANADA
ID: 38828507
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?

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28011676.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now