Update row via HTML form

[keeps21]

A little problem I've run into is the following.

I have a script that allows a user to edit a story.

I have an HTML form for title and main_text which gets it's values by pulling the selected data from the database.

If the user either

i) doesn't change anything, and then saves.
or
ii)enters the exact same text.

[php]if (mysql_affected_rows() == 1) [/php]

fails, so an error message is output.

But in fact there is no error, as everything ran fine.

Hopefully I explained that alright.

Do you have any ideas on how to ffix this problem?

Thanks

Here is the full code
[php]
<?php
# Filename - edit_story.php
# Date - 9th August 2007
# Author - Stephen Hoult
# Author Email - [e-mail]

// This file allows logged in users to edit a story.

// Include config file for error management and such
include('./includes/config.inc.php');

// Set page title and include HTML header
$page_title = 'Edit a Story';
include('./includes/header.html');


// If no first_name variable exists, redirect the user.
if (!isset($_SESSION['first_name'])) {

// Start defining the URL.
$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
// Check for a trailing slash.
if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
$url = substr ($url, 0, -1); // Chop off the slash.
}

// Add the page.
$url .= '/login.php';

ob_end_clean(); // Delete the buffer.
header("Location: $url");
exit(); // Quit the script.

} else { // First name variable exists - user is logged in


// Connect to the Database
require_once('../mysql_connect_ur.php');


if (isset($_POST['submitted'])) { // if the form has beeen submitted

// Get story ID
if (isset($_POST['id'])) {
$id = escape_data($_POST['id']);

} else {
$id = FALSE;
}

// Handle the form

// Validate title
if(!empty($_POST['title'])) {
$t = escape_data($_POST['title']);
} else {
echo '<p><font color="red" size="+1">Please enter a title.</font></p>';
}

// Validate main text
if(!empty($_POST['main_text'])) {
$mt = escape_data($_POST['main_text']);
} else {
echo '<p><font color="red" size="+1">Please enter the main text.</font></p>';
}


// Check that the ID exists in the database.
// Build query
$query = "SELECT id, title, main_text FROM content WHERE id = $id";
$result = mysql_query($query);
$num = mysql_num_rows($result);



if ($num == 1) { // If a row was found by the query - ie the story id exists in the table.

// Update the database
$query = "UPDATE content SET title='$t', main_text='$mt' WHERE id = $id";
$result = mysql_query($query);


// Check to see if update was successful
if (mysql_affected_rows() == 1) { // If 1 row was affected by the update
echo '<p>The story has been edited.</p>';

} else {
echo '<p><font color="red" size="+1">The story could not be updated. Please try again</font></p>';
}// End If update was successful


} else {
echo '<p><font color="red" size="+1">The selected ID does not exist in the database. Please try again.</font></p>';
} // End mysql_num_rows() to see if id exists in the table.


} else { // The form has not yet been submitted - Display the form

// Get story ID
if (isset($_GET['id'])) {
$id = escape_data($_GET['id']);

} else {
$id = FALSE;
}

$query = "SELECT id, title, main_text FROM content WHERE id = $id";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
?>



<fieldset><legend>Edit a story</legend>

<form action="edit_story.php" method="post" >

<p>Title: <input type="text" name="title" size="30" maxlength="50" value="<?php if (isset($_POST['title'])) {echo $_POST['title'];
} else { echo $row['title'];} ?>" /></p>

<p>Main Text<textarea name="main_text" cols="40" rows="5"/><?php if (isset($_POST['main_text'])) {echo $_POST['main_text'];
} else { echo $row['main_text'];} ?></textarea></p>

<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="id" value="<?php $_POST['id'] = $id; echo $_POST['id'] ;?>">


</form>

</fieldset>

<?php
}// End of if is submitted

} // End of is logged in

include('./includes/footer.html'); // Include HTML footer
?>[/php]

[keeps21]

I assume one way to do this would be to add a field 'modified_on' as a timestamp, to my database and add the appropriate values to the query.

Then when updated, even if none of the title, or main text changes, the 'modified_on' field would, so the error would not be kicked out.

[Atli]

Try changing

if(mysql_affected_rows() == 1)
 

To

if($result)
 

If a query failes, the $result is always FALSE. But when a query is successful it either retuns data, or it retuns TRUE which will both be evaluated as true in an if statement.

[keeps21]

Thanks that works great.

I tried adding a timestamp as i thought about in post #2 and updating it when the query runs and it also worked great.

[Atli]

Glad I could help.

The timestamp thing is a good idea. You could add a 'Last edited at' thing to your stories :)

[keeps21]

Yeah that was what I thought when I was thinking about adding the TIMESTAMP.

Although in hindsight it may be easier in the long run to use DATETIME instead of TIMESTAMP .

This would save having to convert back and forth between the two whe outputing the Last modified date and time.

Thanks for your help. :)