Formatting date? Hard to understand how.

I used to know this stuff, but it's hard to figure out after 5 years...

[mylungsarempty]

I'm just trying to understand how to retrieve a date from MySQL and format it the way I'd like - I've read plenty of pages about the FORMAT_DATE function, but, I'm obviously not putting it together...

<td valign="top">

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

mysql_select_db("abc", $con);


 $sql="INSERT INTO SHOWS (DATE, TIME, VENUE, TITLE, DESCRIPTION, AGE, COST) VALUES ('$_POST[DATE]','$_POST[TIME]','$_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>";
}

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

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>";


while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td bgcolor='016111'><font face='Arial' size='1'>" . $row['ID'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['DATE'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['TIME'] . "</font></td>";
 echo "<td bgcolor='111111'><font face='Arial' size='1'>" . $row['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>

That's the code I'm working on - I hope someone can show me how to use this FORMAT_DATE(date,format) thing properly - nothing I'm trying is working for me. Thanks a great deal..!

[edited by: jatar_k at 3:58 pm (utc) on Apr 23, 2010]
[edit reason] removed specifics [/edit]

[jatar_k]

this would be the manual page
[dev.mysql.com...]

there are some examples there, what have you tried?

also I sure hope you are going to add validation to your $_POST vars

[mylungsarempty]

Yes, I read that page. How do I insert the per-row variable into the FORMAT_DATE function, so that each row returned displays the corresponding date, formatted? I cannot just input a specific date, I need the page to extract the date the way it's doing it in the WHILE loop. I just need to be able too plug the $row['DATE'] variable into the date formatting function...

Add validation to my $_POST vars? Do you mean validating the form data? I will be the only one using the form, if that's what you're meaning... it won't be open to other users. Thanks for your guidance, does this update clarify my issue?

The first time I learned PHP I had a concentration perscription from the doctor. I'm re-learning it drinking old-fashioned coffee and I'll admit it's a little more to grapple with.

[rocknbil]

I will be the only one using the form

This is every beginning programmer's mistake, assuming that hackers won't find their scripts. They will. So start thinking about validating and cleansing input.

On to your question: You don't want to mess with the date and time types, it will wreak havoc on your ordering of the results. Leave them be. You get the format you want in selecting, not inserting.

SELECT * FROM SHOWS ORDER BY ID DESC

Normally you format as it's being selected, which you can't do here with a star select. But you **can** nest a query, like so.

$query = select date_format($row['DATE'],"%m/%d/%Y"); // mm/dd/yyyy
$dt_res = mysql_query("$query");
// There will only be one result.
$dt_row = mysql_fetch_array($dt_res);
// Store it back in $row so you don't have to change anything else
$row['DATE'] = $dt_row[0];

$query = select date_format($row['TIME'],"%r"); // hh:mm:ss AM/PM
$dt_res = mysql_query("$query");
$dt_row = mysql_fetch_array($dt_res);
$row['TIME'] = $dt_row[0];

[mylungsarempty]

I found this code useful, when inserted into the while loop as modified:

$showdate = $row['DATE'];
$query = "select date_format($showdate,'%m/%d/%Y')"; // mm/dd/yyyy
$dt_res = mysql_query("$query");
// There will only be one result.
$dt_row = mysql_fetch_array($dt_res);
// Store it back in $row so you don't have to change anything else
$showdate = $dt_row[0];

Thank you. I feel like I am getting closer to completing the page.

[rocknbil]

Well, that's the same thing really, you're just storing it in a variable in between, taking up a few more bytes in memory. I like to recycle. :-P

But it's all good if it works.

[mylungsarempty]

Well, the code as you typed it did not work. The quotation marks weren't working. I had to make this change to not receive an error. I think the quotes in the variable name confused the date_format function, but I'm not sure - all I know is that this change fixed it - for anyone else who comes across this post and needs to use it.