Select list and working with dates and mysql

[Orangutang]

Hi,

I've got a select list where the user can choose the date with:
<select name=dayone>
<option value='01'>01</option>
etc, etc, etc.
</select>
and the same for month and year.

Then I post these values and pick them up on the next page with:

$dayone = $_POST['dayone'];
$mthone = $_POST['mthone'];
$yrone = $_POST['yrone'];

Then I prepare them for inserting into mysql with:

$deldateone = ("$yrone-$mthone-$dayone");

and insert $deldateone in the query.

And I have my date in mysql as 2011-02-13.

In another part of the application I'm trying to enable the user to edit the date so I'm retrieving the date in one variable but I need to split this one variable $deldateone back into 3 so I have $yrone, $mthone and $dayone to echo in the select list.

Is there a way to do this?

Any advice would be really helpful,
Many thanks in advance.

[g1smd]

You could use various parts of the "date" function or else "explode" breaking on the "-" character.

[Orangutang]

Hi g1smd,

Thanks for the advice but I'm still struggling. I thought I'd looked at all the php date functions but I couldn't find one that did what I need. I probably missed it or read it and mis-understood it so any help there would be great.

I've also looked at explode and found this which I understand.

$deldate = "2011-02-13";
$deldatesplit = explode("-", $deldate);
echo "Year = $deldatesplit[0]<br />";
echo "Month = $deldatesplit[1]<br />";
echo "Day = $deldatesplit[2]<br />";

And it displays:

Year = 2011
Month = 02
Day = 13

But I don't have $deldate = "2011-02-13". I only have $deldate.

At the moment I'm echoing $deldate out and its displaying 2011-02-13 but I'd like to split it into 3 so I can echo out each one independently in a select list with 3 separate fields.

Hopefully I'm explaining it ok, I realise that's half the problem for someone trying to help.

[Orangutang]

Hi,

I've found a way to do it but it seems a bit long winded. When the date is originally picked from the select list and posted and picked up I use sessions by doing this:

$_SESSION['dayone'] = $dayone;
$_SESSION['mthone'] = $mthone;
$_SESSION['yrone'] = $yrone;

Then 10 pages later I recall them and echo them out with:

$dayone = ($_SESSION['dayone']);
$mthone = ($_SESSION['mthone']);
$yrone = ($_SESSION['yrone']);

echo $dayone;
echo $mthone;
echo $yrone;

Then I've got my 3 variables to use but I'm negating using sql and I thought I could retrieve from sql db when needed then split into 3 to get my 3 variables like you do when you re-format the date for display using date_format.

Is there not something like date_format which splits it rather than reformatting it? Like a date_split function?

[Orangutang]

Nope, unfortunately the sessions method doesn't work because the form can be input one day by one user then the edit ability needs to be done by another user on another day and my session data's lost.

?

[Orangutang]

I've found another way but it involves not using mysql date as a column stipulation. Instead of one column in mysql with date stipulated have 3 columns with integer stipulated, one named dd, one named mm and one named yyyy.

When I initially send the form don't do the bold:

$dayone = $_POST['dayone'];
$mthone = $_POST['mthone'];
$yrone = $_POST['yrone'];

$deldateone = ("$yrone-$mthone-$dayone");

Leave them as individual variables, $dayone, $mthone and yrone and insert them into the 3 columns, dd, mm and yyyy.

Then whenever I echo them out display 3 cells instead of 1. I don't display $deldateone in 1 cell but $dayone, $mthone and $yrone in 3 cells. Takes up more space but that's ok for this.

Is this an ok way to sort this because I've normally seen dates inserted into a mysql date field?

[rocknbil]

Looking at the whole thing, first, you don't need to reply on programmatic hoop jumping to extract each piece of the date. It's alread in mySQL as below. Storing them as individual fields is not a great idea as it requires two more fields and complicates and ordering you'd like to do.

Look at this, from Date and Time functions [dev.mysql.com]:

select date_format(yourfield,"%Y"), date_format(yourfield,"%m"), date_format(yourfield,"%d") from users where user_id=1234; 
while ($row=mysql_fetchrow_array)) { 
 // Remembering fetchrow_array returns BOTH indexed and 
 // associative array, you don't need "select field as..." 
 $year = $row[0]; 
 $month = $row[1]; 
 $day = $row[2]; 
} 

Will give you 2011, 02, and 14 respectively.

Now comes the cool part. :-) Return to your original option list. you will always need these three together - year DD, M DD, D DD - right? So create a function that will populate the right selection only if it exists. There are a number of ways to do this, here's one simple one.

// Preset these so if there's no record, it will auto select them to the top values.
$year=$month=$day=null;
// Execute the above query, then
$my_datelist = get_date_list('birthday',$year,$month,$day);
echo $my_date_list;

This returns three lists with the names "birthday_year", etc., so you can have multiple date lists on a page (think startdate, expirationdate.) You'd explode the name values when reading in to tell which is which.


my_date_list would go something like this.

// Defined these as constants so you don't have to use globals 
// you may want to get the end year dynamically so it's always 
// this year, requiring no maintenance 
define('START_YEAR',1901); 
define('END_YEAR',2011); 
// 
function get_date_list($name,$yyyy=null,$mm=null,$dd=null) { 
 $yname = $name . '_year'; 
 $mname = $name . '_month'; 
 $dname = $name . '_day'; 
 $start_year = START_YEAR; 
 $end_year = END_YEAR; 
 $dtString = "  
 <select name=\"$mname\" id=\"$mname\">  
 <option value=\"\">--</option>  
 ";  
 for ($i=1;$i<12;$i++) {  
 $moTxt = (strlen($i)<2)?"0" . $i:$i; // 01 instead of 1 
 $dtString .= "<option value=\"$moTxt\"";  
 if ($mm == $moTxt) { $dtString .= ' selected'; } 
 // Or selected=\"selected\" if you're using XHTML, which you most likely won't need  
 $dtString .= ">$moTxt</option>\n";  
 }  
 $dtString .= "  
 </select>  
 <select name=\"$dname\" id=\"$dname\">  
 <option value=\"\">--</option>  
 ";  
 for ($i=1;$i<32;$i++) {  
 $daTxt = (strlen($i)<2)?"0" . $i:$i;  
 $dtString .= "<option value=\"$daTxt\"";  
 if ($dd == $daTxt) { $dtString .= ' selected'; } 
 $dtString .= ">$daTxt</option>\n";  
 }  
 $dtString .= "  
 </select>  
 <select name="$yname" id="$yname">  
 <option value=\"\">--</option>  
 ";  
 for ($i$start_year;$i<=$end_year;$i++) {  
 $dtString .= "<option value=\"$i\"";  
 if ($yyyy == $i) { $dtString .= ' selected'; } 
 $dtString .= ">$i</option>\n";  
 }  
 $dtString .= "  
 </select>\n";  
 // 
 return $dtString; 
} // End get_date_list() 

May contain errors, typed on the fly . . .

So what have you got?

- A date - in, date-out method of handling mysql dates that allows internal mysql functions to manage the date extraction and insertion, requiring no special programming.
- A portable function that can be used from any part of your programs without duplicated code everywhere.
- Never hard code a date list again.
- Never dig through your code to update some value (e.g., the year start/ends)
- You leave your dates as a mysql date format, allowing you to leverage the power of ordering and limiting on datefields when you need to.

select * from table where datefield >= date_sub(curdate(),interval 2 week) and datefield <=curdate() order by datefield desc limit 20;


... Gives you the last 20 records from the two weeks with most recent first. You'd have a lot of programming to do that if you have year, month, day as individual fields. :-)

[Orangutang]

Hi rocknbil,

That's brilliant, thanks for spending the time to put that together.

It makes perfect sense, I just doubt I'm at the standard I need to be to be able to implement it but I'll give it a go. :-)

[Orangutang]

Deleted.

[Orangutang]

Hi rocknbil,

Not too bad in the end and I've had some success, the out method is working. :-)

I originally started off trying to split the date in 3 so after the date was originally selected by a select list and inserted into the db another user on another day could edit it and have a nice select list to do so. I think we could well and truly say that's been accomplished. :-)

And this as well:

- A date - in, date-out method of handling mysql dates that allows internal mysql functions to manage the date extraction and insertion, requiring no special programming.
- A portable function that can be used from any part of your programs without duplicated code everywhere.
- Never hard code a date list again.
- Never dig through your code to update some value (e.g., the year start/ends)
- You leave your dates as a mysql date format, allowing you to leverage the power of ordering and limiting on datefields when you need to

Beautiful :-)

I've posted my final working code below which achieves the out method, I noticed you had Birthdays in mind when you posted whereas I need to handle delivery dates so I've changed the START_DATE.

<?php
// connect to db

// Defined these as constants so you don't have to use globals
// you may want to get the end year dynamically so it's always
// this year, requiring no maintenance
define('START_YEAR',2011);
define('END_YEAR',2021);

/// START FUNCTION ///////////////////////////////////////////
function get_date_list($name,$yyyy=null,$mm=null,$dd=null) { // don't understand this line, function being passed these variables from where?
$yname = $name . '_year'; // not sure what these are or how they came to exist?
$mname = $name . '_month';
$dname = $name . '_day';
$start_year = START_YEAR;
$end_year = END_YEAR;
$dtString = "

<select name=\"$dname\" id=\"$dname\">
<option value=\"\">--</option>
";
for ($i=1;$i<32;$i++) {
$daTxt = (strlen($i)<2)?"0" . $i:$i; // 01 instead of 1
$dtString .= "<option value=\"$daTxt\"";
if ($dd == $daTxt) { $dtString .= ' selected'; }
$dtString .= ">$daTxt</option>\n";
}
$dtString .= "
</select>

<select name=\"$mname\" id=\"$mname\">
<option value=\"\">--</option>
";
for ($i=1;$i<13;$i++) {
$moTxt = (strlen($i)<2)?"0" . $i:$i;
$dtString .= "<option value=\"$moTxt\"";
if ($mm == $moTxt) { $dtString .= ' selected'; }
$dtString .= ">$moTxt</option>\n";
}
$dtString .= "
</select>

<select name=\"$yname\" id=\"$yname\">
<option value=\"\">--</option>
";
for ($i=$start_year;$i<=$end_year;$i++) {
$dtString .= "<option value=\"$i\"";
if ($yyyy == $i) { $dtString .= ' selected'; }
$dtString .= ">$i</option>\n";
}
$dtString .= "
</select>\n";

//
return $dtString;
} // End get_date_list()

/// END FUNCTION ///////////////////////////////////////////

$datetest = "select date_format(deldate,'%Y'), date_format(deldate,'%m'), date_format(deldate,'%d') from reqs where reqid=57 ";
$dateresult = mysql_query($datetest) or die(mysql_error());

while ($row=mysql_fetch_array($dateresult)) {
$year = $row[0];
$month = $row[1];
$day = $row[2];

// for debugging
echo $year;
echo $month;
echo $day;
?><br /><?php
}

?><hr><?php
// Preset these so if there's no record, it will auto select them to the top values.
$year=$month=$day=null;
// display list with get_date_list function
$copydatetest = "select date_format(deldate,'%Y'), date_format(deldate,'%m'), date_format(deldate,'%d') from reqs where reqid=57 ";
$copydateresult = mysql_query($copydatetest) or die(mysql_error());

while ($row=mysql_fetch_array($copydateresult)) {
$year = $row[0];
$month = $row[1];
$day = $row[2];
$my_date_list = get_date_list('deldate',$year,$month,$day);
}
?><br /><?php
echo $my_date_list;
?>

This is seriously helpful, I've got loads of select lists within my application and now I've got a really cool function to sort them all out.

The only thing I need to work out now is how to put it into a form and insert them into db for the in method but I thought I'd post that separately otherwise it would of be too long and getting hard to read.

Serious thanks though. :-)

[rocknbil]

Overall, "you 'got' it". :-)

function get_date_list($name,$yyyy=null,$mm=null,$dd=null) { // don't understand this line, function being passed these variables from where?

When you do this,

$my_date_list = get_date_list('deldate',$year,$month,$day);

You are passing whatever values you want for Year, $month, $day to the function. The function is then using those to build the select list names and id's.

Want a list with no preset dates?

$my_date_list = get_date_list('deldate');

Because we do this in the function,

$name,$yyyy=null,$mm=null,$dd=null

It allows those parameters to be optional. Name, of course, is not.

$yname = $name . '_year'; // not sure what these are or how they came to exist?

These are "relatively arbitrary" and just an easy way for me to identify incoming date variables. So you can see the robustness of something this simple, taking my multi data lists in one form as an example, let's say you have date lists for start date and expiration date:

$start_list = get_date_list('startdate',$year,$month,$day);
$end_list = get_date_list('expiredate',$year,$month,$day);

Then, assume this is the "receiving end" of the posted data

$datefields = Array ('startdate','expiredate');

$fields=null;

foreach ($datefields as $fld) { 
 $yy = $fld . '_year'; // startdate_year and expiredate_year 
 $mm = $fld . '_month'; // startdate_month and expiredate_month 
 $dd = $fld . '_day'; // startdate_day and expiredate_day 
 // Presuming you've ALREADY CLEANSED $_POST and for simplicity 
 if ( 
 isset($_POST[$yy]) and ! empty($_POST[$yy]) 
 and isset($_POST[$mm]) and ! empty($_POST[$mm]) 
 and isset($_POST[$dd]) and ! empty($_POST[$dd]) 
 ) { 
 $ins = $_POST[$yy] . '-' . $_POST[$mm] . '-' . $_POST[$dd]; 
 if ($fields) { $fields .= ','; } 
 $fields .= " $fld='$ins'"; 
 } 
} 
// 
if ($fields) { 
 $fields = preg_replace('/,$/','',$fields); // or use trim. :-) 
 $query = "update table set $fields where id=12345"; 
 // etc. 
} 

In other conditions, I may just "look" for it so I don't have to define an array.

foreach ($_POST as $key=>$value) { 
 if (preg_match('/[a-z]+\_year$/i',$key)) { 
 // I've found a date select list 
 list($name,$tag) = explode('_',$key); 
 $yy = $name . '_year'; // redundant, I know . . . 
 $mm = $name . '_month'; 
 $dd = $name . '_day'; 
 // proceed as above 
 } 
} 

Of course, if you only have one value, you don't need an array - so you could just hard code this part.
$yy = 'deldate_year';
$mm = 'deldate_month';
$dd = 'deldate_day';

The only thing I need to work out now is how to put it into a form and insert them into db for the in method but I thought I'd post that separately

I think I just answered the insert part. To put your list in a form, it's now a string variable like any other variable.

$my_date_list = get_date_list('deldate',$year,$month,$day);

echo "
<form action=\"yourscript.php\" method=\"post\">
<p><label for=\"deldate\">Delete Date:</label> $my_date_list </p>
</form>
";
or, the popular but difficult-to-debug drop in 'n' out of PHP,

<form action="yourscript.php" method="post">
<p><label for="deldate">Delete Date:</label> <?php $my_date_list ?> </p>
</form>

As you get familiar with this, you can extend your function to include labels, CSS styles, onload handlers, and extend it to other form objects. I'm sure there's a library or libraries that already do this (uhhh . . . Zend. :-) ) but there always seems to be that one condition that the library doesn't do that you need. Well, I always seem to find them anyway . . . .

[Orangutang]

Hi rocknbil,

Is that single or doubles quotes you’ve got round that. :-)

I see what you mean by using the function, it makes everything I need to do so simple and enables the code to be handled robustly.

I’ve posted below what I got working for the multi data lists:

/// SCRIPT START ///

<?php
// connect to db
// define constants
// paste above function as is

strip_tags(mysql_real_escape_string($_POST['startdate'],$mysql)); //Is this cleaning and does it go here?
strip_tags(mysql_real_escape_string($_POST['enddate'],$mysql)); // Is this cleaning and does it go here?

$datefields = Array ('startdate','enddate');
$fields=null;

foreach ($datefields as $fld) {
$yy = $fld . '_year'; // startdate_year and expiredate_year
$mm = $fld . '_month'; // startdate_month and expiredate_month
$dd = $fld . '_day'; // startdate_day and expiredate_day

// Presuming you've ALREADY CLEANSED $_POST and for simplicity /// Not sure?
if (
isset($_POST[$yy]) and ! empty($_POST[$yy])
and isset($_POST[$mm]) and ! empty($_POST[$mm])
and isset($_POST[$dd]) and ! empty($_POST[$dd])
) {
$ins = $_POST[$yy] . '-' . $_POST[$mm] . '-' . $_POST[$dd];
if ($fields) { $fields .= ','; }
$fields .= " $fld='$ins'";
}
}
//
if ($fields) {
$fields = preg_replace('/,$/','',$fields); // or use trim. :-)
$reqquery = "UPDATE reqs SET $fields WHERE reqid = 59 ";
$reqresult = mysql_query ($reqquery) or die (mysql_error());
}

/// FIRST SELECT FOR STARTDATE AND PRESET ///
$startdatetest = "select date_format(startdate,'%Y'), date_format(startdate,'%m'), date_format(startdate,'%d') from reqs where reqid=59 ";
$startdateresult = mysql_query($startdatetest) or die(mysql_error());
while ($row=mysql_fetch_array($startdateresult)) {
$year = $row[0]; $month = $row[1]; $day = $row[2];
// for debugging
echo $year;
echo $month;
echo $day;
?>Req 59 Start Date<br /><?php
}
?><hr><?php
// Preset these so if there's no record, it will auto select them to the top values.
$year=$month=$day=null;

/// SECOND SELECT FOR STARTDATE AND DISPLAY WITH FUNCTION ///
$startcopydatetest = "select date_format(startdate,'%Y'), date_format(startdate,'%m'), date_format(startdate,'%d') from reqs where reqid=59 ";
$startcopydateresult = mysql_query($startcopydatetest) or die(mysql_error());
while ($row=mysql_fetch_array($startcopydateresult)) {
$year = $row[0]; $month = $row[1]; $day = $row[2];
$start_list = get_date_list('startdate',$year,$month,$day);
}

/// FIRST SELECT FOR ENDDATE AND PRESET ///
$enddatetest = "select date_format(enddate,'%Y'), date_format(enddate,'%m'), date_format(enddate,'%d') from reqs where reqid=59 ";
$enddateresult = mysql_query($enddatetest) or die(mysql_error());
while ($row=mysql_fetch_array($enddateresult)) {
$year = $row[0]; $month = $row[1]; $day = $row[2];
// for debugging
echo $year;
echo $month;
echo $day;
?>Req 59 End Date <br /><?php
}
"<hr>";
// Preset these so if there's no record, it will auto select them to the top values.
$year=$month=$day=null;

/// SECOND SELECT FOR ENDDATE AND DISPLAY WITH FUNCTION ///
$endcopydatetest = "select date_format(enddate,'%Y'), date_format(enddate,'%m'), date_format(enddate,'%d') from reqs where reqid=59 ";
$endcopydateresult = mysql_query($endcopydatetest) or die(mysql_error());
while ($row=mysql_fetch_array($endcopydateresult)) {
$year = $row[0]; $month = $row[1]; $day = $row[2];
$end_list = get_date_list('enddate',$year,$month,$day);
}
?>

<form action="startendinout.php" method="post">
<p><label abc="def">Start Date:</label> <?php echo $start_list ?> </p>
<p><label abc="def">End Date:</label> <?php echo $end_list ?> </p>
<hr>
<input type="submit" class="submit" name="submit" value="Submit" />
</form>
<hr>

<?php
/// SELECT REQS BETWEEN TWO DATES ///
$btn = "select * from reqs where deldate >= date_sub(curdate(),interval 2 week) and deldate <=curdate() order by deldate desc limit 20";
$btn_res = mysql_query($btn) or die(mysql_error());
while ($row=mysql_fetch_array($btn_res)) {
$req_id = $row[0]; $deldate = $row[13];
?>Req ID:<?php echo $req_id; ?> <br />
Del Date:<?php echo $deldate;?> <br /><?php
}
?>

/// SCRIPT END ///


I’ve never seen code processed like this before:

RECEIVE
$datefields = Array ('startdate', 'enddate');
// foreach
// code
// out $fields
// update db
// And $fields are the sql column names startdate and enddate.

SEND
$start_list = get_date_list('startdate',$year,$month,$day);
$end_list = get_date_list('enddate',$year,$month,$day);
// And startdate and enddate are the names of the sql column.

<form action="" method="post">
<p><label for="deldate">Start Date:</label> <?php echo $start_list ?> </p>
<p><label abc="def">End Date:</label> <?php echo $end_list ?> </p>

I’ve used functions in a basic way I suppose to check fields as in example below but never created a variable for the function, ie: $start_list and echo it out in the form and post it.

if (!filled_out($_POST)) {
throw new Exception ("<font size='3' color='#FF0707'>Incorrect details, please try again!</font>");
}

Function:

function filled_out($form_vars) {
foreach ($form_vars as $key => $value) {
if ((!isset($key)) || ($value == '')) {
return false;
}
}
return true;
}

I’m using the filled_out function to check the fields and continue if there populated and this is all its doing but the get_date_list function is obviously doing a lot more then all of what’s been done in the function is being put in the $start_list variable and then that variable which contains all the data in the function is being posted. I didn’t even realise to could move data around like this. :-)

This leads me on to the problem I’m having of where to clean the post data, I’ve placed it where I thought it should be but I’m not sure. I’m assuming because I’ve just posted all the data in the function startdate and enddate would be in there so I could use them as I have?

the popular but difficult-to-debug drop in 'n' out of PHP,

Ah, I’ve been wondering what this was about. I can’t see not having to debug and I don’t want to make that any harder than it needs to be. As you can see above I’ve been dropping in and out.

Does it mean where I’ve enclosed the html in start and end php tags I replace them with quotes and end with a semicolon:

?><hr><?php

to this

“<hr>”;

Is that it or am I missing something there?

And thanks again for all your help with this, considering where I started and where I am now, I can't believe it :-)

[rocknbil]

Is that single or doubles quotes you’ve got round that. :-)

Around what? This?

$fields .= " $fld='$ins'";

The answer is "both." Double quotes allows scalar variables to interpolate (i.e., represent a value and not a literal), but you need quotes of one kind or the other) for non-numeric values to insert in your statements.

"exploded" (which will not work properly as it will attempt to insert spaces) these are equivalent.

$fields .= " $fld = ' $ins ' ";

$fields .= " $fld= \" $ins \" ";

I’ve never seen code processed like this before:

It's just one solution to "what is the most efficient and expandable way to approach this problem?" Imagine if a third field is added to your updates. What's required?

$datefields = array('startdate','expiredate','mynewfield');

Just add it to the array.

the popular but difficult-to-debug drop in 'n' out of PHP,

....

Does it mean where I've enclosed the html in start and end php tags I replace them with quotes and end with a semicolon:

This is just my opinion; many say this method of coding reflects PHP's power and versatility, I see it as a maze, making things more difficult. Some areas of programming style is like Ford versus Chevy versus Toyota, there is no one answer. All one needs to do is compare, say, the programming style recommendations in the modX documentation against the style guides for Wordpress or other apps to see this.

In page output (X)HTML, we separate presentation from markup by moving it all into css.
Following the same concept, I like to separate programming from output as much as possible for easier maintenance, keeping PHP in PHP files, HTML/Templates/Static resources in those types of files.

Rather than this,

<p><?php echo "Boo"; ?></p>
<p><?php echo "$variable"; ?></p>
<p><?php echo "$another_variable"; ?></p>

I'd rather do this.

<?php
include('mystyler.php');
$params = Array('Boo',$variable,$another_variable);
echo get_some_block('p',$params);
?>

mystyler.php, something like this (Not sure if my array params work this way, but you get the idea . . . )

function get_some_block($style,$lines) {
$out=null;
if ($style=='li') { $out .= '<ul>'; }
foreach ($lines as $line) {
$out .= '<' . $style . '>' . $line . '</' . $style . '>';
}
if ($style=='li') { $out .= '</ul>'; }
return $out;
}

You could see what happens if you pass li instead of p . . .

Didn't have the time to fully examine your script but wonder why you're doing an insert before outputting the form. If it works, it works . . .

[Orangutang]

Is that single or doubles quotes you’ve got round that. :-)

That was a joke but obviously mis-aligned. :-)

I didn't know a lot about using single or double quotes so what you've written has helped clear that up.

I see what your saying about coming in and out of php, I'll try things with that as I go

Cheers. O