Dropdown menu will not output pm only am values to table

[edfou]

Hi guys, I'm having difficulty solving an issue with the time display. When I choose a time from the drop down on my form at http://www.cmfsc.ca/index.php?page=lostandfoundform  it writes the time to my table in this format  09:00:00  and even though I choose 9pm in the form drop down menu, it always outputs am when I go to view the records page at http://www.cmfsc.ca/index.php?page=lostandfound_records

Note; I don't want to use the 24 hr clock, and I think I should be able to use the 12hr clock with the am or pm suffix.

 

Here is the relevant portion of my form code:

 

                    <tr>
                      <td>Time lost/found</td>
                      <td>
                        <select name="time">
                        <?  print "<option value=\"\">choose</option>\n";
                        $startTime = mktime(7, 00, 0, 0, 0, 0); $endTime = mktime(23, 00, 0, 0, 0, 0);                     
                        for ($i = $startTime; $i <= $endTime; $i = $i + 3600)    {
                            $thisTime = date('g:i a', $i);
                                // nothing selected by default
                            $selected = '';
                                // if the form is submitted.
                                // Check that the posted value is the same as this value
                                // if is the same set it to selected.
                            if(isset($_POST['time']) && $_POST['time'] == $thisTime)
                                $selected = ' selected';
                                // added the selected attribute here
                                   print "<option value=\"$thisTime\"$selected>$thisTime</option>\n";    
                        }
                        ?>
                        </select>                      
                       </td>
                    </tr>

 

 

And my query to INSERT into the table is :

        // Define the query.
        $query = "INSERT INTO lostandfound (id, lostfound, day, time, description, location, name, contact, date_entered) VALUES (0, '$lostfound', '$day', '$time', '".mysql_real_escape_string($description)."', '".mysql_real_escape_string($location)."', '".mysql_real_escape_string($name)."', '".mysql_real_escape_string($contact)."', NOW())";

 

 

 

And here is the relevant code portion for the records page:

 

        "\n\t<td>". $row['description'] . "</td>".
        "\n\t<td>". $row['location']   . "</td>".
        "\n\t<td>". $row['day']     . "</td>".                
        "\n\t<td>". date('g:i a',strtotime($row['time'])) . "</td>".    
        "\n\t<td>". $row['name']     . "</td>".
        "\n\t<td>". $row['contact']  . "</td>".
        "\n\t<td>". date('M-d', strtotime($row['date_entered'])) . "</td>";    
        print "</tr>";

 

Thanks for any help!!!

Ed

[jcbones]

It isn't a matter of using a 12 hour clock or a 24 hour clock, it is a matter of how to store it in MySQL.  It should be stored in a DATETIME column or a TIMESTAMP column, to do so it will need to be in the 24 hour clock configuration of YYYY-MM-DD HH:MM:SS .  This is the preferred way to STORE the date or time, because it can then be accessed, sorted, added, subtracted, differenced, or any other of the many built in functions that MySQL has for dates and times.  The neat thing is that one of those functions is a DATE_FORMAT() function, that will return it in any way that you please.

What I am trying to tell you, is that you must separate the logic of storing and displaying.  Once you have the data stored in an efficient way, you can display it however you want.  Perhaps you would want to change how it is displayed in the future, with properly stored data, it is an easy task.

[Barand]

In your dropdown, even thought the times are displayed as 7:00pm, store the option values in hh:ii:ss format

EG

<option value='19:00:00' selected>7:00pm</option>

[edfou]

Thanks for your help guys! I worked through that and got that part working. What I did is take the value as posted from the form and changed the format before it gets written to the table (see below).

However I have a new challenge and that is simply to capture the current date/time but subtract two hours because my server is in a different timezone. I have added timezone code (see below) but that doesn't seem to help with this. The intent is to populate the POSTED column accurately. Then, eventually I hope to compare the value in the posted column with a later date so that old posts will get deleted automatically after 30 days. Not sure this is possible(!).  Thanks!

 

if ( isset ($_POST['submit'])) {

    date_default_timezone_set("America/Vancouver");

    $day = date("Y-m-d H:i:s", strtotime($_POST['day']));

    $time = date("Y-m-d H:i:s", strtotime($_POST['time']));  

[Barand]

What do the posted day and time look like (formats)?

[edfou]

I pasted in a recent test record from my table below, however my challenge now is not so much the day or time data but the date_entered data and the expiry_date data (which needs to be set at 30 days later than the date_entered data).

I can use NOW() in my INSERT into the table for date_entered but it is 2 hours ahead and I need to fix that.

Thanks for following up!!!

Ed

 

 

id    lostfound  day                      time                     description  location  name             contact

127 LOST       02/11/2013 0:00 10/11/2013 23:00 DF               DF         TESTING-25  AA        

 

date_entered               expiry_date

0000-00-00 00:00:00   0000-00-00 00:00:00

Edited November 12, 2013 by edfou

[KaiSheng]

i see that your variables are not properly validated. Lol.

[edfou]

Thanks jcbones and Barand. I'll close and mark this post as Solved and start a new one shortly as the topic has changed.
Cheers
Ed