Splitting up data from one field in MYSQL

[downah]

Basically I have a date of birth field (date) now how would I go about splitting that date field into 3 variables $year $month and $day?

 

Any small push forward is much appreciated, I have searched but it seems that I might not be putting it into words correctly.

 

Actually while writing this I suppose I might of thought of the solution

<?php

$year = date('Y', strtotime($row['dob']));
$month = date('m', strtotime($row['dob']));
$day = date('d', strtotime($row['dob']));
?>

 

Would that be how you would go about it?

[PFMaBiSmAd]

list($year,$month,$day) = explode('-',$row['dob']);

[AyKay47]

I would do this in the query:

 

select day(date_field) as day, monthname(date_field) as month, year(date_field) as year from table

 

then using php you could do something like this.

 

$query = mysql_query("select day(date_field) as day, monthname(date_field) as month, year(date_field) as year from table");
while($row = mysql_fetch_assoc($query))
{
    $day = $row['day'];
    $month = $row['month'];
    $year = $row['year'];
}

 

*pseudo code*

[downah]

Thanks a lot both of you, I used the list as it seemed very easy and straightforward, works perfect too, although I am trying to get the month to show as date(M) so it shows May instead of 05 -

 

<?php
list($year,$monthM,$day) = explode('-',$row['dob']);
               	  $month = date('M', $monthM);

?>

as soon as I added

 

$month = date('M', $monthM);

 

Whatever month I choose it turns back 01 or Jan, what am I doing wrong?

[Jessica]

date() accepts a unix timestamp.

 

$ts = str_to_time($row['dob']);

$month = date('M', $ts);

 

etc for year and day.

 

edit: The way you originally had it was fine, what's the problem?

[Andy-H]

date() accepts a unix timestamp.

 

$ts = str_to_time($row['dob']);

$month = date('M', $ts);

 

etc for year and day.

 

edit: The way you originally had it was fine, what's the problem?

 

 

It's strtotime not str_to_time

 

//edit

 

Unless that's an alias I am not aware of

[downah]

<?php

 

$year = date('Y', strtotime($row['dob']));

$month = date('m', strtotime($row['dob']));

$day = date('d', strtotime($row['dob']));

?>

 

You mean this jesi? I hadn't actually tried it yet just thought of it while I was writing the topic

[downah]

Haha just tryed it out works perfect, didn't think it would, I solved my own topic in post 1 

[PFMaBiSmAd]

strtotime/date are two fairly slow php functions. Using them to format a date typically takes 8 times longer than doing this in your query.

 

If your query contains the following -

 

SELECT DATE_FORMAT(dob,'%Y-%M-%d') as formatted_date

 

You can use the following php code to get the year, monthname, and day -

 

$list($year,$monthname,$day) = explode('-',$row['formatted_date']);

[Jessica]

date() accepts a unix timestamp.

 

$ts = str_to_time($row['dob']);

$month = date('M', $ts);

 

etc for year and day.

 

edit: The way you originally had it was fine, what's the problem?

 

 

It's strtotime not str_to_time

 

//edit

 

Unless that's an alias I am not aware of

Yep, I don't test the code before I post it I don't think str_to_time is valid, you're right.

[AyKay47]

this should be done in the query itself for optimization, you should either use my suggestion or format the date correctly like PFM suggested.