CODEWITHSUNDEEP
phpmysqldecrement
max_
max_

Reputation: 24481

MySQL not decrementing values

I can't get the values in the table Accounts to decrement when I try to delete a post. The values I would like to decrement are "PostCount", "Likes Count" and "CommentsCount". Currently only the "PostCount" works.

Please can you tell me what I am doing wrong:

$arg = mysql_query("SELECT `numberOfLikes` FROM Posts WHERE `id` = '$postID'") or die(mysql_error());

    $query = mysql_query("SELECT * FROM Likes WHERE `postID` = '$postID'");
    while ($row = mysql_fetch_assoc($query)) {
        $b = $row['accountID'];
        mysql_query("UPDATE Accounts SET `numberOfLikes` = (`numberOfLikes` - 1) WHERE `id` = '$b'");
    }
    $arg = mysql_query("SELECT `numberOfComments` FROM Posts WHERE `id` = '$postID'");
    $query = mysql_query("SELECT * FROM Posts WHERE `id` = '$postID'");
    while($row = mysql_fetch_assoc($arg)) {
        $b = $row['accountID'];
        mysql_query("UPDATE Accounts SET `CommentsCount` = (`CommentsCount` - 1) WHERE `id` = '$b'");
    }
    $arg = mysql_query("SELECT `PostCount` FROM Accounts WHERE `id` = '$accountID'");
    while ($row = mysql_fetch_assoc($arg)) {
        mysql_query("UPDATE Accounts SET `PostCount` = (`PostCount` - 1) WHERE `id` = '$accountID'");
    }
    mysql_query("DELETE FROM Likes WHERE `postID` = '$postID'");
    mysql_query("DELETE FROM Comments WHERE `postID` = '$postID'");
    mysql_query("DELETE FROM Posts WHERE `id` = '$postID' AND `accountID` = '$accountID'") or die(mysql_error());
    exit("Deleted post");

Upvotes: 0

Views: 698

Answers (2)

max_
max_

Reputation: 24481

I re-coded my if statement containing the code I posted and it is now working perfectly:

mysql_query("UPDATE Accounts SET `PostCount` = (`PostCount` - 1) WHERE `id` = '$accountID'");
        $query = mysql_query("SELECT * FROM Likes WHERE `postID` = '$postID'") or die(mysql_error());
        while($row = mysql_fetch_assoc($query)) {
            $accID = $row['accountID'];
            mysql_query("UPDATE Accounts SET `Likes Count` = (`Likes Count` - 1) WHERE `id` = '$accID'");
        }
        $query = mysql_query("SELECT * FROM Comments WHERE `postID` = '$postID'");
        while($row = mysql_fetch_assoc($query)) {
            $accID = $row['accountID'];
            mysql_query("UPDATE Accounts SET `CommentsCount` = (`CommentsCount` - 1) WHERE `id` = '$accID'");
        }
        mysql_query("DELETE FROM Likes WHERE `postID` = '$postID'");
        mysql_query("DELETE FROM Comments WHERE `postID` = '$postID'");
        mysql_query("DELETE FROM Posts WHERE `id` = '$postID' AND `accountID` = '$accountID'") or die(mysql_error());
        exit("Deleted post");

Upvotes: 0

Jim
Jim

Reputation: 18853

First up looping queries kills your execution time. You want to limit it to as little queries as possible, this is where implode comes into play:

// what is this for? I do not see it being used. $arg = mysql_query("SELECT `numberOfLikes` FROM Posts WHERE `id` = '$postID'") or die(mysql_error());

$query = mysql_query("SELECT * FROM Likes WHERE `postID` = '$postID'");
while ($row = mysql_fetch_assoc($query)) {
    $b[] = $row['accountID'];
}
mysql_query("UPDATE Accounts SET `numberOfLikes` = (`numberOfLikes` - 1) WHERE `id` IN(" . implode(',', $b) . ")") or trigger_error('MySQL Update Failed: ' . mysql_error());

This will be much more efficient and have to same effect.

The next query, should be similar: 

$arg = mysql_query("SELECT `numberOfComments` FROM Posts WHERE `id` = '$postID'");
// again an extra unnecessary query not being used. $query = mysql_query("SELECT * FROM Posts WHERE `id` = '$postID'");
$b=array();
while($row = mysql_fetch_assoc($arg)) {
    $b[] = $row['accountID'];
}
mysql_query("UPDATE Accounts SET `CommentsCount` = (`CommentsCount` - 1) WHERE `id` IN(".implode(',', $b) . ")") or trigger_error(mysql_error());

This next one, I do not even know why you are looping through it:

//$arg = mysql_query("SELECT `PostCount` FROM Accounts WHERE `id` = '$accountID'");
//while ($row = mysql_fetch_assoc($arg)) {
mysql_query("UPDATE Accounts SET `PostCount` = (`PostCount` - 1) WHERE `id` = '$accountID'") or trigger_error(mysql_error());
//}

As you are not utilizing that data anywhere, just run the update query.

Implementing the above should speed up your application and reduce redundancy. implode is very handy and running 1 query vs several is almost always preferred. I am not sure if that will solve your problem, but it is a huge step in the right direction in fixing your code.

Upvotes: 1

Related Questions