mysql UPDATE not working! Why?

[nathanpacker]

Very simple code, I even echo the variables to make sure they are comming through:

<?
include 'config.php';

$recordid = $_POST['recordid'];
$im=$_POST['im'];
$bm=$_POST['bm'];
$peachtree=$_POST['peachtree'];
$canapprove=$_POST['canapprove'];
$cancomment=$_POST['cancomment'];
$canclose=$_POST['canclose'];
$administrator=$_POST['administrator'];

echo $im;
echo $bm;
echo $peachtree;
echo $canapprove;
echo $cancommnet;
echo $canclose;
echo $administrator;
echo "<p>";


$result = mysql_query("UPDATE employees SET im='$im', bm='$bm', peachtree='$peachtree', canapprove='$canapprove', cancomment='$cancomment', canclose='$canclose', administrator='$administrator' WHERE RECORDID='$recordid'")
or die('sorry, no query');


 echo "Success";
 
 ?>

It executes the code, and I get a success, with no query errors. But the database is not being updated. Any idea to what I'm doing wrong? Oh, and the variables are echoing out the correct values.

[stymiee]

Do you have access to phpmyadmin or the command line for mysql? If so, echo out your query and paste it directly into mysql. It should tell you the reason why the update isn't working. mysql_error() should tell you this also even if die() isn't working.

[Puckdropper]

You can set your query in a variable and then echo that out. I don't think that will help, but it's another option.

[nathanpacker]

Do you have access to phpmyadmin or the command line for mysql? If so, echo out your query and paste it directly into mysql. It should tell you the reason why the update isn't working. mysql_error() should tell you this also even if die() isn't working.

Thanks. Yes, I do have access to the mysql database, and I have tried running the query directly in the command line, and didn't get any errors. That's why this is so boggling!

[stymiee]

That is mind boggling. I guess at this point I can only wish you luck. :|

[nathanpacker]

Thanks for all your ideas. I figured it out. First, the reason my tests were working when I would paste the code into the mysql command line was because I knew I couldn't use the variables in there, so I replaced them all with numbers, so of course it would work.

And I was echoing out all the variables except for one. I was echoing all the variables that I was updating on the row, but not the one that defines WHICH row to update. The one after the WHERE! Anyway, I tried echoing that out, and realized that it wasn't getting passed from the form. Then I realized, it's not actually part of the form, I needed to pass it on using a hidden input field, and forgot to do that.

So, long story short, I wasn't echoing all my variables. Problem solved!

[vimalsaifudin]

Hai,

I am using AJAX with PHP. I have grid and each row have an edit button on clicking it shows textbox controls and update button. On clicking update button it updates the database and get the updated date and refills the grid. But after refilling the grid, my first row of the grid never getting updated or deleted while the rest rows are performing well.

This is my javascript code:

function UpdateLanguageData(email,type)
{

    var http = new XMLHttpRequest();    
    var url ='AjaxPOST.php';
    var SkillSet="";


    var params = "email="+email+"&value=&type="+type+"&amountLanguage="+document.getElementById('amountLanguage').value+"&txtLanguageTitle="+document.getElementById('txtLanguageTitle').value+"&langCount="+document.getElementById('LanguageAdder').getElementsByTagName('table').length; 
    http.open("POST", url, true);

    //Send the proper header information along with the request
    http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    http.setRequestHeader("Content-Type", "text/html;charset=UTF-8");
    http.setRequestHeader("Content-length", params.length);
    http.setRequestHeader("Connection", "close");
    http.onreadystatechange = function() 
    {//Call a function when the state changes.
        if(http.readyState == 4 && http.status == 200)
         {
           if(this.responseXML != null)
            RetrieveLanguages(http.responseText);
         }
     }
      http.send(params);
}


function RetrieveLanguages(Response,mode)
{
    if(Response!='')
    {

     var Arr = Response.split('|');
     var SubArr = "";
     var data="";

     while (document.getElementById('LanguageAdder').hasChildNodes()) {
      document.getElementById('LanguageAdder').removeChild(document.getElementById('LanguageAdder').firstChild);
     }
     //alert(document.getElementById('RefereeAdder').innerHTML);
     for(var i=0;i<Arr.length;i++)
     {
       SubArr = Arr[i].split('_');
       SubArr[0] = (SubArr[0]).replace(/^\s*|\s*$/g,'');
       SubArr[0] = (SubArr[0]).replace(/\r\n|\r|\n/g,'');

                data +=  "<table id='LanguageGridRow_"+SubArr[0]+"' border='0' class='skillBorder' cellspacing='0'  cellpadding='0' width='100%'><tr>"
        + "<td width='180px'><label id='lblTitle_"+SubArr[0]+"' >"+SubArr[1]+"</label><input style='display:none' type='text' name='LanguageNamer_"+SubArr[0]+"' id='LanguageNamer_"+SubArr[0]+"' value='"+SubArr[1]+"'/></td>"
        + "<td width='180px'><label id='LanguageAmounter_"+SubArr[0]+"'>"+SubArr[2]+"</label><div id='selectedLanguageAmount"+SubArr[0]+"' style='display:none;padding:5px'><input type='text' value='"+SubArr[2]+"' id='amountLanguage"+SubArr[0]+"' class='sliLabel' readonly='readonly' /></div><div class='sliderDisplay2' id='language_up_slider"+SubArr[0]+"' style='display:none'>  </div>   </td>"
        + "<td width='60px'><a id='EditLanguage_"+SubArr[0]+"' title='Edit' onclick=\"EditLanguage('"+SubArr[0]+"')\"><img src='../images/edit.png'  /></a><a  id='UpdateLanguage_"+SubArr[0]+"' title='Save Changes' style='display:none' onclick=\"UpdateLanguageRow('"+SubArr[0]+"','"+SubArr[3]+"')\"><img src='../images/floppy.png'  /></a><a style='display:none'  id='CloseLanguage_"+SubArr[0]+"' title='Close Language' onclick=\"CloseLanguage('"+SubArr[0]+"')\"><img src='../images/Delete.png'  /></a></td><td width='60px'><a  id='DeleteLanguage_"+SubArr[0]+"' title='Delete Language' onclick=\"DeleteLanguage('"+SubArr[0]+"','"+SubArr[3]+"')\"><img src='../images/recycle.png'  /></a></td>";

     }

     document.getElementById('LanguageAdder').innerHTML = data;




    }
}

This is my php function

function updateLanguage($email_address, $LanguageTitle, $amountLanguage, $id)
{
 $query ="UPDATE ag_languages set language_title = '".$LanguageTitle."' , language_level= '".$amountLanguage."' where email_address = '".$email_address."' and language_id = '".$id."' LIMIT 1";
 mysql_query($query) or die (mysql_error());


}

This was how I update my data and regenerate the grid rows. But strange thing is when I alert the query it looks all the variable values are fine but when u alert the mysql_affected_rows() it shows 0 only for the topmost row and for all the rest rows it works fine and mysql_affected_rows() shows 1.

I did not find any mistakes in my code. But can anyone help me why this strange issue happens