Separating string int o new fields?

sks1024

I need a little help with how to separate a string and place data in new fields.

I have a db field with the date in a string i.e. 04/2/2013

I want to somehow separate this string and put the month, the day and the year in their own new separate fields.

Is there a quick script I can run on the database to accomplish this?

Thanks

laserlight

Hmm... on one hand, this could be considered breaking up a multi-valued field into its constituents. On the other hand, a date is a date, so storing it in a single field makes sense, and you might even use a specific DATE data type, if applicable. Why do you want to do this? If it is purely for display reasons, then an appropriate approach is to do the splitting in PHP.

sks1024

You are correct. It is purely for display purposes. I want to display only the month and the day, but have the month display as the 3 digit letters instead of the numbers. i.e. Mar 25 instead on 03 25

laserlight

sks1024 wrote:
I want to display only the month and the day, but have the month display as the 3 digit letters instead of the numbers. i.e. Mar 25 instead on 03 25

One option is to use [man]strtotime[/man] combined with [man]date[/man]. Another option, if you change to store the date using a type that your database engine provides for, is to do the formatting in the query itself.

bradgrafelman

I would suggest doing two things:

Updating the database such that the date is stored in a DATE column. (That is, after all, what that column type does best - store dates.)
Don't let display formats dictate storage formats. For example, there's nothing wrong with using MySQL's (I'm assuming you're using MySQL - other DBMSs probably provide something similar) DATE_FORMAT() function rather than SELECT'ing the DATE column itself.

EDIT: Just to clarify... my preference explained here corresponds to the "[other] option" laserlight mentioned above.

sks1024

Ended up doing this.

$db_date = "04/2/2013";

$new_dates = explode("/", $db_date);

$new_month = $new_dates[0];

echo $new_month;

Was easier than changing db around.

Thanks,
Stan

laserlight

sks1024 wrote:

Ended up doing this.

$db_date = "04/2/2013";

$new_dates = explode("/", $db_date);

$new_month = $new_dates[0];

echo $new_month;

However, that does not "have the month display as the 3 digit letters instead of the numbers". In retrospect, I see that there is an even simpler option than what I suggested in post #4, i.e., DateTime::createFromFormat:

<?php
$db_date = '04/2/2013';

echo DateTime::createFromFormat('m/j/Y', $db_date)->format('M j');

sks1024

Since they are all 2 digit months in the db, I did a switch statement:

	switch ($new_dates[0]) {
        case 01:
        	$new_month = "Jan";
        break;

case 02:
    	$new_month = "Feb";
    break;
//etc, etc

}

laserlight

You could do that, but I am pointing out to you ways to improve your code. If you would rather write your own code instead of using facilities already available, go ahead, but you are just re-inventing the wheel. Besides, your code probably has a bug: it looks like you are using integer literals instead of string literals in the switch, so I presume you used 08 instead of '08'. Trouble is, 08 is an invalid integer literal in octal, which is what the leading 0 means it is.

bradgrafelman

laserlight;11025993 wrote:
so I presume you used 08 instead of '08'. Trouble is, 08 is an invalid integer literal in octal, which is what the leading 0 means it is.

Same trouble applies to 09 as well.

Weedpacket

sks1024 wrote:
Was easier than changing db around.

But it's surprising how often the "easier" approach turns out to involve more work in the long run. If you have a leaking pipe it's "easier" to put a bucket under it than to fix the pipe.