1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 12:37 pm Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

DATE and TIME giving me trouble in PHP.

Unable to format dates and times properly to and from database

         

mylungsarempty

3:01 pm on Jul 7, 2010 (gmt 0)

10+ Year Member



I have been working on this trying to figure it out for longer than I even want to say ... I just cannot figure out how to make it work!?

All I'm having a problem with is inputting the dates and times and retrieving them the way I want them formatted. Cannot bridge "human style" date times with "computer style" date times.

Any advice and help you can offer will be immensely appreciated...!


Here is the code I'm working on, with specifics removed: 

<body>

<html>

<body bgcolor="000001" text="tan">

<h2>example.com administrative list of shows</h2>

<hr>

<table border="0" width="95%">

<tr>

<td width="555" valign="top">

<blockquote>

<p><b>Add a new show here:</b></p>

<form action="admin-shows.php" method="post">

<table border="0">

<tr>

<td>

Date:

</td>

<td>

<input type="text" size="1" maxlength="2" name="MONTH"> / <input type="text" size="1" maxlength="2" name="DAY"> / <input type="text" size="2" maxlength="4" value="2010" name="YEAR"><br><font size="1">month/day/year</font>

</td>

</tr>

<tr><td>&nbsp; </td><td>&nbsp; </td></tr>

<tr>

<td>

Time:

</td>

<td>

<input type="text" size="1" maxlength="2" name="HOUR"> <b>:</b> <input type="text" size="1" maxlength="2" name="MINUTE">&nbsp; AM:<input type="radio" name="time" value="0">&nbsp; PM:<input type="radio" name="time" value="12" checked>

</td>

</tr>

</table>


<br /><br />

<?php

$con = mysql_connect("localhost","useromitted","passomitted");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("d60648383", $con);

$result = mysql_query("SELECT * FROM VENUES ORDER BY VENUENAME");

echo "Venue: <SELECT NAME='VENUE'>";

while($row = mysql_fetch_array($result))
 {

 echo "<option value='" . $row['VENUEID'] . "'><font face='Arial' size='1'>" . $row['VENUENAME'] . "</font></option>";

 }

echo "<br>";

mysql_close($con);

?>

</SELECT> &nbsp; <br />

<p><a href="http://example.com/admin/admin-venue.php" class="bluelinks">(add a venue)</a><br /></p>

Title: <input type="text" name="TITLE" size="45" />

<br /><br />

<table border="0"><tr><td valign="top">&nbsp; </td><td valign="top">Description:<br><textarea name="DESCRIPTION" rows="5" cols="40"></textarea></td></tr></table><br>

Age: &nbsp; &nbsp; <input type="radio" name="AGE" value="all ages">all ages &nbsp; &nbsp; <input type="radio" name="AGE" value="18+">18+ &nbsp; &nbsp; <input type="radio" name="AGE" value="21+">21+<br><br>

Cost of admission: <input type="text" name="COST" size="5" value="$"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

<input type="submit" value="Add Show" /><br><br>

</form>

</blockquote>

</td>

<td valign="top">

<?php
$con = mysql_connect("localhost","u70715871","5c9003");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("d60648383", $con);


$hour = $_POST[HOUR] + $_POST[time];
$minute = $_POST[MINUTE];
$second = "00";
$month = $_POST[MONTH];
$day = $_POST[DAY];
$year = $_POST[YEAR];

$showdatetime = "$year-$month-$day $hour:$minute:$second";



 $sql="INSERT INTO SHOWS (DATETIME, VENUE, TITLE, DESCRIPTION, AGE, COST) VALUES ('$showdatetime','$_POST[VENUE]','$_POST[TITLE]','$_POST[DESCRIPTION]','$_POST[AGE]','$_POST[COST]')";


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

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }
echo "<br><br><b>1 show added to example.com</b><br><br>";
print $sql."<br><br>".$showdatetime."<HR>";
}

echo "<br><br><table border='0' align='center' cellpadding='3' cellspacing='1' width='85%'>
<tr>
<th>ID</th>
<th>Date</th>
<th>Time</th>
<th>Venue</th>
<th>Title</th>
<th>Description</th>
<th>Age</th>
<th>Admission</th>
</tr>";

$result = mysql_query("SELECT * FROM SHOWS ORDER BY ID DESC");

while($row = mysql_fetch_array($result))
 {
 $showdate = date("m/d/y",$row['DATETIME']);
 $showtime = date("h:i A",$row['DATETIME']);

 $venueid = $row['VENUE'];

 $getvenue = "SELECT DISTINCT VENUENAME FROM VENUES WHERE VENUEID = $venueid";

 $venuename = mysql_query($getvenue);

$vname = mysql_fetch_array($venuename);
$venue = $vname['VENUENAME'];

 echo "<tr>";
 echo "<td bgcolor='016111'><font face='Arial' size='1'>" . $row['ID'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $showdate . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'><nobr>" . $showtime . "</nobr></font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $venue . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['TITLE'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['DESCRIPTION'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['AGE'] . "</font></td>";
 echo "<td bgcolor='111111' align='center'><font face='Arial' size='1'>" . $row['COST'] . "</font></td>";
 echo "</tr>";
 echo "<tr>";
 echo "<td bgcolor='00000f' align='right' valign='top' colspan='3'><font face='Arial' size='1'><b>URL:</b></font></td><td colspan='5' bgcolor='00000f'><a href='http://example.com/shows/index.php?id=" . $row['ID'] . "' class='bluelinks'>http://example.com/shows/index.php?id=" . $row['ID'] . "</font><br><br></td>";
 echo "</tr>";
 }
echo "</table>";

mysql_close($con);

?>

</td>

</tr>

</table>




Thank you so much for helping me.

mylungsarempty

3:19 pm on Jul 7, 2010 (gmt 0)

10+ Year Member



The data in the table is updating properly:

2010-07-07 04:20:00

So I'm not sure why it isn't being displayed on the page properly after the info is pulled from the table...

Matthew1980

3:20 pm on Jul 7, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hi there mylungsareempty,

Just scanning the code quickly I notice this:-

$hour = $_POST[HOUR] + $_POST[time];
$minute = $_POST[MINUTE];
$second = "00";
$month = $_POST[MONTH];
$day = $_POST[DAY];
$year = $_POST[YEAR];

Having that like that would throw an error, as the parser would presume a constant, you need them defined like this:-

$hour = $_POST['HOUR'] + $_POST['time'];
$minute = $_POST['MINUTE'];
$second = "00";
$month = $_POST['MONTH'];
$day = $_POST['DAY'];
$year = $_POST['YEAR'];


Ie, single quotes around the key names, that will clear up that error.

For formatting the time (from server query to output) you would need to use (within the query):-

DATE_FORMAT('colum_name', "format_tokens") as formatted_colum

At least I think this is what you are aiming at :) Looka at this link: [dev.mysql.com ] for more information.

Also, as you are using the $_POST's in query's I would at least use mysql_real_escape_string() and strip_tags() to sanitise the data before you use it in the sql query.

Hope this is what you were referring to anyway.

Cheers,
MRb

mylungsarempty

7:03 pm on Jul 13, 2010 (gmt 0)

10+ Year Member



that's fantastic - I'm going to work on this right now and post my results -- thank you!

mylungsarempty

7:17 pm on Jul 13, 2010 (gmt 0)

10+ Year Member



Ok, well, I wasn't getting an error before and I'm not getting one now - so it wasn't a quote vs. no quote issue...

$showdatetime is not being passed to DATETIME field that i named DATETIME - is it because I named the field the type of field that it is? That's all i can figure.. let me know if that's not allowed. Thanks!

rocknbil

12:15 am on Jul 14, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



is not being passed to DATETIME field that i named DATETIME


Correct . . . this is what backticks (not quotes) are for. You should never name fields reserved words, but if you do,

$sql="INSERT INTO SHOWS (`DATETIME`, `VENUE`, `TITLE`, `DESCRIPTION`, `AGE`, `COST`) VALUES ('$showdatetime','" . $_POST['VENUE'] . "','" . $_POST['TITLE'] . "','" . $_POST['DESCRIPTION'] . "','" . $_POST['AGE'] . "','" . $_POST['COST'] . "')";

Now to confuse the issue, you do *not* quote field names in functions - but if you have a conflicting name, you backtick it:

DATE_FORMAT('DATETIME`, "format_tokens") as formatted_colum

mylungsarempty

12:29 am on Jul 14, 2010 (gmt 0)

10+ Year Member



I'm confused. I think I need to sit down for a while...

rocknbil

7:25 pm on Jul 14, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Well standing at your computer will do that . . . :-)

Sorry . . . and I have a typo, again. OK you have a field named datetime. datetime is a mysql data type, so it is a reserved word. The way around that is to *backtick* the field.

select `DATETIME` from table;

When you use any of the mysql functions, there are generally two choices. You can perform the function directly on a field or on a literal value. If you perform it on a field, it will not require quotes - but since your field is a reserved word, you will need to use backticks.

select date_format(`DATETIME`,"%m-%d-%Y") from table;

(Compare the previous with my post above, I had a typo there, sorry)

However, if you perform it on a literal value, it **will** require quoting.

$today = '2010-07-14';
select date_format("$today","%m-%d-%Y");
--> 07-14-2010

mylungsarempty

8:01 pm on Jul 14, 2010 (gmt 0)

10+ Year Member



Well, I have already changed the field name to SHOWTIME anyhow, so I don't think that was my problem... I will post the updated code here and see if anyone can help me see why the table isn't updating... 



<table border="0" width="95%">

<tr>

<td width="555" valign="top">

<blockquote>

<p><b>Add a new show here:</b></p>

<form action="admin-shows.php" method="post">

<table border="0">

<tr>

<td>

Date:

</td>

<td>

<input type="text" size="1" maxlength="2" name="MONTH"> / <input type="text" size="1" maxlength="2" name="DAY"> / <input type="text" size="2" maxlength="4" value="2010" name="YEAR"><br><font size="1">month/day/year</font>

</td>

</tr>

<tr><td>&nbsp; </td><td>&nbsp; </td></tr>

<tr>

<td>

Time:

</td>

<td>

<input type="text" size="1" maxlength="2" name="HOUR"> <b>:</b> <input type="text" size="1" maxlength="2" name="MINUTE">&nbsp; AM:<input type="radio" name="time" value="0">&nbsp; PM:<input type="radio" name="time" value="12" checked>

</td>

</tr>

</table>


<br /><br />

<?php

$con = mysql_connect("localhost","u70715871","5c9003");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("d60648383", $con);

$result = mysql_query("SELECT * FROM VENUES ORDER BY VENUENAME");

echo "Venue: <SELECT NAME='VENUE'>";

while($row = mysql_fetch_array($result))
 {

 echo "<option value='" . $row['VENUEID'] . "'><font face='Arial' size='1'>" . $row['VENUENAME'] . "</font></option>";

 }

echo "<br>";

mysql_close($con);

?>

</SELECT> &nbsp; <br />

<p><a href="http://example.com/admin/admin-venue.php" class="bluelinks">(add a venue)</a><br /></p>

Title: <input type="text" name="TITLE" size="45" />

<br /><br />

<table border="0"><tr><td valign="top">&nbsp; </td><td valign="top">Description:<br><textarea name="DESCRIPTION" rows="5" cols="40"></textarea></td></tr></table><br>

Age: &nbsp; &nbsp; <input type="radio" name="AGE" value="all ages">all ages &nbsp; &nbsp; <input type="radio" name="AGE" value="18+">18+ &nbsp; &nbsp; <input type="radio" name="AGE" value="21+">21+<br><br>

Cost of admission: <input type="text" name="COST" size="5" value="$"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

<input type="submit" value="Add Show" /><br><br>

</form>

</blockquote>

</td>

<td valign="top">

<?php
$con = mysql_connect("localhost","u70715871","5c9003");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("d60648383", $con);


$hour = $_POST['HOUR'] + $_POST['time'];
$minute = $_POST['MINUTE'];
$second = '00';
$month = $_POST['MONTH'];
$day = $_POST['DAY'];
$year = $_POST['YEAR'];

$showdatetime = "$year-$month-$day $hour:$minute:$second";



 $sql="INSERT INTO SHOWS (SHOWTIME, VENUE, TITLE, DESCRIPTION, AGE, COST) VALUES ('$showdatetime','$_POST[VENUE]','$_POST[TITLE]','$_POST[DESCRIPTION]','$_POST[AGE]','$_POST[COST]')";


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

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }
echo "<br><br><b>1 show added to example.com</b><br><br>";
print $sql."<br><br>".$showdatetime."<HR>";
}

echo "<br><br><table border='0' align='center' cellpadding='3' cellspacing='1' width='85%'>
<tr>
<th>ID</th>
<th>Date</th>
<th>Time</th>
<th>Venue</th>
<th>Title</th>
<th>Description</th>
<th>Age</th>
<th>Admission</th>
</tr>";

$result = mysql_query("SELECT * FROM SHOWS ORDER BY ID DESC");

while($row = mysql_fetch_array($result))
 {
 $showdate = date("m/d/y",$row['SHOWTIME']);
 $showtime = date("h:i A",$row['SHOWTIME']);

 $venueid = $row['VENUE'];

 $getvenue = "SELECT DISTINCT VENUENAME FROM VENUES WHERE VENUEID = $venueid";

 $venuename = mysql_query($getvenue);

$vname = mysql_fetch_array($venuename);
$venue = $vname['VENUENAME'];

 echo "<tr>";
 echo "<td bgcolor='016111'><font face='Arial' size='1'>" . $row['ID'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $showdate . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'><nobr>" . $showtime . "</nobr></font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $venue . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['TITLE'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['DESCRIPTION'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['AGE'] . "</font></td>";
 echo "<td bgcolor='111111' align='center'><font face='Arial' size='1'>" . $row['COST'] . "</font></td>";
 echo "</tr>";
 echo "<tr>";
 echo "<td bgcolor='00000f' align='right' valign='top' colspan='3'><font face='Arial' size='1'><b>URL:</b></font></td><td colspan='5' bgcolor='00000f'><a href='http://example.com/shows/index.php?id=" . $row['ID'] . "' class='bluelinks'>http://example.com/shows/index.php?id=" . $row['ID'] . "</font><br><br></td>";
 echo "</tr>";
 }
echo "</table>";

mysql_close($con);

?>

</td>

</tr>

</table>

</body>

</html>



I really wish it would work :(

Maybe you can help me! :)

Matthew1980

8:37 pm on Jul 14, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hi there mylungsareempty,

>>why the table isn't updating...

Well it won't update, your inserting data :)

So it's just the query you are having issues with then?

$sql="INSERT INTO SHOWS (`SHOWTIME`, VENUE`, `TITLE`, `DESCRIPTION`, `AGE`, `COST`) VALUES ('".$showdatetime."','".$_POST['VENUE']."','".$_POST['TITLE']."','".$_POST['DESCRIPTION']."',
'".$_POST['AGE']."','".$_POST['COST']."') ";

That's the revised version..

The single quotes around the $_POST keys were missing, and, not really necessary, but I changed the vars so that they are concatenated, but that's just my own preference, either way that *should* function now.

Cheers,
MRb
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 12:37 pm Apr 28, 2026