Update a column

[rondog]

Hey guys..my mind is like mush right now and cant think of how to do this..

 

I have a table and a column with a date that looks like: 4/25/10

 

basically I need to take that date and do a strtotime() on it and update it..so rather than 4/25/10 I need it to be the strtotime version. Any help? thanks!

 

 

edit::the entire column by the way, not just 1 row.

[PFMaBiSmAd]

You can use the mysql STR_TO_DATE() function in a query to convert any date format into an actual mysql DATE value.

[rondog]

ok well my next question then is is mysql able to determine something like

 

$today = "4/21/2010";

 

SELECT * FROM table WHERE date_column >= '$today'  ?

 

 

I always just convert it using strtotime so its a number and then I format it with date() when I display it.

[PFMaBiSmAd]

The reason why the mysql (and other database) dates are in the YYYY-MM-DD format is because you can do greater-than/less-than comparisons and sorting on dates in that format because the fields making up the date are left-to-right, most significant digit (year) to least significant digit (day.)

[rondog]

ok that is good to know. What should I do now to go about getting them into that format. I am in PHP my admin right now, but I cant find where to run the STR_TO_DATE() function

[PFMaBiSmAd]

1) Alter your table and add a DATE data type column.

2) Execute an UPDATE query to populate the new column from your existing column using the STR_TO_DATE() function.

3) After you are sure the new column has the correct data and you have changed any existing queries to work with the new column, delete the old column. You can use the mysql DATE_FORMAT() function to retrieve a DATE data type in any format you need.

 

Ref: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[rondog]

ok I'm up to the part where I need to execute the update..that was kind of my original question...how to do an update on all the rows for a column

[rondog]

I am trying this, but it doesnt seem to be working. The newdate column still says 0000-00-00

$q = mysql_query("SELECT id,date FROM de_events");
while ($result = mysql_fetch_array($q))
{
$query = mysql_query("UPDATE de_events SET newdate = STR_TO_DATE('".$result['date']."','%Y-%m-%d') WHERE id = '".$result['id']."'");
}

[mrMarcus]

echo your query to make it is contains proper values.

[rondog]

echo your query to make it is contains proper values.

 

They look ok to me..?

UPDATE de_events SET newdate = STR_TO_DATE('4/17/2010','%Y-%m-%d') WHERE id = '1'
UPDATE de_events SET newdate = STR_TO_DATE('4/17/2010','%Y-%m-%d') WHERE id = '2'
UPDATE de_events SET newdate = STR_TO_DATE('4/21/2010','%Y-%m-%d') WHERE id = '3'
UPDATE de_events SET newdate = STR_TO_DATE('4/22/2010','%Y-%m-%d') WHERE id = '4'
......

[PFMaBiSmAd]

The format specifier '%Y-%m-%d' does not match the starting format of 4/17/2010.

 

Also, if you are updating all the rows, you don't need a WHERE clause in the query and you don't need to select anything or loop over anything. Just execute the UPDATE query directly against the database.

[rondog]

The format specifier '%Y-%m-%d' does not match the starting format of 4/17/2010.

 

Also, if you are updating all the rows, you don't need a WHERE clause in the query and you don't need to select anything or loop over anything. Just execute the UPDATE query directly against the database.

 

Ahh that worked! I was thinking the  '%Y-%m-%d' was the format I wanted it input as. Thanks so much!

[rondog]

hey one more question. Now when I SELECT the new date column, how can I get it back in the format of dd/mm/yyyy?

 

I tried:

$today = date("Y-m-d");

"SELECT * FROM de_events WHERE date >= DATE_FORMAT('$today','%d/%m/%Y')";

 

and it isnt working

 

[rondog]

I kinda figured it out, but not sure if im doing it the right way

 

$sql = "SELECT *,DATE_FORMAT(date,'%m/%d/%Y') FROM de_events WHERE date >= '$today'";

 

The thing is, its returning the column name as DATE_FORMAT(date,'%m/%d/%Y'), although I get both versions of the date, the yyyy-mm-dd version and the m/d/Y version.

[rondog]

ok I did:

$sql = "SELECT *,DATE_FORMAT(date,'%m/%d/%Y') AS date FROM de_events WHERE date >= '$today'";

 

and its returning the formatted date as the date column...this'll work for now, but if anyone has a better way or the correct way to do it, please let me know, thanks.