mysql update on select row number

Question

I have next code:

SET @rownum=0;
UPDATE product_images AS t, (SELECT @rownum:=@rownum+1 rownum, id, rel 
FROM product_images WHERE product_id='227') AS r
SET t.rel = r.rownum
WHERE t.id = r.id

This is working excellent in phpmyadmin

BUT ... next code (witch is actually the same) but placed in php code

mysql_query ("
SET @rownum=0; 
UPDATE product_images AS t, 
(SELECT @rownum:=@rownum+1 rownum, product_images.* 
FROM product_images WHERE product_id='$pid') AS r
SET t.rel = r.rownum WHERE t.id = r.id ") or die(mysql_error());

GIVES ME ERROR : "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE product_images AS t, (SELECT @rownum:=@rownum+1 rownum, product_images.* ' at line 1"

PLEASE HELP. THANK YOU.

Answer 1

These are 2 queries you try to execute at once. That does not work with the mysql_query PHP method.

You actually don't need the first statement. Try

UPDATE product_images AS t
JOIN
(
    SELECT @rownum:=@rownum+1 rownum, id, rel
    FROM product_images
    CROSS JOIN (select @rownum := 0) rn
    WHERE product_id='227'
) AS r ON t.id = r.id
SET t.rel = r.rownum

to init the @rownum variable on the fly.

Simplified SQLFiddle example

Answer 2

MySQL's PHP driver does not allow multiple queries in a single query() call as a security measure against some forms of SQL injection attacks. You'll have to split your multi-query into multiple individual query() calls.