[help] MySQL table not updated

[Jordi]

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:

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:
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.

[Genixdeae]

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
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
}
}
}
}
}
}
?>

[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)

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.

[Jordi]

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.

[NogDog]

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.