brad115 Posted February 8, 2011 Share Posted February 8, 2011 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! Quote Link to comment Share on other sites More sharing options...
parino_esquilado Posted February 8, 2011 Share Posted February 8, 2011 Create a cron!! LOL Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 I have created a cron job!!! The SQL Commands only work on the first command, not the rest. I am a newbie to PHP. Please help. Quote Link to comment Share on other sites More sharing options...
Maq Posted February 8, 2011 Share Posted February 8, 2011 First of all, that's not PHP code. Second, what do you mean by: The SQL Commands only work on the first command, not the rest. Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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.. Quote Link to comment Share on other sites More sharing options...
Maq Posted February 8, 2011 Share Posted February 8, 2011 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 Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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) <>"" )" Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted February 8, 2011 Share Posted February 8, 2011 If you're getting that output, you're going into PHP. Make sure you haven't deleted the "<?php" at the start of your script. Ken Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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. Quote Link to comment Share on other sites More sharing options...
tjverge Posted February 8, 2011 Share Posted February 8, 2011 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 Quote Link to comment Share on other sites More sharing options...
Maq Posted February 8, 2011 Share Posted February 8, 2011 You need to either change all your inner double quotes to singles or escape them. Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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. Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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 ?> Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 8, 2011 Share Posted February 8, 2011 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.'; } ?> Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 8, 2011 Share Posted February 8, 2011 This really shouldn't be too difficult, but the structure looks like it isn't even close to normalized. Can you post the table structure of the tables, some sample data, and how you're trying to use it? Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 8, 2011 Author Share Posted February 8, 2011 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) <>"" ) Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 8, 2011 Share Posted February 8, 2011 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 ?> Quote Link to comment Share on other sites More sharing options...
brad115 Posted February 9, 2011 Author Share Posted February 9, 2011 Thank you so much! This works perfectly!!! Thanks again Pikachu2000 and everyone else that assisted me!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.