Welcome Guest ( Log In | Register )



2 Pages V   1 2 >  
 Reply to this topicStart new topic
> Getting Certain Parts Of A Record, The character data
FirefoxRocks
post Oct 16 2007, 10:09 PM
Post #1


Super Member
Group Icon

Group: [HOSTED]
Posts: 651
Joined: 12-July 06
From: Ontario, Canada
Member No.: 14,464
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.)
Go to the top of the page
 
+Quote Post
TavoxPeru
post Oct 17 2007, 03:02 AM
Post #2


Super Member
Group Icon

Group: [HOSTED]
Posts: 744
Joined: 8-April 06
From: Lima - Peru
Member No.: 12,579
QUOTE(FirefoxRocks @ Oct 16 2007, 05: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.)

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,
Go to the top of the page
 
+Quote Post
kelvinmaki
post Oct 17 2007, 05:40 AM
Post #3


Advanced Member
Group Icon

Group: Members
Posts: 170
Joined: 30-July 07
Member No.: 23,704
QUOTE
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
Go to the top of the page
 
+Quote Post
vujsa
post Oct 17 2007, 06:39 AM
Post #4


Absolute Newbie
Group Icon

Group: Admin
Posts: 887
Joined: 20-February 05
From: Indianapolis, Indiana, USA (Midwest)
Member No.: 2,714
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
Go to the top of the page
 
+Quote Post
pyost
post Oct 17 2007, 11:40 AM
Post #5


Nenad Bozidarevic
Group Icon

Group: [MODERATOR]
Posts: 998
Joined: 7-November 05
From: Belgrade, Serbia
Member No.: 9,500
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 wink.gif
Go to the top of the page
 
+Quote Post
FirefoxRocks
post Oct 17 2007, 01:06 PM
Post #6


Super Member
Group Icon

Group: [HOSTED]
Posts: 651
Joined: 12-July 06
From: Ontario, Canada
Member No.: 14,464
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
Go to the top of the page
 
+Quote Post
pyost
post Oct 17 2007, 01:35 PM
Post #7


Nenad Bozidarevic
Group Icon

Group: [MODERATOR]
Posts: 998
Joined: 7-November 05
From: Belgrade, Serbia
Member No.: 9,500
You don't really have to understand arrays, as the following code would give you pure $year, $month and $day string smile.gif

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);
Go to the top of the page
 
+Quote Post
Quatrux
post Oct 17 2007, 05:07 PM
Post #8


the Q
Group Icon

Group: [HOSTED]
Posts: 1,017
Joined: 13-July 05
From: Lithuania, Vilnius
Member No.: 7,059
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);
Go to the top of the page
 
+Quote Post
pyost
post Oct 17 2007, 05:15 PM
Post #9


Nenad Bozidarevic
Group Icon

Group: [MODERATOR]
Posts: 998
Joined: 7-November 05
From: Belgrade, Serbia
Member No.: 9,500
Thanks Quatrux, I've corrected the two pieces of code wink.gif Though to me it still seems logical for the delimiter to come after the string tongue.gif
Go to the top of the page
 
+Quote Post
Quatrux
post Oct 17 2007, 08:00 PM
Post #10


the Q
Group Icon

Group: [HOSTED]
Posts: 1,017
Joined: 13-July 05
From: Lithuania, Vilnius
Member No.: 7,059
PHP never were a logical language, hahahaha biggrin.gif
Go to the top of the page
 
+Quote Post
« Next Oldest · PHP · Next Newest »
 

2 Pages V   1 2 >
Reply to this topicStart new topic

Collapse

> Similar Topics

Topics Topics
  1. Php, Sql Lite: Storing Session's Data?(1)
  2. PHP Based Site Access Authentication - Help(4)
  3. Php/mysql Data Display(3)
  4. Possible To Do?(7)
  5. [PHP + MySQL] Encrypting Data(9)
  6. Reading Data From Sessions(2)
  7. Storing Data Into Xml With A Php Form(2)
  8. Need An Alternative To $http_post_data For PHP4(5)
  9. Data Passing - Re An Assignment For School - Please Help :)(8)
  10. Send XML Data To PHP Page(0)
  11. Proper Way To Grab User Data?(1)
  12. Retrieving Data And Displaying In Boxes(6)
  13. Sql Injection Prevention (passing Numerical Data Across Pages).(9)
  14. Letting Users Add Mysql Data With Php(1)
  15. Reading Xml Data(2)


 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

- Lo-Fi Version Time is now: 30th August 2008 - 05:32 PM
Powered By IP.Board 2.2.1 © 2008  IPS, Inc.
Licensed to: Xisto Corporation