ajax returns success but mysql db not updated

Question

I have a javascript for loop that sends an array to an ajax page to update the mysql database.

I echo the result back to the original page and it echos as a success but when I check the db nothing has changed

my javascript for loop that sends the array

for(var m=0; m<array.length; m++){
    $.post("update_page_positions.php",{page_ref:array[m][0], ref:array[m][12], menu_pos:array[m][1], sub_menu_pos:array[m][2], top_menu:array[m][3], pagelink:array[m][4], indexpage:array[m][5], hidden:array[m][6], page_title:array[m][7], page_desc:array[m][8], page_keywords:array[m][9], page_name:array[m][10], deletedpage:array[m][11]},         
        function(data,status){  
            alert("data="+data+" status="+status);   
});

here is the php ajax page the updates the db

<?
    include("connect.php");     
    $ref = $_POST['ref'];
    $page_ref = $_POST['page_ref'];
    $menu_pos = $_POST['menu_pos'];
    $sub_menu_pos = $_POST['sub_menu_pos'];  
    $top_menu = $_POST['top_menu'];
    $indexpage = $_POST['indexpage'];       
    $page_name = $_POST['page_name'];    
    $page_title = $_POST['page_title'];
    $page_desc = $_POST['page_desc'];   
    $page_keywords = $_POST['page_keywords'];   
    $hidden = $_POST['hidden']; 
    $pagelink = $_POST['pagelink']; 
    $deletedpage = $_POST['deletedpage'];   

    $query = mysql_query("SELECT * FROM pages WHERE ref='$ref' AND page_ref='$page_ref'");

 if(mysql_num_rows($query)==0){
    mysql_query("INSERT INTO pages(page_ref, ref, page_name, menu_pos, sub_menu_pos, top_menu, link, indexpage) VALUES('$page_ref','$ref','$page_name','$menu_pos','$sub_menu_pos','$top_menu','$pagelink','$indexpage')");
}

 if($deletedpage=="1"){
        mysql_query("DELETE FROM pages WHERE ref='$ref' AND page_ref='$page_ref'");         
        mysql_query("DELETE FROM site_content WHERE ref='$ref' AND page_ref='$page_ref'");
 } 
 else{       
       if(mysql_query("UPDATE pages SET menu_pos='$menu_pos', sub_menu_pos='$sub_menu_pos', top_menu='$top_menu', indexpage='$indexpage', page_name='$page_name', page_title='$page_title', desc1='$page_desc', keywords_list='$page_keywords', hidden='$hidden', link='$pagelink' WHERE ref='$ref' AND page_ref='$page_ref'")){

        echo "updated!";
     } else{
        echo "error";           
      }

 }   
?>

the INSERT and DELETE functions are fine but the UPDATE returns a success statement but does not update the db.

Can anyone see what the problem is?

Once you've solved your problem, you'd do well to look at sanitizing your data: http://stackoverflow.com/questions/129677/whats-the-best-method-for-sanitizing-user-input-with-php — Grim..., Jul 31 '13 at 15:04
There is a fundamental problem in your code: You fire multiple AJAX calls from a loop. Due to the asynchronous nature of AJAX calls that might lead to all sorts of unpredictable problems. You should rewrite your code to make it one AJAX call that passes on the entire array. — ciruvan, Jul 31 '13 at 15:06
@grim I am currently building and security will be sorted when i have the application working — Barry Watts, Jul 31 '13 at 15:06
UPDATE returns successfully even if no record was updated (eq it does not return the number of modified records, only the fact that the query was "runable"), so if your $ref/$page_ref couple does not refers to an existing record it returns true anyway — yent, Jul 31 '13 at 15:07
@cuewizchris but the other two statements(delete and update work fine) so the right refs are being passed — Barry Watts, Jul 31 '13 at 15:10
You should still avoid making multiple AJAX calls if you can achieve the same result with just one. It's bad design. — ciruvan, Jul 31 '13 at 15:15
@cuewizchris, how can I update multipl;e fields from an array without looping it? — Barry Watts, Jul 31 '13 at 15:21
You should pass on the entire array to your server-side PHP script, then have that loop through your array and make the appropriate database changes. Never do more AJAX calls than necessary. — ciruvan, Jul 31 '13 at 15:23
@cuewizchris are you talking about stored scripts in the mysql db, sorry my knowledge is not the greatest — Barry Watts, Jul 31 '13 at 15:26
Nope. Right now, you're making one AJAX call per array element. update_page_positions.php then does the SQL for one element. You should refactor it so it will deal with the entire array at once. — ciruvan, Jul 31 '13 at 15:34
@cuewizchris ok thanks, I have 3 other ajax call loops also running after this one, so I need to look into this, thanks for the pointer — Barry Watts, Jul 31 '13 at 15:37

score 1 · Accepted Answer · answered Jul 31 '13 at 15:06

Posted as an answer because the comment was too hard to read:

Rather than echoing "updated", try echoing

"UPDATE pages SET menu_pos='$menu_pos', sub_menu_pos='$sub_menu_pos', top_menu='$top_menu', indexpage='$indexpage', page_name='$page_name', page_title='$page_title', desc1='$page_desc', keywords_list='$page_keywords', hidden='$hidden', link='$pagelink' WHERE ref='$ref' AND page_ref='$page_ref'"

(ie. the query you're trying to run).

See if that gives you some clues.

score 0 · Answer 2 · answered Jul 31 '13 at 15:10

0

UPDATE reports success, but does nothing in case when its WHERE clause rejects all rows in updated table.

Maybe $page_ref identifier is correct (so DELETE works), but full $page_ref and $ref combination is not?

answered Jul 31 '13 at 15:10

mas.morozov

2,666
1
22
22

ajax returns success but mysql db not updated

2 Answers2