SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question html/php calendar and MYSQL database

    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    'yyyy-mm-dd'

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    <?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

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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

  8. #8
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by rickibarnes View Post
    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

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    <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:
    HTML Code:
    <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 Code:
    <?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.

  10. #10
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No worries, I know how it feels!

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by aej71_le View Post
    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.
    Quote Originally Posted by Cups View Post
    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).

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Two observations:

    Build your options list from arrays like this: (rm the comments when you understand it)
    PHP Code:
    $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:

    PHP Code:
    $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 ;


  14. #14
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •