www.webdeveloper.com
Results 1 to 5 of 5

Thread: [help] MySQL table not updated

  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Exclamation [help] MySQL table not updated

    Hello everybody,

    I am currently building something kind of like a forum for a project and I want the administrator to be able to move forums up and down to change the order that they appear on the screen. For this I have written the following function:

    PHP Code:
    function changeOrder ($forum$order$action) {
        if (
    $forum) {
            
    $where "AND forum_id='$forum'";
            
    $table "topics";
            
    $order_type "topic_order";
        }
        else {
            
    $table "forums";
            
    $order_type "forum_order";
        }
        
        if (
    $action == "decrease"$next $order-1;
        else 
    $next $order+1;
        
        
    $sql1 "UPDATE $table SET $order_type=-1 WHERE $order_type=$next $where";
        
    $sql2 "UPDATE $table SET $order_type=$next WHERE $order_type=$order $where";
        
    $sql3 "UPDATE $table SET $order_type=$order WHERE $order_type=-1 $where";

        
    $sql4 "SELECT name, $order_type FROM $table WHERE $order_type=$next $where";
        
    $sql5 "SELECT name, $order_type FROM $table WHERE $order_type=-1 $where";
        
        
    $sql6 "SELECT name, $order_type FROM $table WHERE $order_type=$order $where";
        
    $sql7 "SELECT name, $order_type FROM $table WHERE $order_type=$next $where";
        
        
    $sql8 "SELECT name, $order_type FROM $table WHERE $order_type=-1 $where";
        
    $sql9 "SELECT name, $order_type FROM $table WHERE $order_type=$order $where";
        
        
    $test mysql_fetch_object(mysql_query($sql4)); echo $test->name.", ".$test->$order_type."<br />";
        
    mysql_query($sql1) or die (mysql_error()); echo $sql1."<br />";
        
    $test mysql_fetch_object(mysql_query($sql5)); echo $test->name.", ".$test->$order_type."<br />";
        
        
    $test mysql_fetch_object(mysql_query($sql6)); echo $test->name.", ".$test->$order_type."<br />";
        
    mysql_query($sql2) or die (mysql_error()); echo $sql2."<br />";
        
    $test mysql_fetch_object(mysql_query($sql7)); echo $test->name.", ".$test->$order_type."<br />";
        
        
    $test mysql_fetch_object(mysql_query($sql8)); echo $test->name.", ".$test->$order_type."<br />";
        
    mysql_query($sql3) or die (mysql_error()); echo $sql3."<br />";
        
    $test mysql_fetch_object(mysql_query($sql9)); echo $test->name.", ".$test->$order_type."<br />";

    A lot of these rules are of course not necessary, but I put them there because the function doesn't work and I wanted to print out why.
    This is an example of what I would get if I would call on this function:
    forum2, 2 (forumname followed by forum_order)
    UPDATE jbieger_debat_forums SET forum_order=-1 WHERE forum_order=2
    forum2, -1
    forum3, 3
    UPDATE jbieger_debat_forums SET forum_order=2 WHERE forum_order=3
    forum3, 2
    forum2, -1
    UPDATE jbieger_debat_forums SET forum_order=3 WHERE forum_order=-1
    forum2, 3

    This is exactly how it should work. The only problem is that my MySQL-table doesn't get updated. At least, something like that is happening. This is very strange though, because with $sql4-9 I am getting information out of the (updated) table and this information seems to be correct! And indeed, on the forum-page, the forums would be switched.
    However when I come back to that page, I find that the forums have automatically switched back. Also, when I would look in phpMyAdmin (even immideately after I called the function), nothing appears to have changed.
    Some more oddities:
    If I would copy the update-queries into the SQL-form of phpMyAdmin, everything works perfectly and the table gets updated.
    Also, if I would call the three update-queries seperately, they do seem to work. I would do that like this:
    PHP Code:
    mysql_query($sql1);
    //mysql_query($sql2);
    //mysql_query($sql3);

    reload page

    //mysql_query($sql1);
    mysql_query($sql2);
    //mysql_query($sql3);

    reload page

    //mysql_query($sql1);
    //mysql_query($sql2);
    mysql_query($sql3); 
    This does work.

    Does anyone have any idea about what might be going wrong and what I can do about it?

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    379
    I was doing something like that for a website i'm building, only i was moving the order of the menu items. Anyways the idea behind the two are the same thus they should work. I'm not sure what's wrong with yours. Here's mine if you want to use it.
    PHP Code:
    <?PHP
    if(isset($_GET["type"]) && $_GET["type"] == "down") {
       
    $islast "SELECT * FROM `$menu_table` ORDER BY `menu_or` DESC LIMIT 0, 1";
       
    $islastresult mysql_query($islast$dbconn);
          while(
    $last mysql_fetch_assoc($islastresult)) {
             if(isset(
    $_GET["id"]) && $_GET["id"] == ""$last["ID"] ."") {
    ?>
                You can not move this item down any further.
    <?PHP
             
    }else{
                
    $query "SELECT * FROM `$menu_table` WHERE `ID` = '"$_GET["id"] ."'";
                
    $result mysql_query($query$dbconn);
                   if(!
    mysql_num_rows($result)) {
    ?>
                      The selected id doesn't exsist. Please go back and try again.
    <?PHP
                   
    }else{
                      while(
    $menu mysql_fetch_assoc($result)) {
                         
    $updated_num $menu["menu_or"] + 10;
                         
    $query2 "UPDATE `$menu_table` SET `menu_or` = menu_or-10 WHERE `menu_or` = '"$updated_num ."'";
                         
    $result2 mysql_query($query2$dbconn);
                         
    $query3 "UPDATE `$menu_table` SET `menu_or` = menu_or+10 WHERE `ID` = '"$_GET["id"] ."'";
                         
    $result3 mysql_query($query3$dbconn);
                         
    $query4 "SELECT * FROM `$menu_table` WHERE `menu_or` = '"$updated_num ."'";
                         
    $result4 mysql_query($query4$dbconn);
                         if(!
    mysql_num_rows($result4)) {
    ?>
                            Couldn't update. Please give this to the administrator.<br><?PHP echo $query ."<br><br>"$query2 ."<br><br>"$query3?>
    <?PHP
                         
    }else{
    ?>
                            <meta http-equiv="refresh" content="1;URL=index.php?p=admin&amp;type=menu">Menu updated. If you are not redirected click <a href="index.php?p=admin&amp;type=menu">here</a>.
    <?PHP
                         
    }
                      }
                   }
             }
          }
    }elseif(isset(
    $_GET["type"]) && $_GET["type"] == "up") {
       
    $isfirst "SELECT * FROM `$menu_table` ORDER BY `menu_or` ASC LIMIT 0, 1";
       
    $isfirstresult mysql_query($isfirst$dbconn);
          while(
    $first mysql_fetch_assoc($isfirstresult)) {
             if(isset(
    $_GET["id"]) && $_GET["id"] == ""$first["ID"] ."") {
    ?>
                You can not more this menu item up any further.
    <?PHP
             
    }else{
                
    $query "SELECT * FROM `$menu_table` WHERE `ID` = '"$_GET["id"] ."'";
                
    $result mysql_query($query$dbconn);
                if(!
    mysql_num_rows($result)) {
    ?>
                   The selected id doesn't exsist. Please go back and try again.
    <?PHP
                
    }else{
                   while(
    $menu mysql_fetch_assoc($result)) {
                      
    $updated_num $menu["menu_or"] - 10;
                      
    $query2 "UPDATE `$menu_table` SET `menu_or` = menu_or+10 WHERE `menu_or` = '"$updated_num ."'";
                      
    $result2 mysql_query($query2$dbconn);
                      
    $query3 "UPDATE `$menu_table` SET `menu_or` = menu_or-10 WHERE `ID` = '"$_GET["id"] ."'";
                      
    $result3 mysql_query($query3$dbconn);
                      
    $query4 "SELECT * FROM `$menu_table` WHERE `menu_or` = '"$updated_num ."'";
                      
    $result4 mysql_query($query4$dbconn);
                      if(!
    mysql_num_rows($result4)) {
    ?>
                         Couldn't update. Please give this to the administrator.<br><?PHP echo $query ."<br><br>"$query2 ."<br><br>"$query3?>
    <?PHP
                      
    }else{
    ?>
                         <meta http-equiv="refresh" content="1;URL=index.php?p=admin&amp;type=menu">Menu updated. If you are not redirected click <a href="index.php?p=admin&amp;type=menu">here</a>.
    <?PHP
                      
    }
                   }
                }
             }
          }
    }
    ?>
    Only Those Who Listen Prosper
    ~GD~

  3. #3
    Join Date
    Jan 2005
    Posts
    17
    Heh...I'm too tired (or is that lazy? =) to look through all that code, but here are some ideas :

    To begin with, maybe change your $test variable to something different each time it is used (sometimes I have had PHP do strange things when I reuse a variable for multiple sql arrays)

    Secondly, comment out the '$var = mysql_query($sql)' and instead simple 'echo $sql'. Then, make sure that the values are all coming through as you would expect them to.

    Third, put in some error checking. Do a 'if ($query) { $message = 'I rock!'; } else { $message = 'I suck'.mysql_error(); } echo $message;

    Hope that helps a bit.
    American by birth, but Southern by the grace of God.

  4. #4
    Join Date
    Jan 2005
    Posts
    4
    Originally posted by jajtiii
    Heh...I'm too tired (or is that lazy? =) to look through all that code, but here are some ideas :

    To begin with, maybe change your $test variable to something different each time it is used (sometimes I have had PHP do strange things when I reuse a variable for multiple sql arrays)
    I can try, but it shouldn't matter because first of all, the $test-variables aren't really important. They're just there for errorchecking. Second of all, I didn't initially have them and it didn't work then either.

    Originally posted by jajtiii
    Secondly, comment out the '$var = mysql_query($sql)' and instead simple 'echo $sql'. Then, make sure that the values are all coming through as you would expect them to.
    Without the '$var=mysql_query($sql);' the query would actually not be executed, would it. And I am already also using 'echo $sql;'.

    Originally posted by jajtiii
    Third, put in some error checking. Do a 'if ($query) { $message = 'I rock!'; } else { $message = 'I suck'.mysql_error(); } echo $message;

    Hope that helps a bit.
    I'm already doing something similar by using 'or die (mysql_error());'

    Thanks for your time, I'm going to try Genixdae's code now.

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,144
    If your MySQL installation is configured for transaction-type processing, you may have to to a SQL "commit;" to actually enter the changes into the DB. I am not on expert on this type of configuration, so can only reference you to www.php.net to look into this, or talk to your web server sysadmin and see whether this could be the case.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles