Insert works but Update doesn't?

[Kathy_OC]

I am trying to update a record in a MySQL database and then redirect to another page. I can create the record, but can't seem to figure out how to update it and redirect to the next page. Sections of code that I have tried are below.

// This doesn't work (fields are blank but it redirects)
$query = "UPDATE request SET Technology='$Technology', Description='$Description' WHERE RequestID = " . $RequestID;
mysql_query($query);
header('Location: http://example.com/php/CostInfo.php');
exit();

// This works but creates a new record (of course)
$query = "INSERT INTO request (Technology, Description, DateSubmitted) VALUES ('$Technology','$Description', now())";
mysql_query($query);
header('Location: http://example.com/php/CostInfo.php');
exit();

// This updates the fields, but doesn't redirect to the next page
$query = "UPDATE request SET Technology='$Technology', Description='$Description' WHERE RequestID = " . $RequestID;
mysql_query($query);

I'm a newbie and feel like I must be missing something obvious, but what? I would appreciate any help or advice about how to troubleshoot this.

Thanks,
Kathy

[edited by: Kathy_OC at 1:15 pm (utc) on Mar. 31, 2008]

[edited by: coopster at 2:43 pm (utc) on Mar. 31, 2008]
[edit reason] used example.com for domain [/edit]

[d40sithui]

hmm the code and query looks valid to me!
you say that the fields are blank with the top query. how are you retrieivng the field data? have you echoed them out to make sure that they are not empty?

[bilenkyj]

Instead of the redirect function use

<meta HTTP-EQUIV="REFRESH" content="0; url=<?php echo"theurl";?>">

[Kathy_OC]

Thanks for the replies.

I'm retrieving the data with:
$Technology = htmlspecialchars($_POST['txtTechnology']);
$Description = htmlspecialchars($_POST['txtDescription']);

When I've echoed the fields out to the screen, they are not empty.

The meta redirect function, redirects the page immediately to the next page without allowing the user to enter information. What I'm trying to do is:
the user enters data to update a record that already exists
the user clicks on the submit button
the database is updated
the next screen is displayed

Or am I just putting that function in the wrong place? I placed it near the top of my page.

Kathy

[bilenkyj]

have a hidden field in the form called trigger or soemthing - give it the value of "go" - then have your update function and url redirect within an if statement

if(trigger=="go"){
update db

redirect
}

The update and redirect will then not feature until the form is submitted

[d40sithui]

damn i was hoping that was the culrit, but of course, it's never this easy right? lol.
i think i understand your process.
what i don't get is why your third query works, but the first does not. They are the same! which makes no sense.
i would try this on your action script. after you've retrieved your data and checks that it works, and validate it etc.

<?
$query = "UPDATE request SET Technology='$Technology', Description='$Description' WHERE RequestID = " . $RequestID; 
$result = mysql_query($query);
//if rows updated - redirect to next page
if($result && mysql_affected_rows()){ 
header('Location: http://example.com/php/CostInfo.php');
}
//update failed - return to previous form
else{
header('Location: http://example.com/php/form1.php');
}
?>

i guess another thing u could do is copy and paste the third query since it works. add underneath it the header function. other than that i'm out of ideas on this one. if you can't get it to work, please post your scripts starting with the form and the action script for it.

[Kathy_OC]

I really appreciate both of your suggestions, but it's still not working. The problem seems to be when I use both the update and the redirect. The update works fine if I don't redirect.

I found out some more information though. When I redirect, my database is being updated, but the text fields have blank information. I added a date/time stamp to my update query and that field is updated to the current date/time. As I said earlier, I can echo the fields to the screen and the echo displays data. But somehow the redirect makes the fields update to blank? That doesn't make sense to me, but that's what appears to be happening. Could there be something in my MySQL database/field definition that would cause this?

Thanks again for your help,
Kathy

[deMorte]

Have you tried omitting the exit(); from your script? This is just a shot in the dark, but maybe it'll help.
You could also try formatting the update query like this:

$query = "UPDATE request SET Technology='".$Technology."', Description='".$Description."' WHERE RequestID = ".$RequestID; 

[Kathy_OC]

Well, I tried both of those ideas, but no luck. Thanks for the suggestions.

Kathy

[Kathy_OC]

There was a request from d40sithui to post the code, so here it is. (It looks like my code didn't indent/format when I posted it here - sorry if it's hard to read)

Thanks for taking a look at it.
Kathy

<?php
error_reporting(E_ALL);
session_start();

require_once("dataconn.php");

if(isset($_SESSION['varRequestID']))
{
$RequestID = $_SESSION['varRequestID'];
echo "RequestID is: " . $RequestID;
} else {
echo "<h2>ERROR: Request number has not been found.</h2>";
echo "<h2><a href='index.php'>Return to Home Page</a></h2>";
// should not display the form
exit();
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// The request is a POST, so validate the form
echo "<br><b>form has been submitted - " . $_SERVER['SCRIPT_NAME'] . "</b><br>";
$errors = validate_form();
if (count($errors)) {
// If there were errors, redisplay the form with the errors
display_form($errors);
} else {
// The form data was valid, so save data to database and
// display next data entry screen

// update record in database
//$Technology = htmlspecialchars($_POST['txtTechnology']);
//$Description = htmlspecialchars($_POST['txtDescription']);

$Technology = mysql_real_escape_string($_POST['txtTechnology']);
$Description = mysql_real_escape_string($_POST['txtDescription']);
echo "<br>" . $Technology . "<br>";
echo $Description . "<br>";

//$query = "UPDATE request SET Technology='".$Technology."', Description='".$Description."', DateSubmitted=now() WHERE RequestID = ".$RequestID;
$query = "UPDATE `request` SET `Technology`='".mysql_escape_string($_POST['txtTechnology'])."', `Description`='".mysql_escape_string($_POST['txtDescription'])."', DateSubmitted=now() WHERE `RequestID` = '" . $RequestID."'";

echo "query: " . $query . "<br>";
$result = mysql_query($query);
echo "result: " . $result . "<br>";

//if($result){
if($result && mysql_affected_rows()){
echo "Should have worked";
//echo "<meta HTTP-EQUIV='REFRESH' content='0; url=CostInfo.php'>"
header('Location: http://example.com/php/CostInfo.php');
exit();
}else{
echo "There has been an error." . mysql_error();
}
}
} else {
// display the form
display_form(array());
}

function display_form($errors) {

// Set up defaults
$defaults['txtTechnology'] = isset($_POST['txtTechnology']) ? htmlentities($_POST['txtTechnology']) : '';
$defaults['txtDescription'] = isset($_POST['txtDescription']) ? htmlentities($_POST['txtDescription']) : '';

if (isset($_SESSION['varRequestID']))
{
$RequestID = $_SESSION['varRequestID'];
echo "<h2>Request Number: " . $RequestID . "</h2>";
} else {
echo "session variable has not been set <br>";
}

>

<p>Instructions: The technology and description fields are required. </p>
<form name="frmTechRequested" method="post" action="<?php echo $_SERVER['SCRIPT_NAME'] ?>">
<p>
<label for="txtTechnology">Technology:</label>
<input name="txtTechnology" type="text" id="txtTechnology" size="60" value='<?php echo $defaults['txtTechnology'] ?>'>
<?php print_error('txtTechnology', $errors) ?>
</p>
<p>
<label for="txtDescription">Description:</label>
<textarea name="txtDescription" cols="60" rows="3" id="txtDescription"><?php echo $defaults['txtDescription'] ?></textarea>
<?php print_error('txtDescription', $errors) ?>
</p>
<table width="90%">
<tr>
<th>Component</th>
<th>Description</th>
<th>Cost</th>
<th>Vendor</th>
</tr>
<?php

$MaxComponents = 10;
for ($NumComponents = 1; $NumComponents < $MaxComponents + 1; $NumComponents++)
{
echo "<tr>\n";
echo "<td><input name='txtComponent" . $NumComponents . "' type='text' id='txtComponent" . $NumCompenents . "'></td>\n";
echo "<td><input name='txtDescription" . $NumComponents . "' type='text' id='txtDescription" . $NumCompenents . "'></td>\n";
echo "<td><input name='txtCost" . $NumComponents . "' type='text' id='txtCost" . $NumCompenents . "'></td>\n";
echo "<td><input name='txtVendor" . $NumComponents . "' type='text' id='txtVendor" . $NumCompenents . "'></td>\n";
echo "</tr>\n";
}

?>
</table>
<p>&nbsp;</p>
<p>&nbsp;&nbsp;&nbsp;
<input type="submit" name="Submit" value="Next Screen &gt;&gt;" id="Submit">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="reset" name="Cancel" value=" Cancel " id="Cancel" onClick="document.location='index.php'">
</p>
</form>
<p>&nbsp;</p>

<?php
}// end of display_form function

// A helper function to make generating the HTML for an error message easier
function print_error($key, $errors) {
if (isset($errors[$key])) {
// ***** SHOULD ADD ERROR CLASS TO CSS *****
print "&nbsp;&nbsp;&nbsp;**&nbsp;{$errors[$key]}&nbsp;**";
}
}

function validate_form() {
// Start out with no errors
$errors = array();

// technology is required
if (! (isset($_POST['txtTechnology']) && (strlen($_POST['txtTechnology']) > 0))) {
$errors['txtTechnology'] = 'Please enter a technology';
}
// description is required
if (! (isset($_POST['txtDescription']) && (strlen($_POST['txtDescription']) > 0))) {
$errors['txtDescription'] = 'Please enter a description';
}

return $errors;
}

?>

[d40sithui]

After reading over your script, i think i see some potential errors.
1.) After your function display_form, you did not end php code correctly. you're missing a "?" - should be "?>" remember? lol.
2.) it looks like you're using the same page to display the form and also to process it. though this is an efficient method of coding, you're probably better off creating a new script to handle your form. this way, you can narrow down your errors more easily by accessing specific scripts.
3.)before your query, you have some echos. remember, header() will not work if there are ANY output before it. that means, you will need to remove these lines:
echo "query: " . $query . "<br>";
echo "result: " . $result . "<br>";
echo "<br>" . $Technology . "<br>";
echo $Description . "<br>";
echo "Should have worked";
4.) again, this page has a lot of stuff crammed into it. your error is most likele related to something small and overlooked. If you still cannot find the problem, create a basic form. then create a basic action script. dont include anything else in the action script but the update and header. that means no output.
//cant get any more simple than this
<?
//connect to db
//get post vars.
//updates db
//on success header forward
//on failure header back
?>

[Kathy_OC]

Thanks for taking a look at my script and for your feedback.

I created a basic form like you suggested and got the same behavior.
I don't know how that "?" turned up missing - it's in my code. I must have hit something when I was cutting/pasting.

I found out some more information since my last post. The update and redirect work fine if I redirect to a HTML page. It also works if I redirect to a PHP page using the IP address of the server instead of the name (although I lose my session variable when I do that). So I'm thinking that this is a PHP bug. I've updated to the latest version of PHP (5.2.5) and that hasn't helped, but I am running this on IIS and I've been reading similar bug reports on php.net. Though none of the suggestions in the bug reports have helped yet.

Anyway, thanks again for all of your help.

Kathy

[jatar_k]

I don't think this is a php bug since all of these things work nicely for most people and I have done the same on *nix servers a million times. It could be an issue with IIS but even that seems odd.

did you completely split form and function as suggested?

if the update query works, which it seems to in some cases then it should always work. The redirect happens after so that doesn't have anything to do with the update.

if you redirect to the same page then you might be double executing your update query and since the values were not posted then you are updating to blank.

[Kathy_OC]

I agree with you that what I'm seeing doesn't make sense. But when I change my redirect to an IP address or htm page then the update works perfectly. If I redirect using
header('Location: http://example.com/php/CostInfo.php');
then the blanks are saved to the database.
I am redirecting to a different page.

This is one of the bug reports I have been looking at:
[bugs.php.net...]
It doesn't quite fit my situation, but it's similar. And it is unclear if this is an issue with PHP or IIS.

Kathy

[edited by: dreamcatcher at 1:57 pm (utc) on April 13, 2008]
[edit reason] Use example.com, thanks. [/edit]

[jatar_k]

is your php installed as cgi?

[skyblue]

Hi. I am wondering if you have solved your problem, as I am having a similar issue too.

[Kathy_OC]

No, I haven't solved it yet. The next thing I'm going to try is to test out my app with IIS 6.0 and FastCGI. I'm hoping to do that this week. I've been working with IIS 5.0 and tried both CGI and ISAPI.

For my app, it seems to be a problem with the combination of my session variable, update and header/redirect. If I eliminate any one of the three, then my code works.

What is your issue like? What operating system and web server are you working with?

Kathy

[skyblue]

Hi Kathy,

I found out the culprit of my problem is the missing favicon.ico and apache ErrorDocument 404 setting. My settings (LAMP) are pretty different from yours, but it is similar in the sense that it is tied to sessions, mysql update and header redirect.

Essentially, the header/redirect fired up and favicon.ico is being fetched before the session has been saved (in mysql db). On my server, a missing file (404) load a php script and session values have been fetched from db way too early, and later being overwritten in db with empty values, like the update has never happened.

But probably this is a total different issue than yours.