UPDATE/SET - cronjob, stored procedure? Question

[Hitch54]

Hi: 

 

I hope you'll bear with me through this post, I really need the help.  I use MySql 5.0 and PHP5. 

 

I have a table that needs updating. I use a classifieds cms that doesn't provide a mechanism to allow users to pick a time for their ad to run during ad creation, only a mechanism to extend the ad near the expiration date.  So, I have created a field with a drop-down selection box.  I want to run a MySql query (UPDATE, SET) to perform this function automatically in a cronjob.  That's the background. 

 

Now, these queries, individually, work:

 

UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 7 DAY) WHERE col_34 = 'Extend 7 days...$5.00'; 
UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 14 DAY) WHERE col_34 = 'Extend 14 days...$10.00'; 
UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 21 DAY) WHERE col_34 = 'Extend 21 days...$15.00';

 

I also need to wipe col_34 clean of any data (which I don't know how to do at all) AFTER these queries have been performed, which I would need to do several times a day up to hourly. So, I tried to create a cronjob which looked like this:

 

<?php 
$con = mysql_connect("localuser","username","pwd"); 
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 

mysql_query("UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 7 DAY) WHERE col_34 = 'Extend 7 days...$5.00'"); 
mysql_query("UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 14 DAY) WHERE col_34 = 'Extend 14 days...$10.00'"); 
mysql_query("UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 21 DAY) WHERE col_34 = 'Extend 21 days...$15.00'"); 


mysql_close($con); 
?> 

 

This did not work. The cronjob executes,it connects to the db okay, but nothing occurs whatsoever. So then, with the help of a friend with a lot more MySql expertise than I, we tried to construct a procedure which looked like this:

 

DROP PROCEDURE if exists `Procedurename` $$ 
CREATE PROCEDURE `Procedurename` () 
BEGIN 
DECLARE error BOOLEAN DEFAULT FALSE; 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE; 
UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 7 DAY) WHERE col_34 = 'Extend 7 days...$5.00'; 
UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 14 DAY) WHERE col_34 = 'Extend 14 days...$10.00'; 
UPDATE noah_item SET expirationtime=DATE_ADD(expirationtime, INTERVAL 21 DAY) WHERE col_34 = 'Extend 21 days...$15.00'; 
IF error THEN 
SELECT 'UPDATE failed'; 
END IF; 
END 

 

Which I then tried to call from within a cronjob, which looked like this:

 

<?php 
$con = mysql_connect("localhost","user","password"); 
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 

mysql_select_db("yourdatabase", $con); 

mysql_query("CALL Procedurename()"); 

mysql_close($con); 
?> 

 

Which, unhappily, ALSO did not work.

 

So, other than manually running these queries each every hour, and manually deleting the contents of col_34 every hour, I have reached the end of my ability to figure this out. If anyone could point out to me what I'm doing wrong, I would be grateful. I run other cronjobs (to a different db on the same server), so the initial connection isn't the problem; it seems to be that while the cronjob connects to the db, nothing happens at that point; no error is returned, nada.

 

Any pointers on how to add a query to delete the contents of col_34 as well, after the contents of expirationtime are updated, would be very much appreciated.

 

Thank you for your patience in reading this to the end.

 

Hitch54

 

[BioBob]

Its possible its blowing up in the query itself for some reason.  Situations like that where you cant figure out WHY its not working, I try to stick with php's OR statements as much as possible.

 

mysql_query($some_query) OR die("there was an error in the query:\n$some_query\nMySql Said: " . mysql_error() . mysql_errno() );

 

Alternatively, if you dont want to kill the script, you could try error_log(mysql_error()) also.

 

Alternatively, yet again, mysql will return FALSE if the query didnt run or there was an error, so you can also do something like:

 

$some_query = "UPDATE something SET something = 'something' WHERE some_id = '$real_id' LIMIT 1";

if (!mysql_query($some_query))

  {

  error_log("Some Error Text" . mysql_error());

  }

 

Its pretty simple logic, but what you gotta do is write your code so no matter what, you can figure out what happened.  90% of programming is setting up your code so no matter what happens in your code, you have a way to find out what its doing and why its doing what it is doing.