Formatting Data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

RobbieL
Forum Commoner
Posts: 31
Joined: Fri Mar 23, 2007 5:57 pm

Formatting Data

Post by RobbieL »

I've had a play about, but can't seem to find a solution. I have three drop down boxes, one contains the days (1-31), one contains the months(1-12) and one contains the years(2008-2020). The user selects a date and submits it. The date takes the form of DD/MM/YYYY.

My problem is I need to run a query that grabs and bunch of data and organises it by year. The only way I can think of it is to run to seperate queries. The first grabbing all the dates and putting the results into an array. Then, using the split, remove the "/" and put each portion of the date in their own variable. The second query would grab all the data and group it by the variable that contains the year.

I can't help but feel I'm missing a step out. Would I need to use the SQL LIKE function to say only select data were the date ends in the years that are currently in the variable?

I'm at work at the moment, and will be for a while so not able to play about with it. Anyone able to clarify if this is a successful method of pulling this off? Or is there an easier way of doing it? Sorry if any of that sounds confusing. I'm new to PHP, so any help would be much appreciated.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What database server type is this stored in? If you can, tap into the DB date handling functions.
RobbieL
Forum Commoner
Posts: 31
Joined: Fri Mar 23, 2007 5:57 pm

Post by RobbieL »

It's on a MySQl database, and it's stored in a VARCHAR field. Is it possible to convert data submitted via a form into the date function?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

There are a lot of things you can do. My first question is why are you storing dates in a varchar field? Why not use a DATE or DATETIME field?
RobbieL
Forum Commoner
Posts: 31
Joined: Fri Mar 23, 2007 5:57 pm

Post by RobbieL »

I've only previously used the DATETIME field when I'm just inserting the date using PHP's date function. I assumed since the user is setting the date themselves, there would be some format issue between what they submit and what format the DATE/DATETIME field takes.

I'm new to PHP, so I'm bound to be doing alot of things is a strange and <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> way. :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Hey, how else are you supposed to learn? We have all been there.

Can you give an example of some data. It doesn't have to be real data, but it should resemble the data you are asking about. Let's see if we can't help you out a bit.
RobbieL
Forum Commoner
Posts: 31
Joined: Fri Mar 23, 2007 5:57 pm

Post by RobbieL »

Cheers Everah, this is really appreciated. :)

Right. Some data. The user that is filling out the form is creating an event. It needs a title, description and date. So some data might be:

In title: Learning PHP
In Description: A beginners guide to learing PHP
Date: 12/11/2007

When they submit it, it then goes into a table called "Events", that has four fields. ID (Auto Increment PK), Title (VARCHAR), Description(TEXT) and Date(VARCHAR).

Is that what you're looking for?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, for a start.

Now what would happen if you changed the Date(VARCHAR) field to a Date(DATE) field? Then you would have everything you need to be able to handle the date math you want to do inside the database without any futzing around in PHP.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

I'd recommend converting your date field from VARCHAR to DATE, then converting the user entered date of 12/11/2007 to a MySQL timestamp: YYYY-MM-DD. You could then use MySQL functions to return only entries with a certain year.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
RobbieL
Forum Commoner
Posts: 31
Joined: Fri Mar 23, 2007 5:57 pm

Post by RobbieL »

Cheers for the replies guys.

It definetily sounds like what I need. Not to sure I'm familiar with MySQL functions, so will give them a little research. Will give it all a shot when I get home. Will let you know how I get on. No doubt I'll hit a snag somewhere down the line. :)

Cheers again. Your help is much appreciated.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

RobbieL
Forum Commoner
Posts: 31
Joined: Fri Mar 23, 2007 5:57 pm

Post by RobbieL »

Already hit a bit of a snag. I've converted the date field to DATE from VARCHAR. But trying to convert the date that is taken in by the form to a MySQL TimeStamp is giving me some bother.

I've taken in the three parts of the date the user submits in the form, and got them in the 12/11/2007 format I had earlier. But converting that to a MySQL TimeStamp is baffling me.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

RobbieL wrote:Already hit a bit of a snag. I've converted the date field to DATE from VARCHAR. But trying to convert the date that is taken in by the form to a MySQL TimeStamp is giving me some bother.

I've taken in the three parts of the date the user submits in the form, and got them in the 12/11/2007 format I had earlier. But converting that to a MySQL TimeStamp is baffling me.

Code: Select all

date('Y-m-d', strtotime($your_date_string));
That help?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Once you have your separate year, month, & day - throw them at mktime()
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You are building the date yourself, right? Something like:

Code: Select all

<?php
$date = "$month/$day/$year";
?>
Instead of doing it that way try doing:

Code: Select all

<?php
$date = "$year-$month-$day";
?>
Post Reply