brad115

Thank you so much! This works perfectly!!! Thanks again Pikachu2000 and everyone else that assisted me!!

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) <>"" )

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

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 ?>

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.

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.

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) <>"" )"

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..

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.

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!

Hi, I am trying to create a cron job that truncates a database table, then inserts data back into the table, The cron jobs works fine, if i just truncate the table, but does not seem to work if i want to re-insert data back in the table. truncate.php looks as follows: <?php require_once('/usr/www/users/amisea/sosams/db-connecter.php'); $query = "TRUNCATE TABLE 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); ?>