PHP - MySQL Help - DATE formatting?

[MVLSports]

This is probably a simple fix for most of you; however, I'm fairly new to PHP and MySQL.

So I have form and the database set up accordingly with each of the fields. I have the "Starting Date", "Ending Date", and "Deadline Entry" fields being stored as a DATE in the database.

I want this data to be outputted in chronological order by "Starting Date". My problem is that it wants the date as YYYY-MM-DD, if it's not entered in this format, the does not get entered. Not many users will want to enter the date in this manner. I personally don't know of many people that use that type of formatting either. I also don't want it to be outputted like that.

I would prefer to have the output as "February 24, 2011", for example.

Do I need to make changes to my script or to the database?

I even thought about making the month, day of month, and year separate fields and then sorting by month, day of month, and year. I don't know that it'd work that way though because wouldn't if return April, August, etc. in alphabetical order instead?

Any direction, advice, suggestions, etc. is great appreciated.

Sorry I couldn't post any links to what I currently have, I don't have a post count of 10 or greater, so I was not able to post links.

Thanks,
Mark

[weegillis]

There probably is a quick fix, but you might not have learned anything in so implementing it. This is a perfect opportunity to 'develop' a solution, based upon what your problem presents as, and upon your desired result.

The basic issue is that the user may supply a date in a variety of formats, most (except one) not valid for your back end process. So this is where you need to take control. First create (or borrow) a date validator that parses a submitted string, discovers and tabulates the date fields, and then converts their data to your desired form before sending off the query to MySQL.

We can further shorten the process by looking at the HTML (and DOM) of our form. Can we force a user to enter date field data just with the control we choose? Can we use DOM to do some of the validation on change or submit?

These are just some of the general problem solving questions we need to learn to ask, and do what we can to answer them before we jump to one implementation or another, without really knowing what it does or how it functions. My two cents...

[weegillis]

Here is some form code to consider, if you haven't already:

<fieldset id="start">
<legend>Start Date</legend>
<div>
<label for="startdate">
<select name="startdate" id="startdate">
<option value="01" selected="selected">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
</label>
<label for="month">
<select name="startdate" id="month">
<option value="January" selected="selected">January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="May">May</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>
</select>
</label>
<label for="year">
<select name="startdate" id="year">
<option value="2009" selected="selected">2009</option>
<option value="2010">2010</option>
</select>
</label>
</div>
</fieldset>
<fieldset id="end">
<legend>End Date</legend>
<div>
<label for="todate">
<select name="enddate" id="todate">
<option value="01" selected="selected">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
</label>
<label for="tomonth">
<select name="enddate" id="tomonth">
<option value="January" selected="selected">January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="May">May</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>
</select>
</label>
<label for="toyear">
<select name="enddate" id="toyear">
<option value="2009" selected="selected">2009</option>
<option value="2010">2010</option>
</select>
</label>
</div>
</fieldset>
This form could be generated, rather than included in the source file, but the client would see it like it is shown above. Some of the DOM (and AJAX) work that could be done would be to adjust the list length for dates depending upon the month shown in the adjacent control. You could also dictate which years would be visible. I stole this snippet from an old page that was lying around on this box, so mine are a little out of date.


Why 01, 02, ..? The visual can be filtered from the display, but the literal is for sorting, otherwise, 10-19 come before 2, and 20-29 before 3.

[MVLSports]

Thanks for the response, I think you're a bit over my head though.

I'm thinking about taking a longer route, sure it's probably a bit more work, but I'll understand it.

I'm thinking that I can have the month, day of month, and year as separate fields. I would then run a query:
SELECT * FROM tablename WHERE month='January' and year='desired year here'
ORDER BY startingdayofmonth

echo "resultshere";

Then I'd copy and paste that and change the month to February for the following month and so on.

[MVLSports]

Your second post is similar to what I was thinking of doing.

[weegillis]

Your second post is similar to what I was thinking of doing.
Ultimately, it all stems from the data we collect in the first place. Using form controls, we have control of the data from the start. We can present it to the user the way a human wants to see it, and pass the valid format to our handler processes.

Having looked at the various process points in your plan, a little more reading and digging will result in your finding a suitable solution, ready made, that you can implement without having to write all the code. It comes off as copy and paste if we just run out and grab it, but having examined our problem and taken the time to understand the possible solutions, without knowing all the code, it's no longer 'just' c/p, but borrowing code that makes the web better.

I always give a commented attribution for borrowed code and include the URL in the source file. That way it's not plargiarism. Best to look for code that the person has offered up to the world and comply with their attributioin wishes. Google and Yahoo! are also sources for code, but you will want to read and understand their terms of service, and probably have to sign up with their API group.

So as long as you have a firm grip of the reins in your data collection methodology, the processing end will be fairly straight forward and present little in the way of serious challenge. It begins with your form, and how the data is validated client side, and with AJAX or the form handler, server side, calibrating and tabulating the data for storage. I have little doubt there are ready made solutions to do this.

After that it's down to reporting. Here is where you may need to create some hands on PHP to properly query and tabulate select data, or data groups. It's possible at this point to even hand off some of the processing to the client so the user can sort without calling back the server, or so AJAX can be used to call back for a new sort order. There are still choices on your part to make, with respect to the whole process of collecting, storing and reporting.

Keep in mind all along the way that your HTML will play a large part, so carefully craft as much of it as you can before beginning the script processes. Use field names that make sense when used as variable names (semantics) and your coding task will already be easier.

[chrike528]

After you pull the date from the DB you can just reformat it to match the look you want.

$DBdate="2010-08-12";
echo date("F d, Y", strtotime($DBdate));
//Will Output: August 12, 2010


And before you dd to DB reformat the date to mtch the db:

$yourdate="August 12, 2010";
echo date("Y-m-d", strtotime($yourdate));
//Will Output: 2010-08-12

[weegillis]

The question would still remain, where to reformat the data: the client side, or the server side? My take is that if there is a shred of DOM script running on the client side form controls (event listeners and such), then this is where to place initial validation, filtering and rewriting.

While server side processing can be elegant and efficient, it can also be more load on the server. At least with DOM you have a way of sharing the processing clicks across your user base. The advent of API repositories has really opened the field for client side widgets and plugin support. With so many libaries now available, why wouldn't one avail oneself to their usage? Half the code is cached on millions of users' machines already.

[MVLSports]

So what if I coded a hidden field for "monthid" (for example) that says something along the lines of if March equals startmonth then month id equals 3? I could then use the "monthid" to sort right? Any holes in this idea?

[chrike528]

You can also just search and sort the data in the sql query with:
$month= 3;
SELECT date_column FROM table_name WHERE MONTH() = $month ORDER BY STR_TO_DATE(date_column, '%d/%m/%y') ASC;

[weegillis]

Any time we add secondary processing we add to complexity and load time. The hole will be in our pocket book if we can't properly maintain it, and better still, re-use it elsewhere. Consider also the makeup of your POST data. For instance, in the above form, there are only two data objects, not six as you will note by the form control's name attributes. The data is already grouped as 'startdate' and 'enddate'.



function month($post_date) {
return date("m",$post_date);
}
.
.
// $month = month($any_POST_date_format);

/* or from our post variables array the two variables we know are date formats */

$startmonth = month($startdate);
$endmonth = month($enddate);


The value matches your desired result without excessive insitu logic being added to the fray.

[denvermatt]

In my opinion you will definitely want to separate your fields for month, day and year into separate form fields in the way that weegillis suggested with his sample code. You do this in order to normalize the data for your code so that you can inject it into the database. If you don't, then users will type all kinds of garbage into your form and screw up your application.

Some will do it because they're stupid, some will do it for sport.