foreach with mysql UPDATE

[fuzzy1]

Hey All,

Having a little trouble with a foreach update. I need to split a datetime field (date_entered)
into a date field(date_start) and a time field (time_start) on an ongoing basis.

I've got them split into an array, but can't figure out how to pass the date_start & time_start values back into the record in the appropriate columns. Probably going about it all wrong from the start😕

Heres: what I've got - (which currently updates only 1 record at a time)

$check_start =" SELECT * FROM emails WHERE date_start IS NULL AND time_start IS NULL ";
$result = @mysql_query ($check_start)or die ("Query Failed Selecting Start Dates & Times "); // Run the query. 
	$numresults=mysql_query($check_start); 
	$numrows=mysql_num_rows($numresults); 
	echo $numrows .'<br>';
	if ($numrows != 0)
	{// convert $check_start results to array -$addStarts[] 
	while ($row = mysql_fetch_assoc($result)) {
	$_id = $row['id'];
	$date_entered=$row['date_entered'];
	$_split_date = preg_split ("/[\ ,]+/", $date_entered);
	$start_date = $_split_date[0];
	$start_time = $_split_date[1];
	$addStarts[] = "$_id,$start_date,$start_time"; 
	}
		foreach($addStarts as $key => $value)
		{
//	echo $value .'<br>';
		$result=@mysql_query("UPDATE emails SET date_start = '$start_date', time_start='$start_time' WHERE id='$_id'")
		 or die ("Query Failed Updating Email Start Dates & Times "); // Run the query. 
		$affected_rows = mysql_affected_rows (); 
		echo '<br> Updated ' . $affected_rows .' Email Start Dates & Times';
		}
	}

[NogDog]

First question: is it too late to modify the table structure so that you just have one datetime_start field of type DATETIME? (I can't think of a reason to keep the separate, myself.)

Secondly, I expect the loop on the first query results is unnecessary: you should be able to do something like the following, where I'm assuming you've merged the start date/time into one field:

$startDateTime = date('Y-m-d H:i:s', strtotime($date_entered));
$query = "UPDATE emails SET start_datetime = '$startDateTime' WHERE start_datetime IS NULL";
$result = mysql_query($query) or die(mysql_error());

(This is assuming that $date_entered will be in a format that strtotime() recognizes. If not, further modifications will be needed to make it so.)

[fuzzy1]

Hey NogDog,

• Yes, it's pretty much way too late to change the table structure. I'm working with SugarCRM 's contact management architecture. Thousands of Archived email, (inbound, sent, campaigns etc.) all of which receive a datetime entry (formated 'Y-m-d H:i:s') however its hit & miss for those records which receive the accompanying and separate date and time entries AND deciphering just how when and (most importantly) WHY those entries are -- or ARE NOT made-- promises to be a huge undertaking in and of itself - never mind CHANGING it all now.

• So it follows that - No, the datetime field (date_entered) is not the result of concatenating start date and time.
  Start date & time are null, and I'm trying to derive their values from date_entered (which is never null) and insert them.

While I REALLY LIKE SugarCRM, it has 2 major and widely documented and shortcomings:
1.Email Handling

2. Date Time Handling.
So here I am.

The bug I am trying to hack my way around lies in that Sugar's Email module (and numerous sub-panels) look to the columns: start_date, and start_time to then (sadly) CONCATENATE the two for out-put as Date Sent & Date Received in a list_view format, meaning that should an email record's date and time fields be NULL, the user cannot tell from the list view what date/time the email was sent or received (nor sort by it neither so... grrr)

If I get your general intent here, I'm thinking your suggesting something like:

$startDate = date('Y-m-d', strtotime($date_entered)); 
$startTime = date('H:i:s', strtotime($date_entered)); 
$query = "UPDATE emails SET time_start = '$StartTime', date_start = $StartDate"; //FOREGOING THE WHERE IS NULL 
$result = mysql_query($query) or die(mysql_error()); 

-- though I don't have any idea if strtotime works in reverse (timetostr?) and STRINGS I've got -- via preg_splt -- but I still need a loop to retrieve and so define $date_entered for each record, and another to update each record with the data derived from the first. No?

[fuzzy1]

I think I got it???

$check_start =" SELECT * FROM emails WHERE date_start IS NULL AND time_start IS NULL ";
$result = @mysql_query ($check_start)or die ("Query Failed Selecting Start Dates & Times "); // Run the query. 
	$numresults=mysql_query($check_start); 
	$numrows=mysql_num_rows($numresults); 
// If we have no results, die
	if ($numrows != 0)
		{
			while ($row = mysql_fetch_assoc($result)) {
			$_id = $row['id'];
			$date_entered=$row['date_entered'];
			$_split_date = preg_split ("/[\ ,]+/", $date_entered);
			$start_date = $_split_date[0];
			$start_time = $_split_date[1];

		$set_start = "UPDATE emails SET date_start = '$start_date', time_start='$start_time' WHERE id='$_id'";
		$_result = @mysql_query ($set_start)or die ("Query Failed Setting Start Dates & Times "); // Run the query. 
	}
}

[NogDog]

Ahh...I misread things first time around, thinking that $date_entered was coming from some sort of form input. I still think you could do it in one query, however, letting the SQL handle everything:

$query = "UPDATE emails SET date_start = DATE(date_entered), time_start = TIME(date_entered) WHERE date_start IS NULL AND time_start IS NULL";