Update date on mysql doesnt not work help please

PHP
1

Hi i have this code which allows me to retrive the date and change the format that is displayed but now when i use the update form it sets the date back to 0000/00/00

this the retrive code works fine

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

but now how can i do the same to update
i tried just change the SELECT to update but give an error so i wana vice verse the date back to its original format when it send back to mysql databse but keep the format a

Doesnt work help please??


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

if you want to feed in the date to the UPDATE statement in month-day-year format, you have to use the STR_TO_DATE conversion function

UPDATE news
   SET news_date = STR_TO_DATE('[COLOR="Red"]$mynewdatevalue'[/COLOR],'%m/%d/%Y')
 WHERE news_id = $news_id
3

i tried your way i also add the other fields i want update but is not working still sets the date back to 0000/00/00 what did i do wrong

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

okay, we need to starighten this out

if you are able to feed in the new value in year-month-day format, you do ~not~ need the STR_TO_DATE function

secondly, if you want to debug a php query, try echoing it first, then copy/paste the SQL into mysql – i.e. run the query outside of php first

5

i do echo it echos 0000/00/00 back

news_id news_date subject news_artical
70 00/00/0000 sun its bright

<?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


$query = "UPDATE news SET news_date = STR_TO_DATE('$mynewdatevalue','%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     

?>
6

no, what i meant was to echo the query

i think you call it $query

also, by the way, when i wrote $mynewdatevalue, the idea here was that you would substitute–or assign–whatever the new date value is supposed to be

7

this how i got still doesnt work help please

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