Getting Certain Parts Of A Record

FirefoxRocks

Oct 16 2007, 10:09 PM

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

TavoxPeru

Oct 17 2007, 03:02 AM

QUOTE(FirefoxRocks @ Oct 16 2007, 05:09 PM)

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

CODE

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

Oct 17 2007, 05:40 AM

QUOTE

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

Oct 17 2007, 06:39 AM

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

http://www.handyphp.com/content/view/10/16/

CODE

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

Oct 17 2007, 11:40 AM

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:

CODE

$date = '2007-08-27';

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

echo $parts[0]; // 2007
echo $parts[1]; // 08
echo $parts[2]; // 27

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

FirefoxRocks

Oct 17 2007, 01:06 PM

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

Oct 17 2007, 01:35 PM

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

CODE

$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:

CODE

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

Quatrux

Oct 17 2007, 05:07 PM

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:

CODE

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

pyost

Oct 17 2007, 05:15 PM

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

Oct 17 2007, 08:00 PM

PHP never were a logical language, hahahaha

Latest Entries

pyost

Oct 27 2007, 08:12 AM

QUOTE(FirefoxRocks @ Oct 26 2007, 02:43 PM)

There are date and time functions here on dev.mysql.com, exact url is http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html.

Heh, didn't think of checking MySQL functions

TavoxPeru

Oct 27 2007, 03:17 AM

QUOTE(pyost @ Oct 26 2007, 03:51 AM)

There are no such functions in PHP5, or at least according to www.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:

CODE

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

FirefoxRocks

Oct 26 2007, 12:43 PM

QUOTE(pyost @ Oct 26 2007, 03:51 AM)

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

There are date and time functions here on dev.mysql.com, exact url is http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html.

turbopowerdmaxsteel

Oct 26 2007, 09:23 AM

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.

pyost

Oct 26 2007, 08:51 AM

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

Getting Certain Parts Of A Record - The character data

Getting Certain Parts Of A Record - The character data

Latest Entries