HOME       >>       Programming

Getting Certain Parts Of A Record The character data


FirefoxRocks

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

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

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

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

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

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

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

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

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

PHP never were a logical language, hahahaha


TavoxPeru

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

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

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


pyost

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


turbopowerdmaxsteel

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.


FirefoxRocks

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

There are date and time functions here on dev.mysql.com, exact url is http://forums.xisto.com/no_longer_exists/.


TavoxPeru

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

That's correct, but you can obtain the month name easily using the strftime(), strtotime(), date() and time() php functions together or directly with the MONTHNAME() MySql function:

<?php// php functions$monthname=strftime("%B",strtotime(date("d-m-Y",time())));// MySql function$sql="SELECT MONTHNAME('2007-10-26') as mname";$rs=mysql_query($sql) or die('Error...');$row=mysql_fetch_array($rs);$mname=$row["mname"];echo "Month Name with Php: " . $monthname;echo "<br />Month Name with MySql: " . $mname;?>
Best regards,

pyost

There are date and time functions here on dev.mysql.com, exact url is http://forums.xisto.com/no_longer_exists/.

Heh, didn't think of checking MySQL functions


VIEW DESKTOP VERSION REGISTERGET FREE HOSTING

Xisto.com offers Free Web Hosting to its Members for their participation in this Community. We moderate all content posted here but we cannot warrant full correctness of all content. While using this site, you agree to have read and accepted our terms of use, cookie and privacy policy. Copyright 2001-2019 by Xisto Corporation. All Rights Reserved.