Formatting Data
Moderator: General Moderators
Formatting Data
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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'm naughty, are you naughty?'>smurf</span> way.
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'm naughty, are you naughty?'>smurf</span> way.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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?
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?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Just remember, The Manual is your friend.
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.
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.
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));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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
You are building the date yourself, right? Something like:
Instead of doing it that way try doing:
Code: Select all
<?php
$date = "$month/$day/$year";
?>Code: Select all
<?php
$date = "$year-$month-$day";
?>