I am trying to change the format of how the date appears on the output from my form. I have searched the internet and I believe this is what I need to do:
SELECT datetime(date_submitted,'%m/%d/%Y') as date_submitted FROM guestquestionnaire
But it's giving me a blank page as though something is wrong. Any ideas? Please let me know if you need more code. I am using PDO and I ideally want the date format at mm/dd/yyyy. If I can also get the time format as 11:11 am (as an example) that would be great too!!
Update - Code:
<?php
try {
$handler = new PDO('mysql:host=localhost;dbname=***', '***', '***');
$handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo $e->getMessage();
die();
}
class guestquestionnaireEntry {
public $id, $date_submitted,
$entry;
public function __construct()
{
$this->entry = "
<tr style='font-size: 8pt;'><td width='60%'><a href=\"?ID={$this->ID}\">ID</a> </td><td width='40%' colspan='2'>{$this->date_submitted}</td></tr>
<table border='1' align='center'>
<tr style='background: #566890; font-size: 8pt; font-weight: bold; color: #fff;'><td colspan='3'>Prior to Arrival</td></tr>
</table>";
}
}
SELECT DATE_FORMAT(date_submitted, '%m/%d/%Y') AS date_submitted FROM guestquestionnaire
// Checks if the submitted is a number. If so, isolates the ID and adds "where" clause
$id = (!empty($_GET['ID']) && is_numeric($_GET['ID']))? " where ID = '".$_GET['ID']."'" : "";
// Add the $id to the end of the string
// A single call would be SELECT * FROM guestquestionnaire where ID = '1'
$query = $handler->query("SELECT * FROM guestquestionnaire{$id}");
$query->setFetchMode(PDO::FETCH_CLASS, 'guestquestionnaireEntry');
while($r = $query->fetch()) {
echo $r->entry, '<br>';
}
?>
try:
SELECT date_format(`date_submitted`,'%m/%d/%Y') as `date_submitted` FROM `guestquestionnaire`
Now that the full code has been posted it appears the problems did not lie with the sql. I have not tested the following but it looks more or less ok so I hope it works.
<?php
try {
$handler = new PDO('mysql:host=localhost;dbname=***', '***', '***');
$handler->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch( PDOException $e ) {
die( $e->getMessage() );
}
class guestquestionnaireEntry {
public $id, $date_submitted,$entry;
public function render(){
return "
<tr style='font-size: 8pt;'>
<td width='60%'>
<a href=\"?id={$this->id}\">ID</a>
</td>
<td width='40%' colspan='2'>{$this->date_submitted}</td>
</tr>
<table border='1' align='center'>
<tr style='background: #566890; font-size: 8pt; font-weight: bold; color: #fff;'>
<td colspan='3'>Prior to Arrival</td>
</tr>
</table>";
}
}
$id = !empty( $_GET['ID'] ) && is_numeric( $_GET['ID'] ) ? "where `ID`='".$_GET['ID']."'" : "";
$sql = trim("select * from `guestquestionnaire` {$id}");
$query = $handler->query( $sql );
$result = $query->fetchAll( PDO::FETCH_CLASS, 'guestquestionnaireEntry' );
foreach( $result as $obj ) echo $obj->render();
?>
The function you're looking for is date_format:
SELECT DATE_FORMAT(date_submitted, '%m/%d/%Y') AS date_submitted
FROM guestquestionnaire
select convert(varchar(),YourDateColumn,103) from YourTable
sorry for my bad english..
if i am understanding your question ,you want is to retrive date and time which is in DateTime format and stored in date_sumitted column in database..
try below code once .. works for me. i hope It will be helpful to you
$stmt = $db->query("SELECT DATE_FORMAT(date_submitted, '%m/%d/%Y') AS date_submitted FROM guestquestionnaire ");
$row = $stmt->fetchall(PDO::FETCH_ASSOC);
foreach ($row as $key)
{
$date = $key['date_submitted'];
echo $date;
}