CODEWITHSUNDEEP
phpmysql
Shannon Hollingsworth
Shannon Hollingsworth

Reputation: 61

How to Get UPDATE to only affect one row (PHP/MySQL)

Here's my query:

$student_id = 34;
$user_id = 1;
$lesson_id = 25;

$query = ("UPDATE lessons SET 
assigned='1',
student_id='$student_id'
WHERE user_id='$user_id'
AND lesson_id='$lesson_id'")

I'm trying to run the UPDATE to affect only one row. I have a table like this: 

_____________________________________
user_id|lesson_id|assigned|student_id
_______|_________|________|__________
1      |16       |1       |12
_______|_________|________|__________
1      |25       |0       |0
_______|_________|________|__________
1      |25       |0       |0
_______|_________|________|__________
1      |7        |1       |34
_______|_________|________|__________
1      |13       |1       |12
_______|_________|________|__________
1      |13       |0       |0
_______|_________|________|__________

When I execute this UPDATE, I find that ALL of the matching rows are updated. So if I try to update based on lesson_id 25, BOTH rows change. I only need one to change per query.

I tried adding LIMIT 1 to the query, but it only allowed me to do it once. I went back to the form and submitted all of the info to the query again, but it did not execute a second time. I thought that was odd.

So, what am I missing?

Upvotes: 0

Views: 662

Answers (2)

Haver
Haver

Reputation: 443

Try to use: 

$query = ("UPDATE lessons SET 
    assigned='1',
    student_id='$student_id'
    WHERE user_id='$user_id'
    AND lesson_id='$lesson_id'
    AND assigned ='0' LIMIT 1");

Upvotes: 1

Robert
Robert

Reputation: 20286

This question does not make much sense. You have a table which has two exactly the same rows and your condition is to update these rows. So how do you want to limit the update?

You should add some unique column to identify records. Now all what you can do is LIMIT statement.

Your Where statement affects these rows:

user_id|lesson_id|assigned|student_id
_______|_________|________|__________
1      |25       |0       |0
_______|_________|________|__________
1      |25       |0       |0
_______|_________|________|__________

You should really think about redesigning your database. However, if it's not possible then use assigned field in WHERE condition. WHERE assigned <> 1

Upvotes: 0

Related Questions