CODEWITHSUNDEEP
phpmysql
JMRboosties
JMRboosties

Reputation: 15740

MySQL is only updating 1 row when I want it to update more than one, and the WHERE clause allows for both to be updated

So the purpose of this request is to get friend requests which you have been sent, then set their state in the friend request table to "1", which denotes that the request has been received but not answered yet. Here is the code:

if (isset($_POST['username'])) {
    $username = $_POST['username'];
}
$statesent = 0;
$statepending = 1;

$connect = mysql_connect("localhost", "root". "");
mysql_select_db("TagDB");

$query = mysql_query("SELECT firstname, lastname, username FROM tagusers 
    INNER JOIN friendtable ON tagusers.username = friendtable.userA
    WHERE friendtable.userB = '$username' AND friendtable.state = '$statesent'");

while ($e = mysql_fetch_assoc($query)) {
    $output[] = $e;
}

$output = json_encode($output);

print $output;

$requestsgot = mysql_query("UPDATE friendtable SET state = '$statepending' WHERE (userB = '$username' AND state = '$statesent')");

This code retrieves the requests just fine, but is only updating a single row of data where userB column value = the username POST, and state = 0. I have more than one which meet both requirements. Any idea why it's only giving me one? I checked for typos and there are none, and besides, I wouldn't be able to get both requests (which I do) if there was a typo present. And what makes this even stranger is that when I move the request from the bottom of the code to the middle, directly after the $query, it will update the opposite row of the one it updates in the position I've posted. It's a strange one. Thanks for the help.

Upvotes: 1

Views: 212

Answers (1)

Dave
Dave

Reputation: 4597

Replace UPDATE friendtable SET state = '$statepending' WHERE (userB = '$username' AND state = '$statesent')

with

SELECT * from friendtable WHERE (userB = '$username' AND state = '$statesent')

and see how many records are returned.

Upvotes: 1

Related Questions