So I explained in the last quickie how to do a cronjob, why, and that I’ll explain how to make such files to be cronjobbed.
Lets imagine this scenario:
You have a config file in some root folder that holds database credentials and now you want to send daily emails to your users about your website’s statistics.
For the sake of this scenario, lets assume we have two tables: Users and Stats.
Users table is formed as the following:
* Username as varchar(255)
* Email as varchar(255)
And Statistics table is formed like this:
* Timestamp as varchar(255)
* IP as varchar(255)
* Target as varchar(255)
This isn’t anywhere close to real tables because in real life scenarios, you’ll most likely use much more fields than just those. That, and datatypes aren’t appropriate too.
Alright, we’re going to do the following to make the file:
1) Require the config file
2) Connect to database
3) Loop around tables to send emails
4) I’ll sit back and you guys make me a sammich? Just trying my luck 😛
So, here we go:
<?php require '../config.php'; ?>
Remember, we put the config file in a root folder thats inaccessible by web requests.
Time to connect to database. To do so, just go with mysql_connect() and mysql_select_db() to select your database.
For the sake of this example, let’s assume that your variables in config.php are as the following:
$database_name: The database name that has your tables
$database_username: Username to access the database
$database_password: Password to access the database
In so many cases, the database server is your server’s localhost. But if it’s not, just replace ‘localhost’ with ‘your_server’s_address’.
The snippet below simply connects or prints an error if it couldn’t.
<?php
require '../config.php';
$connect = mysql_connect('localhost', $database_username, $database_password);
if ($connect)
{
mysql_select_db($database_name, $connect);
}
else
die("Couldn't connect to database!");
Alright, lets start querying and sending emails!
<?php
require '../config.php';
$connect = mysql_connect('localhost', $database_username, $database_password);
if ($connect)
{
mysql_select_db($database_name, $connect);
$users_query = "SELECT * FROM USERS";
$users_result = mysql_query($users_query) or die(mysql_error());
$stats_query = "SELECT * FROM STATISTICS";
$stats_result = mysql_query($stats_query) or die(mysql_error());
while($users_row = mysql_fetch_array($users_result))
{
$email_body = "";
$email_title = "Status for " . $users_row['Username'];
$email_address = $users_row['Email'];
while($stats_row = mysql_fetch_array($stats_result))
{
$email_body = $email_body . $stats_row['Timestamp'] . " - " . $stats_row['IP'] . " - " . $stats_row['Target'] . "\n";
}
}
mail($email_address,$email_title,$email_body, "From: your@email.com");
mysql_close($connect);
}
else
die("Couldn't connect to database!");
Too long? Let me explain in these easy steps:
1) You query to retrieve users and statistics
2) You while-loop each user retrieved from the users query
3) For each user you’re looping, you loop statistics query result
4) You just keep on appending the email body and adding statistic entries line by line and end it with “\n” which is a new line. As in, the [ENTER] button of some sort.
5) When you’re done looping in statistics query result, you just send the email to the user
6) When you’re done, you just close the connection and the whole thing is over
7) Make me a sammich, maybe?
To be honest, I haven’t tested what I wrote up there but I can safely assume it’ll run just fine. Let me know if it didn’t to see why and fix it.
Now, apply the cronjob quickie to this file and you got yourself a daily statistics email update mechanism.
Too long? Well, here’s a dirty joke to make it up for you:
*Two geek couple getting in the mood*
Girl: Baby, I’ll give you the best CronJob you’ll ever get in your entire life
No? Not funny? OK 🙁
Stay tuned for more PHP quickies 😀
References:
mysql_connect()
mysql_select_db()
mysql_query()
mysql_error()
mysql_fetch_array()
mail()
mysql_close()