here's where is get a bit tricky. does this app simply spit out the table and then you deal with the data directly or does the app constatly insert/update the table? if the app simply spits it out once and then you could run the code i posted before which updates all of the dates in one shot. you can then just deal with the data using the new DATE field. if the app is constanly adding new records then you could use this revised code:
$result = mysql_query('SELECT invoice_id, TimeCreated FROM invoice WHERE date_created IS NULL');
while ($row = mysql_fetch_assoc($result))
{
$mysql_date = date('Y-m-d', strtotime($row['TimeCreated']));
$update = mysql_query("UPDATE invoice SET date_created = '$mysql_date' WHERE invoice_id = '" . $row['invoice_id'] . "'");
if ($update) {echo 'UPDATE OK<br>';} else {echo 'UPDATE FAIL<br>';}
}
this now only pulls the records where date_created is NULL, meaning only the new records where the new date has not been added. for this to work properly you would have to set you date_created field to default to NULL (which you should always do). you then would just run this query whenever you add records. the first time you run this is will take a bit of time if the table is big but after that as i said it will only update to newly added records.
the tricky part is if your app updates existing records then the above code won't work, worse yet, the date_created and TimeCreated would no longer match on the updated records. if your app updates existing records then you should use the original code example.
the whole point of this is to do the conversion once rather than have to do them on all of you SELECT queries. make sense?
