Select Option for date-- whats the most practical way?

[Nico]

I have made 2 different ways to have a SELECT box for the date. The first one is one table, 3 colums "day,month,year".

This one I query 1 table and get the values needed, however since day is 31 characters and month and year don't have 31 entries, the select box shows 19 blank spots for month and 25 for year. Is there a way to strip the nulls (if a CHAR field) or zeros (if an INT field) so that I can have just one query from one table to fill all three boxes?

The second is, in my opinion, really messy. 3 tables - day, month, year. Three seperate queries however the SELECT boxes are filled correctly.

I'm fairly new to php/ mysql and i'm not sure which would be best to have. Since the information queried is quite small i doubt it really matters, however I'd like to know which is 'better' for the server. i.e. more efficient, faster, less work etc.

If anyone can tell me how to strip nulls or zeros from a query i'll probably go to the 1 table method simply because its not so messy, however I'm certainly open to suggestions.

Thanx
Nico

[mrmufin]

I'm sort of confused by your question...

However, I'll throw in my nickels worth on how I typically handle dates. Though MySQL has a wonderful array of functions for storing and manipulating temporal data, I prefer to store dates as integers which represent the appropriate UNIX timestamp. Since comparison of temporal data types involves means converting to a UNIX timestamp anyway, the method I use tends to be at least as efficient...

When gathering date information from user-world HTML, I'll use three <form> input fields: a select box for the month (based on an array where 1=>'January'...12=>'December'), a select box for the day (1=>1...31=>31), and either a select box or text field (depending on the application) for the year.

Assuming that the form fields are named 'month', 'day' and 'year' respectively, the PHP processing side will typically look something like this:

// Run PHP's checkdate() function on the values from user-world.
// This assures you won't be storing 'invalid' timestamps if a user selects June 31, 2003, etc..
if(!checkdate($_POST['month'], $_POST['day'], $_POST['year'])) {
	echo 'Invalid date entered...';
} else {
	// Use the mktime() function to get the UNIX timestamp associated with the date from user-world:
	// Since the first three arguments to mktime() are 'hour', 'minute' and 'second', set them to zero:
	$utime = mktime(0, 0, 0, $_POST['month'], $_POST['day'], $_POST['year']);

// Now we can safely insert the $utime into a single INTEGER column:
$query = "UPDATE sometable SET utime='{$utime}' WHERE somecolumn='some condition'";
}

When it comes time to retrieve that value, I'll do one of two things: either use one of the date-formatting functions available in MySQL:

$query = "SELECT FROM_UNIXTIME(utime, \"%M %d %Y\") AS 'date' FROM sometable WHERE somecolumn='some value'";

Or, select the column and let PHP do the date formatting via the date() function:

$query = "SELECT utime FROM sometable WHERE somecolumn='some value'";
//... run the query
//... clean up the results
// format the value using date():
$userdate = date("F d Y",$utime);

If you're doing something like building three tables (one for months, one for days and one for years, and then using those values to build select options for forms) as your post sort of suggests, I personally advise against this. It's unnecessary database activity. Two or three select boxes can be quickly assembled from 'static' PHP arrays in much less time...

Hope this helps a bit. As always, your mileage may vary...

[Nico]

I shall look this over in detail momentarily. I am not sure if this will do what i'm looking for, however I do believe it will help me in something different i'm doing. Some of it i can certainly use immediately. Thanx for the reply!

Let me clarify my previous post --

I have a 3 select fields on the page for the date:

month, day, year

I want to populate the pull downs with 01-12, 01-31 and 2002-xxxx.

And yes, it currently does query 3 different tables for the values for month,day,year. I agree, its a lot of activity that doesn't need to happen, however I am not sure how to populate the fields any other way other than old fashioned html. It seems this may end up being the best method, however I hope not 🙂

I could use (and currently am) the normal html <select> <option value="1">1</option> and so forth. This leads to more junk on a page and larger than needed file size.

The 3 date fields in question are combined into a hidden field via my javascript validation script.

function checkReceivedDate(){

	document.main.datereceived.value = ((document.main.year.value) + (document.main.month.value) + 
										(document.main.day.value));
	}

The combined value is named "datereceived". It is a historical date field when someone received an item. It can be a week ago, a month ago etc.

$datereceived is inserted into a table in a field, coincidentally, called datereceived. This field is a TIMESTAMP(8). (YYYYMMDD).

Basically I want the user to be able to put in any date (assuming its a legal date in the past-- which i will use the checkdate on and find a way to compare it to the current date to make sure it is indeed in the past).

However what I DON'T want, is for the user to be able to type in a date... that just begs for trouble. I want them only to have select box options that = $datereceived in the YYYYMMDD format so it can be inserted into the table.

The way I currently have it works just fine. I would like to have it so there is no extra date junk in the html portion of the page. And since I'm learning php, I figured this would be a good way to learn about how php/ mysql handles dates.

Your reply may do exactly that, but to be honest, I need to go over it with a fine toothed comb and look at everything in depth before I can say for sure.

Thanks again for the help!

Nico

[mrmufin]

Originally posted by Nico

I could use (and currently am) the normal html <select> <option value="1">1</option> and so forth. This leads to more junk on a page and larger than needed file size.

More junk on a page, yes. Larger file size, true. Plain old HTML = faster server-side delivery (assuming your 'dropping out' of <?php ?> mode) since it doesn't need to be pulled from a database table, built into a select box using PHP, etc.

The 3 date fields in question are combined into a hidden field via my javascript validation script.

function checkReceivedDate(){

	document.main.datereceived.value = ((document.main.year.value) + (document.main.month.value) + 
										(document.main.day.value));
	}

[/B]

And if I hit the site with JavaScript disabled on my browser (and it usually is) what happens? If you'd like to use JavaScript to do some client-side data validation, that's cool. But writing that data into a hidden form field upon success is unnecessary. And it only adds to additional headaches when folks like myself who usually keep it disabled submit the form to the server, only to find the server spit out some error message because the data it relied on was never populated on the client...

Basically I want the user to be able to put in any date (assuming its a legal date in the past-- which i will use the checkdate on and find a way to compare it to the current date to make sure it is indeed in the past).

So with all that in mind, (including your temporal data type in the database), you could just 'glue' the fields together on the server (after checkdate() validates the info, of course):

$datereceived = $_POST['year'] . $_POST['month'] . $_POST['day'];

I have no problem with JavaScript; in fact I had a (short-lived) love affair with it a few years back. A large part of the reason I went from client-side to server-side programming was because I learned that you simply can not depend on JavaScript to do anything important. It has its place, but it should never be relied upon, if for no other reason, than the fact that it can be disabled (or in some cases, not even present).

Back in the old days (oh, around 1996-97) folks who designed web pages that used JavaScript were urged (at least in most well-written JavaScript and HTML books) to remember the <NOSCRIPT></NOSCRIPT> tag set, and to consider the fact that not everybody had JavaScript enabled browsers... I think it's kinda funny how that tag set has disappeared from the web design landscape...

I'd probably be inclined to keep JavaScript enabled more often (and on some of my browsers, I do) if it weren't for that damned window.open() function which has been so irresponsibly deployed via the onLoad() and onUnload() methods (resulting in websites which try to sell me everything from vacation cruises to sex toys) as soon as I click into or try to leave a site.

Needless to say, I usually find surfing the internet a much more pleasurable experience with JavaScript disabled.

Just my too sense, your mileage may vary...

[Nico]

Indeed I definitely agree with you on the javascript, however this is an internal site and all browser have javascript enabled fortunately and you cannot turn it off. Otherwise I, like you, would not dream of making a validation script from js.

At the time, it was the way I knew how to make a validation script and it worked out for the best simply because it can't be turned off.

I WILL however 'glue' the fields together now. I have seen it before, but I didn't even think about putting in for this.

I think I will just leave the HTML junk in there. Bigger and dirtier, but its a better trade off than pulling static information off the server every time someone opens a page.