Jump to content

Creating a PHP Cron Job


brad115

Recommended Posts

Hi,

 

I need help please with creating a cron job. I have the following sql commands that need to take place within the cron job. This is all i need to do every couple of hours, thus the reason for a cron job.

 

Code below:

 

DELETE FROM tmpsessionmodule;

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>"" );

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>"" )

 

Can someone please help me!! Any assistance will be greatly appreciated. Thanks!

Link to comment
Share on other sites

Hi,

 

I know that it's not PHP Code.. that's the code i used in mysql. I can get the cron job to work with my code, but it only completes the first task..

 

Code i've used below:

 

<?php
require_once('/home/ffsports/mysql/db-connect.php'); // connect to db
$query = "DELETE FROM tmpsessionmodule;
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>"" )"
$result = @mysql_query($query); // run the query
?>

 

Only the first part works  - $query = "DELETE FROM tmpsessionmodule;

 

Please help! Thanks for responding..

Link to comment
Share on other sites

If the first query is the only one working, then I would assume the other queries are erroneous but being suppressed by the '@' symbol.  Replace that line with:

$result = mysql_query($query) or die(mysql_error()); // run the query

Link to comment
Share on other sites

Hi,

 

Thank you for responding.. Much appreciated. The cron job now at least runs, but the table still doesn't seem to update..

 

Any assistance would be greatly appreciated!! Thanks again for the help thus far.

 

Below is the cron job output:

 

X-Powered-By: PHP/5.2.6-1+lenny9

 

Content-type: text/html

 

 

 

require_once('/home/ffsports/mysql/db-connect.php'); // connect to db

 

$query = "DELETE FROM tmpsessionmodule;

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>"" );

 

INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>"" )"

 

 

 

 

Link to comment
Share on other sites

Hi,

Ok yes, the line was deleted (<?php)

 

I added it back. Now the following error occurs:

 

<br />

<b>Parse error</b>:  syntax error, unexpected T_VARIABLE in <b>/usr/www/users/emisec/sasams/truncate.php</b> on line <b>4</b><br />

 

Line 4: INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>"" );

 

Any suggestions please? Really stuck now.

 

Link to comment
Share on other sites

Try this

<?php
require_once('/home/ffsports/mysql/db-connect.php'); // connect to db
$query = "DELETE FROM tmpsessionmodule;
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>"" )";
$result = @mysql_query($query); // run the query
?>

you where missing a ; on your last insert into

Link to comment
Share on other sites

Hi,

 

Code looks like this now:

 

<?php
require_once('/home/ffsports/mysql/db-connect.php'); // connect to db
$query = "DELETE FROM tmpsessionmodule ;
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>\"\" )"
$result = mysql_query($query) or die(mysql_error()); // run the query
?>

 

I get the following error when running the cron job:

 

X-Powered-By: PHP/5.2.6-1+lenny9

 

Content-type: text/html

 

<br />

<b>Parse error</b>:  syntax error, unexpected T_VARIABLE in <b>/usr/www/users/emisec/sasams/truncate.php</b> on line <b>16</b><br />

 

 

Can anyone please assist? Thank you for all the help thus far.

Link to comment
Share on other sites

Ok, saw TJVerge's reply and added the ;  >  ;) Thank you for that!

 

The cron runs... then output the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';

 

<?php
require_once('/usr/www/users/emisec/sasams/db-connect.php'); // connect to db
$query = "DELETE FROM tmpsessionmodule ;
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>\"\" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>\"\" )" ;
$result = mysql_query($query) or die(mysql_error()); // run the query
?>

Link to comment
Share on other sites

You can't execute multiple queries like that. They need to be executed separately, or the VALUE()s combined into one query string. Obviously I'm unable to test this, but try it and see what you get.

 

<?php
require_once('/home/ffsports/mysql/db-connect.php'); // connect to db
$del_query = "DELETE FROM `tmpsessionmodule`";
if( $del_result = mysql_query($del_query) ) {
$ins_query = "INSERT INTO `tmpsessionmodule` (`SessionNo`, `Module`) VALUES
( SELECT `SessionNo`, `Module`1 FROM `session` WHERE Module1 !='' ),
( SELECT `SessionNo`, `Module`2 FROM `session` WHERE Module2 !='' ),
( SELECT `SessionNo`, `Module`3 FROM `session` WHERE Module3 !='' ),
( SELECT `SessionNo`, `Module`4 FROM `session` WHERE Module4 !='' ),
( SELECT `SessionNo`, `Module`5 FROM `session` WHERE Module5 !='' ),
( SELECT `SessionNo`, `Module`6 FROM `session` WHERE Module6 !='' ),
( SELECT `SessionNo`, `Module`7 FROM `session` WHERE Module7 !='' ),
( SELECT `SessionNo`, `Module`8 FROM `session` WHERE Module8 !='' ),
( SELECT `SessionNo`, `Module`9 FROM `session` WHERE Module9 !='' ),
( SELECT `SessionNo`, `Module`10 FROM `session` WHERE Module10 !='' ),
( SELECT `SessionNo`, `Module`11 FROM `session` WHERE Module11 !='' ),
( SELECT `SessionNo`, `Module`12 FROM `session` WHERE Module12 !='' )";
if( $ins_result = mysql_query($ins_query) ) {
	echo 'Query ran and inserted ' .mysql_affected_rows() . 'records';
} else {
	echo "<br>Query string: $ins_query<br><br>Failed with error: " .mysql_error();
}
} else {
echo "<br>Delete query: $del_query<br><br>Failed with error: " . mysql_error() . '<br>Therefore, insert query not attempted.';
}
?>

Link to comment
Share on other sites

Hi,

Thanks Pikachu2000 for your help. Tried your code as per below, but receive an error.

 

Error is the following:  <br><br>Failed with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `SessionNo`, `Module1` FROM `session` WHERE Module1 !='' ),

 

 

<?php
require_once('/usr/www/users/emisec/sasams/db-connect.php'); // connect to db
$del_query = "DELETE FROM `tmpsessionmodule`";
if( $del_result = mysql_query($del_query) ) {
   $ins_query = "INSERT INTO `tmpsessionmodule` (`SessionNo`, `Module`) VALUES
   ( SELECT `SessionNo`, `Module1` FROM `session` WHERE Module1 !='' ),
   ( SELECT `SessionNo`, `Module2` FROM `session` WHERE Module2 !='' ),
   ( SELECT `SessionNo`, `Module3` FROM `session` WHERE Module3 !='' ),
   ( SELECT `SessionNo`, `Module4` FROM `session` WHERE Module4 !='' ),
   ( SELECT `SessionNo`, `Module5` FROM `session` WHERE Module5 !='' ),
   ( SELECT `SessionNo`, `Module6` FROM `session` WHERE Module6 !='' ),
   ( SELECT `SessionNo`, `Module7` FROM `session` WHERE Module7 !='' ),
   ( SELECT `SessionNo`, `Module8` FROM `session` WHERE Module8 !='' ),
   ( SELECT `SessionNo`, `Module9` FROM `session` WHERE Module9 !='' ),
   ( SELECT `SessionNo`, `Module10` FROM `session` WHERE Module10 !='' ),
   ( SELECT `SessionNo`, `Module11` FROM `session` WHERE Module11 !='' ),
   ( SELECT `SessionNo`, `Module12` FROM `session` WHERE Module12 !='' )";
   if( $ins_result = mysql_query($ins_query) ) {
      echo 'Query ran and inserted ' .mysql_affected_rows() . 'records';
   } else {
      echo "<br>Query string: $ins_query<br><br>Failed with error: " .mysql_error();
   }
} else {
   echo "<br>Delete query: $del_query<br><br>Failed with error: " . mysql_error() . '<br>Therefore, insert query not attempted.';
}
?>

 

Can anyone please assist me? Any help is greatly appreciated. Thanks

Link to comment
Share on other sites

I use the same code in mysql daily and it works fine. the tmpsessionmodule table is purely used for reporting purposes, thus the reason for the table updating.  I've inserted my sql code below.

 

Table Structures below:

 

tmpsessionmodule:

Field       Type

SessionNo int(11)

Module         varchar(255)

 

session:

Field               Type

SessionNo       int(11)

District               varchar(255)

Trainer               int(11)

Venue               int(11)

Caterer               int(11)

DateOfSession  date

Comments       varchar(255)

InsTaught?       varchar(255)

Module1       varchar(255)

Module2       varchar(255)

Module3       varchar(255)

Module4       varchar(255)

Module5       varchar(255)

Module6       varchar(255)

Module7       varchar(255)

Module8       varchar(255)

Module9       varchar(255)

Module10       varchar(255)

Module11       varchar(255)

Module12       varchar(255)

 

DELETE FROM tmpsessionmodule;
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>"" )

 

 

Link to comment
Share on other sites

In MySQL command line client, you can execute multiple queries. In php's mysql_query() function however, you can not. Compound that with the fact that it *appears* you're trying to force a database to mimic a spreadsheet, and it gets even more difficult to make it do what you're wanting it to do. This is about the only thing I can think of to make it work as you intend, while minimizing the number of query executions required. I've tested this locally, and it does what I believe you want it to do, so try it and see if it's what you need.

 

<?php
require_once('/home/ffsports/mysql/db-connect.php'); // connect to db
$del_query = "DELETE FROM `tmpsessionmodule`";
if( $del_result = mysql_query($del_query) ) {
   $values = array();
   for( $i = 1; $i < 13; $i++ ) {
      $result = mysql_query("SELECT `SessionNo`, `Module$i` FROM `session` WHERE Module$i !=''");
      while( $array = mysql_fetch_row($result) ) {
         $values[] = "( '$array[0]', '$array[1]' )";
      }
   }
   $ins_query = "INSERT INTO `tmpsessionmodule` (`SessionNo`, `Module`) VALUES " . implode(', ', $values);
   if( $ins_result = mysql_query($ins_query) ) {
      echo 'Query ran and inserted ' .mysql_affected_rows() . 'records';
   } else {
      echo "<br>Query string: $ins_query<br><br>Failed with error: " .mysql_error();
   }
} else {
   echo "<br>Delete query: $del_query<br><br>Failed with error: " . mysql_error() . '<br>Therefore, insert query not attempted.';
}
// echo $ins_query; uncomment to debug query string
?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.