how can i change the format of the date??

[Lisa23]

Hi i've been trying to change the format of the date which comes from the database row [ news-date ]

this what i tried but di not work help please

 

$query = "SELECT * FROM news, date_format [news_date](date,'%M %d, %Y') AS news_date
"; 
$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 

	   echo "<p> <strong>{news.SUBJECT} </strong></p>"; 



?> 

[litebearer]

try this as an experiment

 

$query "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
echo $row['new_date'] . "<br>";
}

[Lisa23]

try this as an experiment

 

$query "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
echo $row['new_date'] . "<br>";
}

 

didnt like this line

$query "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";

[pengu]

http://www.phpfreaks.com/tutorial/working-with-dates-in-php

 

Some great tutorials on this website.

[litebearer]

what is the data type for news_date

[mikosiko]

 

didnt like this line

$query "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";

 

missing = after  $ query

$query = "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";

[Lisa23]

 

didnt like this line

$query "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";

 

missing = after  $ query

$query = "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";

 

thank you very much it working thank you

[Lisa23]

now the modify script sets the date back to 0000/00/00 on the scho i set it likeusing his sintax

$query = "SELECT *, DATE_FORMAT(news_date, '%m/%d/%Y') as new_date FROM news WHERE news_id = '$news_id' ";

how can i have the same way when update because now it sets the date back 0000/00/00

 

this what the update script looks like hwo can i make it so it doesnt set it back to  0000/00/00

$query = "UPDATE news SET news_date = '$news_date', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'";

[litebearer]

what is the data type for your date field? 

 

[Lisa23]

what is the data type for your date field?

 

date on the database  the format is YYYYY/M/D

 

but because i set to display like d/m/yyyy using the

$query "SELECT *, DATE_FORMAT(news_date, '%M %d, %Y') as new_date FROM news";

when i use to update it returns 0000/00/00 on the database

how can i do the same on the on modify date

[PFMaBiSmAd]

There's a complementary function to DATE_FORMAT() called STR_TO_DATE() - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

 

However, since you should be validating the data you receive before putting it into a query, your php code could break apart (explode) the d/m/yyyy values and put them together in the yyyy-mm-dd format before you put it into a query.

[Lisa23]

yah now now i want to do the other way i want just send back to database but because the format on the database is y m d and on the query i have d m y when i try to up date i want to send modified so that it fits back databse criteria

 

i tried like this but did not work help please

 

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

[litebearer]

simple example

$my_date_01 = "3/13/2010"; /* the date in m/d/yyyy format */
$my_date_array = explode("/", $my_date);
$my_date_02 = $my_date_array[2] . "/" . $my_date_array[0] . "/" . $my_date_array[1];
/* $my_date_02 now looks like 2010/3/13 */

 

[Lisa23]

simple example

$my_date_01 = "3/13/2010"; /* the date in m/d/yyyy format */
$my_date_array = explode("/", $my_date);
$my_date_02 = $my_date_array[2] . "/" . $my_date_array[0] . "/" . $my_date_array[1];
/* $my_date_02 now looks like 2010/3/13 */

 

how do i implement that on my update

 

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

[Lisa23]

i have also tried this way no did not work help please :'(

 

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