CODEWITHSUNDEEP
phpmysqltimemysqlidate-format
Envious
Envious

Reputation: 487

MySQL Date_Format isn't working with

I have a PHP page with a MySQL database with 2 fields that use the TIME type in MySQL. I want to convert those two fields from the 24 hour format (00:00:00) into 12 hour AM/PM format (00:00 AM) using MySQL's DATE_FORMAT('','') but it's not working.

So far, what I have done is created a 3rd and 4th field that also uses the TIME type. I send the 1st and 2nd field into the 3rd and 4th and convert the 3rd and 4th to preserve the original. 

<?php
//connection statements omitted

$sql="SELECT * FROM $table ORDER BY date LIMIT $start, $amount";
$result = $mysqli->query($sql);

//Sends the original time_in data to the new format_in column. 
$sql2="UPDATE $table SET format_in = time_in";
$result2=$mysqli->query($sql2);

$sql3="SELECT DATE_FORMAT(format_in,'%l:%i %p') FROM $table";
$result3=$mysqli->query($sql3);

while($row = $result->fetch_array()){

?>
//other fields omitted
<td><?php echo $row['format_in'];?></td>

//end while loop
<?php } ?>

The only thing this code does is replicates whatever was in the time_in column. Which is the standard 24 hour format 00:00:00. Basically, this code doesn't do anything. What am I doing wrong here?

EDITED to show my $result

Upvotes: 0

Views: 1351

Answers (1)

raidenace
raidenace

Reputation: 12836

while($row = $result3->fetch_array()){

instead of 

while($row = $result->fetch_array()){

Based on the conversation we had in comments section:

Change 

$sql="SELECT * FROM $table ORDER BY date LIMIT $start, $amount";
$result = $mysqli->query($sql);

to

$sql="SELECT field_1,field_2,field_n,DATE_FORMAT(format_in,'%l:%i %p') format_in FROM $table ORDER BY date LIMIT $start, $amount";
$result = $mysqli->query($sql);

Upvotes: 1

Related Questions