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' "; PHP: 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 Doesnt work help please?? $query = "UPDATE*, DATE_FORMAT(news_date, '%m/%d/%Y') as new_date FROM news WHERE news_id = '$news_id' "; PHP:
'update news set news_date = '.$my_date_value.' where news_id = '.$news_id Your $my_date_value variable should be a string in the form 'YYYY-MM-DD HH:MM:SS'
i tried like this but did not work $query = "UPDATE news SET news_date='.$my_date_value.', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'"; PHP: I dont know if its because on a different form the echo i set it the format to be d/m/YYYY so when i click the modify it echos the date in this format but i just want to the update to send in the database in the the normal format but when it echos display in the format i want so i wonder if i cant have it to display in one way and send to database in another??
When running your query use: mysql_query($query) or die(mysql_error()); PHP: So that it outputs an error so you can see exactly where it is going wrong and work from there, if you are still struggling let us know the error as it will help us to pinpoint it easily. You have to ensure the values you are submitting in your update are of the correct type and size for the field you are trying to insert them into. Also just for your reference: MySQL Update Tutorial and MySQL Select Tutorial
i had that line u told me but still brings the date back to 0000/00/00 $query = "UPDATE news SET news_date ='$news_date', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'"; mysql_query($query) or die(mysql_error());
You don't appear to be changing the way $news_date is formatted before it goes back into the mysql statement to update. A, show us an example of what an unaltered date looks like in a mysql table B, show us the code you use to change it, for visual use. C, show us the current unaltered update query. ..and finally just clarify what you want back in the db On a side note, if your update is to be noted every time you make changes, you can always use, either of these: ie: pick the one you want. $today = date("F j, Y, g:i a"); // March 10, 2001, 5:16 pm $today = date("m.d.y"); // 03.10.01 $today = date("j, n, Y"); // 10, 3, 2001 $today = date("Ymd"); // 20010310 $today = date('h-i-s, j-m-y, it is w Day'); // 05-16-18, 10-03-01, 1631 1618 6 Satpm01 $today = date('\i\t \i\s \t\h\e jS \d\a\y.'); // it is the 10th day. $today = date("D M j G:i:s T Y"); // Sat Mar 10 17:16:18 MST 2001 $today = date('H:m:s \m \i\s\ \m\o\n\t\h'); // 17:03:18 m is month $today = date("H:i:s"); PHP: $query = "UPDATE news SET news_date='$today' WHERE news_id = '$news_id' "; PHP:
Yeah I agree if the row is successfully updating but the news_date field for this row is now showing 0000/00/00 after the update, it's definitely to do with the format of your $news_date variable, as suggested above we can help further if you post more info!
2 scripts the form and the action script after press submit hi this is the update form you notice on the SELECT query i have like this $query = "SELECT news_id, news_date, subject, news_artical, DATE_FORMAT(news_date, '%m/%d/%Y') as new_date FROM news WHERE news_id = '$news_id' "; PHP: so that the date can be display like 12/30/2010 works fine but update gives error the update form <?php define('ROOT_DIR', './'); define('THIS_SCRIPT', 'news'); define('PROPER', TRUE); /** * include common files */ include_once(ROOT_DIR. 'includes/common.inc.php'); ?> <?php // query to get records $news_id = $_GET['news_id'] ; // create query to delete record $query = "SELECT news_id, news_date, subject, news_artical, DATE_FORMAT(news_date, '%m/%d/%Y') as new_date FROM news WHERE news_id = '$news_id' "; //Run the query $result = mysql_query($query); //see if any rows were returned if (mysql_num_rows($result) > 0) { // yes - Display Form $row = mysql_fetch_array($result); //Fetch the row //Display the form with original values ?> <form action = 'modifynewsaction.php' method="GET" enctype="multipart/form-data"> <p>ID: <input readonly="yes" name="news_id" type="text" value=<?php print $row["news_id"] ?> size="5" > This has been made 'readonly' as cannot amend key field </p> <p>Date: <input type="text" name="new_date" value=<?php print $row["new_date"] ?> ></p> <p>Subject: <input type="text" name="subject" value=<?php print $row["subject"] ?> ></p> <p>Artical: <input type="text" name="news_artical" value=<?php print $row["news_artical"] ?> ></p> <p><input type="submit" value="Submit" name="Update" > </p> </form> <?php } //End if rows returned //No rows returned else print "No records were found"; ?> </body> </html> PHP: and this is the action form when press submit <?php define('ROOT_DIR', './'); define('THIS_SCRIPT', 'news'); define('PROPER', TRUE); /** * include common files */ include_once(ROOT_DIR. 'includes/common.inc.php'); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> </head> <body> <h1 align="center"><a href="add.delete.modify.php">Back</a> Admin Center <a href="../orange.php">Home</a></h1> <h3 align="center">Logged in as <b><?php echo $session->username; ?> <a href="../process.php">Logout</a></h3></b></font><br><br> <h2>RECORD MODIFICATION HAS BEEN COMPLETED </h2> <p> <?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 //$news_date = STR_TO_DATE('$news_date','%Y/%m/%d'); $query = "UPDATE news SET news_date = '$news_date', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'"; mysql_query($query) or die(mysql_error()); // 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 news_id, news_date, subject, news_artical, DATE_FORMAT(news_date, '%m/%d/%Y') as new_date 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 ?> </body> </html> PHP:
Hi i used this format it inserts current date how do i change so it inserts the date user types in?? $news_date = date("Y/m/d"); $query = "UPDATE news SET news_date = '$news_date', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'"; PHP:
Maybe: if (empty($news_date)){ $news_date = date("Y/m/d"); } $query = "UPDATE news SET news_date = '$news_date', subject='$subject', news_artical='$news_artical' WHERE news_id = '$news_id'"; PHP:
That date is not coming from the example I gave you above, so that means that $news_date is not empty and its getting a date from somewhere else. EDIT:Where is the STR_TO_DATE function maybe thats what we should be looking at.