[SOLVED] UPDATE query only entering a ZERO (0) on update...

[jaxdevil]

I have a problem, its very strange. When my script tries to write back the values I have it does all of them but the datetime string I compile to insert back to the table. It simply does not update the datetime field with this data, and when I switch the value from being updated into the datetime field and instead inserted into a varchar field, it does update, but every time it updates only with the character 0 (zero). Below is my code. Anyone know a reason why a script would write a number 0 (zero) to a table instead of the value you select? I can manually update the table in phpmyadmin with the data, but through the script it just inserts a number zero. Its a very short script below. BTW, I had it echo the variable that it is inserting and it echos as it should, as "2009-11-05 11:59:59", which is perfectly formatted for a datetime field, but in any evnet it should still not insert as a numeral 0 (zero) in a plain varchar field.

 

Any help is GREATLY appreciated! If someone wants it I will give you a month or two of free hosting on my servers, you can use it to host your site or just use it as off-site testing space. It is filled with all the add-ons (imagemagick ,suhosin, imagick, magicwand, zend, everything) I REALLY need to figure this problem out!

 

Thanks in advance guys,

SK

<?php

//move to grab all the entries of events that do not have a day of the weekday listed
$query = "SELECT * FROM mst_events WHERE `weekday`=''";
$result = mysql_query($query);
$n=0;
while ($aEvent = mysql_fetch_array($result)){
			$aEventt[$n]['id'] = $aEvent['id'];
		    $aEventt[$n]['month'] = $aEvent['month'];
		    $aEventt[$n]['day'] = $aEvent['day'];
		    $aEventt[$n]['year'] = $aEvent['year'];
$n++;
}

//now determine the weekday for the given month, day, and year combination
foreach($aEventt as $key => $value) {
$aEventt[$key]['weekday'] = date("l", mktime(0, 0, 0, $aEventt[$key]['month'], $aEventt[$key]['day'], $aEventt[$key]['year']));
}

// now compile a datetime format fro mysql and insert the weekday and the datetime stamp into the table for each line item we just retrieved the values for (all the ones without weekdays listed yet)

foreach($aEventt as $keye => $valuee) {
$day = str_pad($aEventt[$keye]['day'], 2, "0", STR_PAD_LEFT);
$month = str_pad($aEventt[$keye]['month'], 2, "0", STR_PAD_LEFT);
$year = $aEventt[$keye]['year'];
$datenotime = $year."-".$month."-".$day." 11:59:59";
mysql_query("UPDATE mst_events SET `weekday`='".$aEventt[$keye]['weekday']."' AND `datetime`='$datenotime' WHERE id='".$aEventt[$keye]['id']."'") or die(mysql_error());  
}
?>

[PFMaBiSmAd]

SET uses a comma separated list (you have an AND in it now)

 

You can also (probably, it depends on the format of what the existing data is) do everything in a single UPDATE query. It is not necessary to select everything and loop through it.

 

You are apparently setting the weekday field and the datetime field based on existing year/month/day fields, only when the weekday is empty?

[jaxdevil]

That was it. I have been without a day off for too long, its always something small that takes up the bulk of time to figure out. I marked this as solved, if you want a couple of months of free hosting (for PFMaBiSmAd only) message me on here and I will hook you up. You saved me some time this evening thats for certain!

 

Thanks again,

SK