update rows in mysql

Question

I have three table:

user (id,name,password,......)

words (id,word,meaning)

userword (id, user_id,word_id,checking,old)

and i have a query in php :

$user= $_SESSION['userid'];
$result = mysql_query("SELECT *
                    FROM words
                    WHERE id
                    IN (
                            SELECT word_id
                            FROM userword
                            WHERE old =0
                            AND user_id =$user
                            LIMIT 10
                      ) 
                    ");

this query works properly but after this I want to update words that I selected and set old=1 for them what can I do?

Answer 1

I don't recommend using mysql_* functions because it's depreceated.

mysql_query("UPDATE userword SET old='1' WHERE old='0' AND user_id='$user' AND word_id IN(SELECT id FROM words) LIMIT 10");

A PDO approach would be :

$sql=$dbh->prepare("UPDATE userword SET old='1' WHERE old='0' AND user_id=? AND word_id IN(SELECT id FROM words) LIMIT 10");
$sql->execute(array($user));

More About PDO : www.php.net/manual/en/book.pdo.php