How to change the date format??

lisa007

Hi i have a Select query which display the data on the databse currently the on the databse the format is YYY / M/ D how can i change so it be D /M/YYYY

i tried echo "<td>" .$row ("d/m/Y"["news_date"]."</td>";

but did not work help please??

$query = "SELECT * FROM news"; 
$result = mysql_query($query); 

// see if any rows were returned 
if (mysql_num_rows($result) > 0) { 
 // yes so print them one after another 
  echo "<table cellpadding=10 border=1>"; 

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


 echo "<tr>"; 
 echo "<td>".$row["news_id"]."</td>"; 
 echo "<td>".$row["subject"]."</td>"; 
 echo "<td>".$row["news_date"]."</td>";

 echo "<td>".$row["news_artical"]."</td>";
 echo "<td><a href=delete2.php?id=".$row["id"].">Delete</a></td>"; 
 echo "<td><a href=modifyform.php?id=".$row["id"].">Modify</a></td>";
 echo "<td><a href=add.php?id=".$row["id"].">Add Row</a></td>";
 echo "</tr>"; 
   } //End While
   echo "</table>"; 
} //End if rows

dagon

DATE_FORMAT

lisa007

tried but did not succed

help please i tried modify like this still did not work gave error

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\rookierods\modifynews.php on line 37

[code=php]$result = mysql_query("select *,date_format(news_date, '%M %d %Y') as date_string FROM news") or die(mysql_error()); 
$result = mysql_query($query); 

// see if any rows were returned 
if (mysql_num_rows($result) > 0) { 
 // yes so print them one after another 
  echo "<table cellpadding=10 border=1>"; 

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


 echo "<tr>"; 
 echo "<td>".$row["news_id"]."</td>"; 
 echo "<td>".$row["subject"]."</td>"; 
 echo "<td>" .$row["date_string"]."</td>";

 echo "<td>".$row["news_artical"]."</td>";
 echo "<td><a href=delete2.php?id=".$row["id"].">Delete</a></td>"; 
 echo "<td><a href=modifyform.php?id=".$row["id"].">Modify</a></td>";
 echo "<td><a href=add.php?id=".$row["id"].">Add Row</a></td>";
 echo "</tr>"; 
   } //End While
   echo "</table>"; 
} //End if rows

[/code]

dagon

$result = mysql_query($query);

that line has no purpose, and is the source of the error, remove it.

lisa007

how can i change when update to send back the date to normal database format because at the moment it resets the date back to 0000/00/00

$query = "UPDATE news SET news_date = STR_TO_DATE('$new_date','%Y/%m/%d'), subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'";

bradgrafelman

What was the value of $new_date when you tried that code?

lisa007

sets the value to 0000-00-00

lisa007

this is how the whole thing looks like a simple update

<?php 
//Get the key field to be amended
$news_id = $_GET['news_id'] ; 
$news_date = $_GET['news_date'] ; 
$subject = $_GET['subject'];
$news_artical = $_GET['news_artical'];

 // check if there were any errors

// check if there were any errors





 $query = "UPDATE news SET news_date = STR_TO_DATE('$news_date','%Y/%m/%d'),   subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'"; 
// execute query 
print "<p>The following records has been updated:  </p>";
 $result = mysql_query($query) ;
 //if there was a problem - get the error message and go back 
 if (!$result)
  {
     echo "There were errors :<br>". mysql_error();
  } 
  else //OK, then the insertion was successful
  {

//Create a new query to display the new row in a table
$query = "SELECT * FROM news WHERE news_id = '$news_id'";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error()); 
echo "<table cellpadding=10 border=1>";  
while($row = mysql_fetch_array($result)) { 
      echo "<tr>"; 
 echo "<td>".$row["news_id"]."</td>";
 echo "<td><strong>" .$row["news_date"]."</strong></td>";
 echo "<td><strong>".$row["subject"]."</strong></td>"; 
 echo "<td width='55%'>".$row["news_artical"]."</td>";
 echo "</tr>"; 
} //End while
echo "</table>"; 
   } //End Else insertion successful
//End else successful Amendment     

?>

lisa007

help please i have also tried this way but did not work 😕

 $query = "UPDATE, DATE_FORMAT(news_date, '%Y/%m/%d') as new_date news SET news_date='$news_date', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'";