How to use Cron and Codeigniter/PHP to query a MySQL database?

Question

I'm interested in what the code (command line and php) would look like where every minute or hour you take inventory of the number of item sales obtained from a mysql database and do some action based on that. I'm using CodeIgniter for PHP and I'll call my controller "cronControl".

Here's what I have so far for the command line part (including directory):

:htdocs TimPeterson$ * * * * * php index.php cronControl countSales

Here's the cronControl php part:

class CronControl extends CI_Controller {

  function countSales(){
     $count=$this->db->query("SELECT stuff");
     //do->stuff->based on $count;
     $count="i counted 137 items";          
     file_put_contents("mylogfile.txt", $count);
  }
}

When I type the above command into shell I get:

-bash: 404.php: command not found

It looks like it is evaluating all the php scripts in my root directory (where my 404.php page is) and not just the cronControl/countSales controller. Please note that this shell command works and prints $count to mylogfile.txt if you leave out the 5 asterisks.

Any thoughts on what's going on?

problem solved!!!: the key is when typing the command in the crontab file to include the asterisks, but in the shell to NOT include the asterisks

so in crontab -e:

* * * * * /usr/bin/php /applications/xampp/htdocs/index.php cronControl countSales

whereas in htdocs $

    /usr/bin/php /applications/xampp/htdocs/index.php cronControl countSales

ok, with massive assistance from Dan, this issue is now solved. the key when typing the command is in the crontab file to include the asterisks, but in the shell to NOT include the asterisks so crontab -e: * * * * * /usr/bin/php /applications/xampp/htdocs/index.php cronControl countSales whereas in htdocs $ /usr/bin/php /applications/xampp/htdocs/index.php cronControl countSales thanks everyone for there help! tim — tim peterson, Mar 03 '12 at 19:57

score 1 · Answer 1 · answered Mar 03 '12 at 14:00

1

A quick way to write to a file is to use file_put_contents, such as

file_put_contents("/tmp/mylogfile.txt","Counted $count\n", FILE_APPEND );

answered Mar 03 '12 at 14:00

Phil

851
6
9

thanks Phil, would you also be able to tell me if my command line statement looks correct? – tim peterson Mar 03 '12 at 14:21
Your command line looks correct, but you should test it from a shell before putting into cron by removing the cron frequency specification. (eg: "php index.php cronControl countSales" in your webroot directory) – Phil Mar 03 '12 at 14:29
hi Phil, hmm ok i updated my question with new code based on your suggestions but it still didn't work, please see above, thanks – tim peterson Mar 03 '12 at 14:46
does the cron/ directory exist in your webroot, and is it writeable ? For example, "mkdir /var/www/cron" and "chmod 0777 /var/www/cron". Note that 0777 is writeable by all, you would need to change that to something more restrictive once your software goes into production. – Phil Mar 03 '12 at 14:50
hi Phil, yes the cron/ directory is in my web root and id did chmod 777 to make it and the mylogfile.txt writeable. This didn't change the "no file or directory" error message so think there is something weird going on. – tim peterson Mar 03 '12 at 14:56

score 1 · Answer 2 · answered Mar 03 '12 at 14:27

1

CodeIgniter has a built in logging mechanism for this very purpose.

For example:

log_message('debug', 'Cron count is ' . $count);

See http://codeigniter.com/user_guide/general/errors.html for more information.

Make sure logging is enabled for dubugging (set in your config.php) and that files can be written to the log directory.

answered Mar 03 '12 at 14:27

Dan Murfitt

1,029
2
12
26

thanks Dan, i'm ultimately interested not in logging messages but rather creating invoices based on the database query. The logging was just a simple example to get me going. Sorry about that. Do you have any thoughts on the cronControl itemSales() function above? It's still not working and not really sure what's going on, thanks! – tim peterson Mar 03 '12 at 14:59
Sure. Is the cron actually running (like if you hit the address of the script from within your web browser)? The syntax of your crontab (* 0 * * *) will cause the script to run every day at midnight. To test the script out you would be better off changing it to "* * * * *" for every minute, or hitting the address of the script directly in your web browser. – Dan Murfitt Mar 03 '12 at 15:03
hi Dan, yes it works if run script from web browser but not from the command line. I think it is relevant that i've rewritten my .htaccess so I don't need index.php in the browser. This means I just type "localhost/cronControl/itemSales" and not "localhost/index.php/cronControl/itemSales". Whereas in the command line, when I just do "php cronControl/itemSales" minus the index.php part then it can't find the controller. Though if I do the index.php part it can't find the cron/ directory and file. thoughts? – tim peterson Mar 03 '12 at 15:13
You could try running the cron with WGET instead. http://stackoverflow.com/questions/5766772/using-wget-to-run-a-cronjob-php – Dan Murfitt Mar 03 '12 at 15:19
hi Dan, ok running WGET didn't work either. I think we are dealing with path issues irrespective of the protocol. Can we go back to why * * * * * php .... doesn't work, it looks like it is checking all the php files in my root directory not just the cronControl, is it 5 asteriks or 4? and is there a forward slash in front or not? thanks! – tim peterson Mar 03 '12 at 16:56
It should be 5 elements (see the syntax here http://www.adminschoice.com/crontab-quick-reference). The asterisks just mean run every minute/hour/day/etc. So, 5 asterisks mean every minute of every hour of every day etc. Ok, what if you run the php command directly in the command line? "php index.php cronControl countSales" – Dan Murfitt Mar 03 '12 at 17:14
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/8483/discussion-between-dan-murfitt-and-tim-peterson) – Dan Murfitt Mar 03 '12 at 17:16

score 0 · Accepted Answer · edited Jul 12 '12 at 17:37

0

With massive assistance from Dan, this issue is now solved. the key when typing the command is in the crontab file to include the asterisks, but in the shell to NOT include the asterisks:

crontab -e: * * * * * /usr/bin/php /applications/xampp/htdocs/index.php cronControl countSales

Whereas in htdocs

$ /usr/bin/php /applications/xampp/htdocs/index.php cronControl countSales

edited Jul 12 '12 at 17:37

user229044

232,980
40
330
338

answered Mar 22 '12 at 15:54

tim peterson

23,653
59
177
299

How to use Cron and Codeigniter/PHP to query a MySQL database?

3 Answers3