Array, range and SQL sorting with or without leading zeros.

[colombian]

I am storing dates in a MySQL database in this format:

year-month-day

2008-2-9

 

People schedule events by choosing any future date, the events are then sorted through an SQL statement.

by using sort by date, ASC.

 

However, I noticed that without leading zeros, MySQL and PHP get confused, and October (10) ranks lower than February(3) and things like that.

I added the leading zeros to the month and day and then it works just fine.

 

However, I wanted to make my functions a little more elegant.

Before for the days, I'd use: $days = range(1,31);

 

Now I am using:

<?php
$days1 = array("01","02","03","04", "05", "06", "07", "08", "09");
$days2 = range(10,31);
$days = array_merge($days1, $days2);
?>

 

However, it feels cheap - and I am hoping someone here has a better suggestion for accomplishing this. Since now the first 9 days are strings and not integers (though fortunately once in MySQL it doesn't matter).

 

I was wondering if there is a better way of having those leading zeros displayed without resorting to this 'hack'.

 

 

Thanks in advance.

 

 

 

Is there a cleaner version of code for this?

[Barand]

Store dates in a table as DATE type column (or DATETIME if the time is relevant).

 

The native format is YYYY-MM-DD (ISO date format)

[colombian]

Thanks for the reply - that is how the dates are stored.

 

The question is more about how to display to the user those options - since they are selecting the time.

If you see my code, I have kind of a string array merging with a number array to get it to display all dates in the double digit format. This is strictly to echo a dropdown list for the user to select the date they want.

 

Maybe I misunderstand what you are saying - and I can display the dates in DD format in a select box by doing something similar - could you show me what you mean?

 

In my code the dates en up stored as YYYY-MM-DD, but to display the month or day to the user in an elegant/efficient function I resorted to those array mergers, so I was hoping for a better solution than that one.

 

Thank you.

[Daniel0]

I don't quite get what you mean. Are you trying to get them in another format? Check out DATE_FORMAT().

[Barand]

I am storing dates in a MySQL database in this format:

year-month-day

2008-2-9

The format you stated "2008-2-9" is not the same as ISO YYYY-MM-DD (Mysql date format) which would store it as "2008-02-09"

 

If you store them in the correct format you don't get your sorting problems.

 

try

<?php
echo "<select name='day'>\n";
for ($d=1; $d<=31; $d++)
{
    printf("<option value='%02d'>%02d</option>\n", $d, $d);
}
echo "</select>\n";
?>
-->

<select name='day'>
<option value='01'>01</option>
<option value='02'>02</option>
<option value='03'>03</option>
<option value='04'>04</option>
<option value='05'>05</option>
<option value='06'>06</option>
<option value='07'>07</option>
<option value='08'>08</option>
<option value='09'>09</option>
<option value='10'>10</option>
...
<option value='30'>30</option>
<option value='31'>31</option>
</select>

[colombian]

Sorry that I didn't make that more clear.

I *was* storing them like that with the efficient functions I was using.

 

My question is not how to store them to the database. I know how to store them there.

 

My question is how to create an efficient function to display that to the user.

Take a look at the array merge that I did for that. I merged the 10-31 range (since it's already DD format) with a manual array ("01", "02", .... "09"). By merging those, I got the 'DD' format to call my function in a variety of places to display the days of the month in a drop down for the user.

 

I could resort to do it manually like in your suggestion, but I am trying to improve efficiencies, and learn more object oriented, modular PHP code.

 

So I was hoping that there was some way to do a range (1-31) and still have the leading zeros where needed. Maybe imposing a number_format function on top of that...

 

The question is not how to store them in the database - it's creating an efficient function to display them to the user, without using the mentioned array_merge, unless that is as clean as it can go, but I doubt it.

 

 

 

 

 

[Barand]

to display dates, use date() function

 

<?php
$dbdate = '2008-04-06';

echo date ('l F jS, Y', strtotime($dbdate));
?>