Lost Variable when using mysql_query to update a text field.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rpo
Forum Newbie
Posts: 6
Joined: Sat Feb 13, 2010 3:05 pm

Lost Variable when using mysql_query to update a text field.

Post by rpo »

Code: Select all

 
function update_rules ($tid) {
    $rules = mysql_real_escape_string(stripslashes(htmlspecialchars($_POST['rules'])));
    $sql = sprintf("insert into T_Rules set rules='%s', tid=%d, updated=NOW() on duplicate key update rules='%s'",$rules,$tid,$rules);
    mysql_query($sql);
    
}
 
Nothing fancy here which is why this is causing me so much grief. The $rules values which is the cause the problem is from a textarea field from the form that is being processed, and the target location is a text field in the database.

Here is the problem: on updates the rules field is blank after the query executes.

Here is what I have tested so far:
  • The query reports no error.
    If I hard code the the value for $rules in the "on duplicate key" section, it properly updates the field.
    If I hard code the the value for $rules as the third parameter for sprintf, it properly updates the field.
    When I echo the sql statement, the sql statement shows that the $rules value is being added at all desired positions in the string.
    If I echo the sql statement and run it through phpMyAdmin, it properly updates the code.
    If i rewrite the sql without the "on duplicate key" section it will properly create a record with the correct $rules value, however when running it with the "on duplicate key" section the rules value is blank even when a new row is created.
    If I rewrite the sql as an update query, it continues to have the same problem.
    I have checked user permissions and that is not a problem ... even with full permissions the problem is occurring.
    I can echo $rules and the value will appear ... even after the mysql_query statement.
    I have also tried creating a seperate variable for the "on duplicate key" section, it has the same problem.
I think I have everything I tried listed there. So why is mysql_query failing to update the field properly when it runs an update query in this case. I've never had this problem before.

Hopefully, I'm overlooking something simple.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Lost Variable when using mysql_query to update a text field.

Post by Eran »

You don't need to repeat the value in the update part of an ON DUPLICATE statement.
[sql] ... ON DUPLICATE KEY UPDATE rules=VALUES(rules) ...[/sql]

Did you check the return value of mysql_query?

Code: Select all

$result = mysql_query($sql);
var_dump($result);
if($result === false) {
    echo mysql_error();
}
rpo
Forum Newbie
Posts: 6
Joined: Sat Feb 13, 2010 3:05 pm

Re: Lost Variable when using mysql_query to update a text field.

Post by rpo »

Thanks for your reply.

The query isn't reporting an error. The var_dump on $result displays bool(true).

Its not that the query is failing, but rather it is not updating the field with the data that is being passed with the $rules variable. Instead it updates the rules field with an empty string.

It does the same thing if I try to run an update query. It updates with an empty string instead of the value stored in $rules.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Lost Variable when using mysql_query to update a text field.

Post by Eran »

Did you try to change the query update part to what I've shown you?
try to var_dump the $rules variable separately. What does it contain?
rpo
Forum Newbie
Posts: 6
Joined: Sat Feb 13, 2010 3:05 pm

Re: Lost Variable when using mysql_query to update a text field.

Post by rpo »

Modifying the query didn't change the behavior.

var_dump on $rules reports the string length and the string I'm passing from the form. i.e. string(4)'fish'.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Lost Variable when using mysql_query to update a text field.

Post by Eran »

try using mysqli instead and see if there's any change
rpo
Forum Newbie
Posts: 6
Joined: Sat Feb 13, 2010 3:05 pm

Re: Lost Variable when using mysql_query to update a text field.

Post by rpo »

More info: I was able to 'fix' the problem.

Originally I was submitting form using apache's mod_rewrite to format the action on the server. I had the method set as post, but was still dealing with some $_GET vars from the rewrite.

I reworked the form with hidden fields and basically bypassed the rewrite function and the updates work now. Still doesn't explain the odd behavior with the rewrite losing the variables on sql updates but not inserts. any ideas as to what may be causing this. I just started playing around with mod_rewrite last week and really haven't been doing anything to spectacular with it.

I would prefer to use mod rewrite with my forms to eliminate the hidden fields. Does anyone have any suggestions or insight regarding this problem?
rpo
Forum Newbie
Posts: 6
Joined: Sat Feb 13, 2010 3:05 pm

Re: Lost Variable when using mysql_query to update a text field.

Post by rpo »

I little more research on this and I was able to lock down the problem.

Apache is requesting the page twice. The second time sans the post variables. So on the first pass ... the insert, it would put in the record correctly ... then on the second pass (an update) it would enter an empty string.

It would work with just the insert (without the on duplicate statement) becuase on the second pass it would simply not do anything due to duplicate key.

I thought I had a fix for this ... but alas ... [NS] doesn't seem to work. SO at the moment I am stuck with the hidden fields.
rpo
Forum Newbie
Posts: 6
Joined: Sat Feb 13, 2010 3:05 pm

Re: Lost Variable when using mysql_query to update a text field.

Post by rpo »

another update on this problem.

It appears that this was a browser issue. I found that some of my other php scripts were executing twice. Apparently CSS errors will cause some browser to request the page twice. I was able to fix another script that was exhibiting the same behavior and that is most likely the case of this one too.

Just something else to check if you are experiencing this problem.
Post Reply