Getting Certain Parts Of A Record The character data

[FirefoxRocks 0]

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.)

[TavoxPeru 0]

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,

[kelvinmaki 0]

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

[vujsa 0]

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

[pyost 0]

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

[FirefoxRocks 0]

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

[pyost 0]

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

[Quatrux 4]

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

[pyost 0]

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

[Quatrux 4]

PHP never were a logical language, hahahaha

[TavoxPeru 0]

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,

[pyost 0]

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

[FirefoxRocks 0]

Ok, how come no one suggested month() or monthname() ? I just found out those two.

[pyost 0]

There are no such functions in PHP5, or at least according to http://php.net/.

[turbopowerdmaxsteel 0]

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.