Jump to content
xisto Community
FirefoxRocks

Getting Certain Parts Of A Record The character data

Recommended Posts

Ok I need help on this puzzling problem. At first I thought that this person stored the dates in the MySQL database like this:August 27, 2007That kinda freaked me out a little, because string dates are hard to manipulate. Then I found out that he stored both th string data and numerical date, which I found a little bit odd, but it was like this:2007-08-27I need to build a PHP program to manipulate the data, but I need to access the year, month and day respectively by themselves.I think that isolating the first 4 characters for the year, last 2 characters for day and 6th and 7th characters for month would be the easiest method to do this. Then I could pass the data onto an XML file, in then which I will use XSLT to transform the data. So how do I do that? (I already got the XML/XSLT part, but I need help on the initial problem.)

Share this post


Link to post
Share on other sites

Ok I need help on this puzzling problem. At first I thought that this person stored the dates in the MySQL database like this:
August 27, 2007

That kinda freaked me out a little, because string dates are hard to manipulate. Then I found out that he stored both th string data and numerical date, which I found a little bit odd, but it was like this:

2007-08-27

I need to build a PHP program to manipulate the data, but I need to access the year, month and day respectively by themselves.

I think that isolating the first 4 characters for the year, last 2 characters for day and 6th and 7th characters for month would be the easiest method to do this. Then I could pass the data onto an XML file, in then which I will use XSLT to transform the data. So how do I do that? (I already got the XML/XSLT part, but I need help on the initial problem.)

To do this you can use the substr() php function or directly using the MySql DATE_FORMAT() function:

<?php// using DATE_FORMAT or substr$sql="SELECT DATE_FORMAT(table.col_date,'%d') as day, DATE_FORMAT(table.col_date,'%M') as month, DATE_FORMAT(table.col_date,'%Y') as year, table.col_date as thedate from table";$rs = mysql_query($sql) or die($sql." : ".mysql_error());$row = mysql_fetch_array($rs);$day=$row["day"];$month=$row["month"];$year=$row["year"];// using substr$year1=substr($row["thedate"],0,4);$month1=substr($row["thedate"],5,2);$day1=substr($row["thedate"],8,2);?>

Best regards,

Share this post


Link to post
Share on other sites

Ok I need help on this puzzling problem. At first I thought that this person stored the dates in the MySQL database like this:

 

August 27, 2007

 

That kinda freaked me out a little, because string dates are hard to manipulate. Then I found out that he stored both th string data and numerical date, which I found a little bit odd, but it was like this:

 

2007-08-27

 

I need to build a PHP program to manipulate the data, but I need to access the year, month and day respectively by themselves.


Anyway for what I know, selecting the date from mysql database will ALWAYS be in YYYY-MM-DD, eg. 2007-10-17. Unless the data type of that date is set to TIMESTAMP. Then it will have YYYY-MM-DD HH:MI:SS, with the time.

 

Just like to confirm on that. Correct me if I'm wrong. Thanks

Share this post


Link to post
Share on other sites

Well, I actually wrote a function to manipulate dates quickly some time ago.

http://forums.xisto.com/no_longer_exists/

// INSERT FUNCTION reformat_date() HERE!$long_date = "August 27, 2007";$year = reformat_date($long_date, "Y"); // 2007$month = reformat_date($long_date, "n"); // 8$day = reformat_date($long_date, "j"); // 27

I didn't try the YYYY-MM-DD format but I think it will also work. The function is pretty basic and will work for nearly any English time format.


The guy probably used two time formates in his database because he wasn't very good with PHP date / time functions. Whatever the reason, dates aren't really that hard to manipulate if you let PHP do the work for you. You may note that my function is just a combination of two very common PHP date functions. The trick is figuring out the best way to combine PHP functions with your own hard work to get the results you desire.

Personally, I prefer to use UNIX timestamps for all of my date storage. It is easier to compare and manipulate that way.

Good luck,
vujsa

Share this post


Link to post
Share on other sites

You could also use the PHP explode function - it creates an array of elements taken from a string a separated with a delimiter. Or in this case:

 

$date = '2007-08-27';$parts = explode('-', $date);echo $parts[0]; // 2007echo $parts[1]; // 08echo $parts[2]; // 27

Of course, as vujsa said, UNIT timestamps are the best way to store date and time ;)

Share this post


Link to post
Share on other sites

The dates are stored as YYYY-MM-DD which is a good thing I suppose. At least better than MMM DD, YYYY.Anyways, I am trying TavoxPeru's method of substr() because it looks simplest. I will test it out sometime today I hope and see if it works as intended. As for explode(), I don't really understand arrays and that kind of stuff, the PHP manual didn't help either. But I will use it if I must. LOL

Share this post


Link to post
Share on other sites

You don't really have to understand arrays, as the following code would give you pure $year, $month and $day string ;)

$date = '2007-08-27';$parts = explode('-', $date);$year = $parts[0];$month = $parts[1];$day = $parts[2];

Furthermore, if you wanted integers instead of strings, you could convert them easily:

$yearNum = intval($year);$monthNum = intval($month);$dayNum = intval($day);

Share this post


Link to post
Share on other sites

to correct pyost a little, due to I think he wrote everything fast without checking and executing the code, the explode function in php parameters are the other way around, I mean like this:

$parts = explode('-', $date);

Share this post


Link to post
Share on other sites

Thanks Quatrux, I've corrected the two pieces of code ;) Though to me it still seems logical for the delimiter to come after the string ;)

Share this post


Link to post
Share on other sites

I guess that pyost get confuse with the implode() php function, which accepts its parameters in any order and do the opposite task -join- of the explode() php function.

 

BTW, the explode function has one more parameter that you can use to limit the maximum of limit elements, but for this case you don't need to use.

 

Best regards,

Share this post


Link to post
Share on other sites

Nope, I don't know either of them by heart, I just visit php.net every time I need them ;) And this time I wasn't careful when I read the order ;)

Share this post


Link to post
Share on other sites

I used to get confused by the same very thing. In some of the PHP functions, the order of parameters does defy the common logic. VB & C# support the split function as Split(<StringToSplit>, <Delimeter>). Another such function is str_replace which takes the syntax str_replace(<ReplaceWhat>, <ReplaceWith>, <ReplaceIn>). Again logic says that the source string must be the first parameter as is in, VB & C# - Replace(<ReplaceIn>, <ReplaceWhat>, <ReplaceWith>).

 

In C#, the Split() and Replace() methods are built into the string object itself. So, the first parameter (Source string) is not specified as the parameter. Rather, these method are called as SourceString.Split() or SourceString.Replace(). But, the thing is same, the source string must be specified before other parameters.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | 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.