html/php calendar and MYSQL database

[aej71_le]

I have a simple calendar in an html form. Year, month and day are in dropdown menu with results saved to three separate variables. I want to insert the combined date into my mysql database (date column is in date format) using php. How do I combine the three variables and get them into a date format to insert into the mysql database? thanks

[guido2004]

'yyyy-mm-dd'

[aej71_le]

Thanks for the replies. The month is in the following format to make it user-friendly
<?php $startmonths = array('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'November', 'December');

with the following code in the form:

Month: <select name="startmonths">
<?php foreach ($startmonths as $startmonth): ?>
<option value="<?php $startmonth; ?>"><?php echo $startmonth; ?></option>
<?php endforeach; ?>
</select>

How would you assign a numeric equivalent to each month so that the data is in the right format for the MYSQL database?
Thanks

[guido2004]

<?php 
  $startmonths = array(
      '01' => 'January'
    , '02' => 'February'
    , '03' => 'March', 
    ....
    , '12' => 'December'
  );
?>

Month: <select name="startmonths">
<?php foreach ($startmonths as $key => $startmonth): ?>
<option value="<?php echo $key; ?>"><?php echo $startmonth; ?></option>
<?php endforeach; ?>
</select>

Don't forget to complete the array

[aej71_le]

Thanks for that. I have rewritten accordingly and returned the variables to the controller script with the following:

<?php $startdate = $startyear . '-' . $startmonth . '-' . $startday; ?>
<input type="hidden" name="startdate" value="<?php
htmlout($startdate); ?>"/>

But the entry in the database is just 0000:00:00 in the database. No error messages. Any ideas? Thank you

[rickibarnes]

How you're constructing the date there looks correct. If you view the page source, what does your hidden field say as its value? How is the data getting from the dropdowns into the hidden field?

[Cups]

When the possibility exists of picking a valid looking date which may not actually exist, run the result through checkdate() in case you have picked 2012-02-31.

http://php.net/manual/en/function.checkdate.php

[aej71_le]

How you're constructing the date there looks correct. If you view the page source, what does your hidden field say as its value? How is the data getting from the dropdowns into the hidden field?

Thanks for that. You are right. I have checked the source code and the values are not being passed to the variables in $startdate. The value of $startdate is instead the sum of the last item in each of the three lists, not the selected values:

Source code: <input type="hidden" name="startdate" value="2018-December-31"/>

Here is the html source code for month. Nothing is selected:

Month: <select name="startmonth">
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>

Here is the php code for start month:

Month: <select name="startmonth">
<?php foreach ($startmonths as $key => $startmonth): ?>
<option value="<?php htmlout($key); ?>"><?php echo $startmonth; ?>
<?php
if ($key['selected'])
{
echo ' selected="selected"';
}
?></option>
<?php endforeach; ?>
</select>

Similarly for days:

html source code: Day: <select name="startday">

<option value="1">1
</option>
<option value="2">2
</option>
<option value="3">3
</option>
<option value="4">4
</option>
<option value="5">5
</option>
<option value="6">6
</option>
<option value="7">7
</option>
<option value="8">8
</option>
<option value="9">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>


php code: Day: <select name="startday">

<?php foreach ($startdays as $startday): ?>
<option value="<?php htmlout($startday); ?>"><?php echo $startday; ?>

<?php
if ($startday['selected'])
{
echo ' selected="selected"';
}
?></option>
<?php endforeach; ?>
</select>

Any ideas? Thanks

[rickibarnes]

You've got your selection code in the wrong place, for a start. Your if statement needs to occur in the opening option tag, so for example your day version needs to look like:

<select name="startday">
 <?php foreach ($startdays as $startday): ?>
 <option value="<?php htmlout($startday); ?>"
<?php
 if ($startday['selected'])
 {
 echo ' selected="selected"';
 }
 ?>><?php echo $startday; ?></option>
 <?php endforeach; ?>
 </select>

However, I don't think this is your problem. If your hidden field and the select fields are all part of the same form, you would need to be passing the variables to the hidden field using JavaScript or similar - you can't populate a field in the same form without some sort of action taking place. So actually what is happening is that hidden field is just getting the last value that each of the variables held. This is why the month is coming up as "December" - we want it to be "12".

I think you might be slightly overcomplicating things though, anyhow. Without knowing exactly what you are trying to achieve, this is how I would do what you've described - getting day, month and year from an HTML form to a database.

First page, with form:

<select name="day">
<option value="01">1st</option>
<option value="02">2nd</option>
<option value="03">3rd</option>
...
etc ...
...
<option value="30">30th</option>
<option value="31">31st</option>
</select>

<select name="month">
<option value="01" selected>January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>

<select name="year">
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
<option value="2014">2014</option>
</select>

Second page, that the form submits to:

<?php
$dateConstruct = $_POST['year'] . "-" . $_POST['month'] . "-" . $_POST['day'];
$sql = "INSERT INTO tableName SET date='$dateConstruct', etc etc";
?>

Let me know if this is what you are looking for.

[aej71_le]

Thanks ever so much for that. It now works. As I'm learning, I stuck to the more complicated php way (I realise simple html would do the job as there isn't much data to type in) and made corrections based on your answer above. Thanks again.

[rickibarnes]

No worries, I know how it feels!

[guido2004]

Thanks ever so much for that. It now works. As I'm learning, I stuck to the more complicated php way (I realise simple html would do the job as there isn't much data to type in) and made corrections based on your answer above. Thanks again.

When the possibility exists of picking a valid looking date which may not actually exist, run the result through checkdate() in case you have picked 2012-02-31.

http://php.net/manual/en/function.checkdate.php

Don't forget to apply this tip from Cups, because the user can choose a non valid date (like Februari, 31).

[Cups]

Two observations:

Build your options list from arrays like this: (rm the comments when you understand it)

$days = range(1,31); // create and array

foreach($days as $day){  // loop through it and build your options
echo "<option value=$day>$day</option>" .PHP_EOL ; // add a line end so your source code is pretty
}

// months, create an array, assign the key 1 to the first instead of the default 0 value 
// which array normally assigns - after they they increment automatically

$months = array(1=>'Jan', 'Feb', 'Mar', ); // etc 

foreach($months as $key=> $month){
echo "<option value=$key>$month</option>" .PHP_EOL ;
} 

When these vals get passed back to your script handler, you will not have "xxxx-01-02" (for 2nd Jan) but "xxxx-1-2" - but its ok because Mysql will take these as valid values - there is some leeway - so there is no need to pad out the dates/months with zeros.

You can also create write once and forget values for years with range too:

$this_year = date("Y"); // you can go n years back if you want to
$span = $this_year + 4; // adds 4 more years

$years = range($this_year, $span);

foreach($years as $year){
echo "<option value=$year>$year</option>" .PHP_EOL ;
} 

[aej71_le]

Many thanks for the tips - the checkdate function is very useful and I am using it.

Not too sure what the purpose of .PHP_EOL is though.

Have added it to the php and then viewed source, but can't see any difference in the html.

Any suggestions as to where you place this in the following script? I have tried it after the closing <option> tag within a <?php ?> tag but no difference to html or to presentation on screen - I am using select tag anyway.

Thanks



Day: <select name="startday">
<?php foreach ($startdays as $startday): ?>
<option value="<?php htmlout($startday) ?>"
<?php
if ($startday['selected'])
{
echo ' selected="selected"';
}
?>><?php echo $startday ?></option>
<?php endforeach; ?>
</select>