Jump to content

Splitting up data from one field in MYSQL


downah

Recommended Posts

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?

Link to comment
Share on other sites

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*

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

<?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

Link to comment
Share on other sites

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']);

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.