Page 1 of 2

Formatting Data

Posted: Tue Oct 16, 2007 11:48 am
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.

Posted: Tue Oct 16, 2007 11:50 am
by RobertGonzalez
What database server type is this stored in? If you can, tap into the DB date handling functions.

Posted: Tue Oct 16, 2007 1:06 pm
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?

Posted: Tue Oct 16, 2007 2:00 pm
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?

Posted: Tue Oct 16, 2007 2:11 pm
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. :)

Posted: Tue Oct 16, 2007 2:24 pm
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.

Posted: Tue Oct 16, 2007 3:44 pm
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?

Posted: Tue Oct 16, 2007 3:47 pm
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.

Posted: Tue Oct 16, 2007 3:50 pm
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.

Posted: Tue Oct 16, 2007 5:39 pm
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.

Posted: Tue Oct 16, 2007 5:49 pm
by RobertGonzalez

Posted: Wed Oct 17, 2007 8:59 am
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.

Posted: Wed Oct 17, 2007 9:32 am
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?

Posted: Wed Oct 17, 2007 10:15 am
by pickle
Once you have your separate year, month, & day - throw them at mktime()

Posted: Wed Oct 17, 2007 10:30 am
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";
?>