3

As part of a data import I have created a PHP file that runs on a webserver / localhost to input data into a database from a CSV source file. The problem is this page needs to be opened manually so in essence is just like running the actual MySQL script behind it.

How can I add this file to run at a specific day everyday on the server without "Human" interaction.

I tried some of the other solutions such as: Starting PHP in Task Schedular and then running it with the argument -f C:\Apache24\htdocs\my_phpfile.php

However, this did not seem to execute the PHP file because when looking at the info in the table it was not updated. I know the PHP file works as I have run this manually through a browser.

Please could I get some advice / help? What is the best way to do this?

I am currently testing the environment on my windows 10 laptop, but ultimately this will run on a Windows 2012 Server.

Thanks.

Improve this question
2
  • 1
    FreeBSD / Linux user here. No idea about Windows and I have no idea about how your PHP is coded, but your approach is correct if you can run PHP from the command line. Alternatively if you can "call" a web page from the command line using something like curl, wget or fetch then you could schedule that command instead. Commented Jul 28, 2016 at 11:00
  • in unix systems you can do cronjobs, maybe you can aswell in windows? with AT command Commented Jul 28, 2016 at 11:03

3 Answers 3

Reset to default
2

You can run php scripts in Task Scheduler (I do it a lot) but be aware of the following points:

You run it as php C:\Apache24\htdocs\my_phpfile.php

PHP must be in your PATH, else you will need to path directly to it:

c:\php\php C:\Apache24\htdocs\my_phpfile.php

Running it like this is running it CLI mode, not Web. When you hit the script in Apache the environment is quite different from CLI. Your script may not be running as you expect due to the difference in environment. e.g.

  • the current-directory is different,
  • there are no $_SERVER variables,
  • the $_ENV will be different,
  • there is no .htaccess,
  • the user will be different and therefore the file-access permissions
  • the loaded modules may be different
  • the PHP.ini may even be different

To test it in CLI, simply open a PowerShell, or a CMD prompt and enter the same command-line you're putting into WTS. You may see some output that informs you of the problem.

Look in your php_errors.log to see why the Task-Scheduler run instance failed.

If you cannot get the script working in CLI mode and must run it in Web mode, you can hit the web script using wget, curl or similar, or even using Internet Explorer's command line options.

Or use http://SetCronJob.com or similar service.

There are a lot of options here - the "best" in my mind is the simplest (KISS principe): running PHP in CLI directly. There's less to go wrong, you're not booting Apache just to load PHP (a massive waste), you're not relying on a network connection, you're not relying on 3rd party (paid) web-services, you're not at risk of IE being updated! WTS is also reliable (on Windows Server anyway).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for this... Which method would you recommend as the most, call it "stable" solution for lack of a better term? I have actually managed to get it running a *.bat file and then it returned the expected results.
See updated answer. Yes wrapping in a .bat is common practice. It helps to encapsulate the command-line for portability, and could first CD into the right place for example. Then you just have to call the bat, with no params in WTS (I find it's interface fiddly for paths and parameters!).
My .bat file is working great. I now added a mail feature, and had to hack the PHPMailer a bit for authentication purposes. But again I am experiencing the same problems as initially. The mail only gets sent if I use the web interface, i.e. chrome. but when running the bat file the mail isn't sent. However, the data is updated.
2

You may want to try create task with schtasks utility to create Windows cron job with cmd or powershell (https://msdn.microsoft.com/en-us/library/windows/desktop/bb736357(v=vs.85).aspx). Here is example that will create task with name task_name running every 5 minutes:

schtasks /Create /TN task_name /SC /MO 5 c:\php\php.exe d:\path\to\php_script.php
Improve this answer

2 Comments

Is this a replacement for at?
@scipilot I suppose schtasks was designed as replacement for at - it has more improved syntax and powers, including flexible schedule for task.
1

The *.bat file did the trick. and kicking it off through Task Scheduler. Initially I had the bat file pointing to the incorrect directory which is why some features weren't working. My next step, is to clean my code, and beautify the mail using base64 and html.

php C:\Apache_Directory\htdocs\my_phpscript.php

My Script... And yes, I know it's not very clean / perfect, it still has a lot of work, but for now it's doing what I need it to do:

 <?php

$sql1 = "USE database_name";

$sql2 = "TRUNCATE TABLE my_table";

$sql3 = "LOAD DATA LOCAL INFILE 'DataImport//import_file.csv' 
REPLACE INTO TABLE my_table
CHARACTER SET latin1
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(`record_number`
, `module_name`
, `action_date`
, `location`
, `type_of_outlet`
, `user_name`
, `store_code`
, `outlet_name`
, `line_question`
, `line_field_id`
, `line_value`
, `brand_code`
, `brand`);";

/* $sql4 = "SELECT COUNT(creation_time) FROM my_table
WHERE DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)"; */


$con=mysqli_connect("localhost","root","mysqlpassword","database");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result1 = mysqli_query($con, $sql1);
$result2 = mysqli_query($con, $sql2);
$result3 = mysqli_query($con, $sql3);

if (mysqli_affected_rows($con) > 1) {
  $message ="". mysqli_affected_rows($con). " rows were successfully added to the my_table table!";
} else {
  $message = "" . mysqli_error($con). "has caused the update to fail";
};

echo $message;

 // To send HTML mail, the Content-type header must be set
require("PHPMailerAutoload.php"); // path to the PHPMailerAutoload.php file.
require("class.phpmailer.php"); 
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

// Additional headers
$headers .= 'To: Name1 <[email protected]>, Name2<[email protected]>' . "\r\n";
$headers .= 'From: Report Server <[email protected]>' . "\r\n";
$subject = 'Data Import Resultt' ."\r\n";

   $mail = new PHPMailer();
   $mail->IsSMTP(true);
   $mail->Mailer = "smtp";
   $mail->Host = "smtp.host.co.za";
   $mail->Port = 587;                       // 8025, 587 and 25 can also be used. Use Port 465 for SSL.
   $mail->SMTPAuth = true;
   $mail->SMTPSecure = 'tls';
   $mail->Username = "[email protected]";
   $mail->Password = "password";
   $mail->CharSet = "UTF-8";
   $mail->SMTPOptions = array(              // Bypass security verification on e-mail. WARNING: When using this method, 
    'ssl' => array(
        'verify_peer' => false,
        'verify_peer_name' => false,
        'allow_self_signed' => true
    )
);


   $mail->From     = '[email protected]';
   $mail->FromName = 'Reports Server';
   $mail->AddAddress('[email protected]', 'Name1');
   $mail->AddAddress('[email protected]', 'Name2');
   $mail->AddAddress('[email protected]', 'Name3');   







   $mail->Subject  = $subject;
   $mail->Body     = $message."\r\n";
   $mail->WordWrap = 50;  

   if(!$mail->Send()) {
        echo 'Message was not sent.';
        echo 'Mailer error: ' . $mail->ErrorInfo;
        exit;
   } else {
        echo 'Message has been sent.';
   }





mysqli_close($con); 

?>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.