Error at simultaneously select and update in mysql

Question

I am stuck at php/mysql error which I am not able to trace. My table user_like structure is with sample data

id user_id song_id like
1  1       1        1
2  1       2        0

0 represents dislike and 1 represents like. Now I have query the table using php like http://myhost.net/server/services/songlike.php?uid=1&song_id=1 and I have also got result of like in my query using following code snippet.

$query="select * from atr_like  where user_id='$uid' and song_id='$songid' limit 1 ";
$rs = mysql_query($query);
$row = mysql_fetch_assoc($rs);
print_r($row);
echo "<br>";
$like=$row['like'];
echo $like;

I am getting this value successfully. What I am doing is that if like is not present means null then inserting like in table. Else I am just toggeling its value by update means if it is zero then one and vice versa. Here I am getting error. My PHP Code is

     if(mysql_num_rows($rs) > 0 && $like!=null)
        {
    if($like==1)
    {
    // "user has liked it already so dislike it update like to 0 ";
    $query1="update  atr_like set like=0 where user_id='$uid' and song_id='$songid'";
    }   
    else
    {
    //"user has disliked it previously already so  update like to 1 ";
    $query1="update  atr_like set like=1 where user_id='$uid' and song_id='$songid'";
    }

        }
        else
        {
        echo "first time so insert record . user is going to like";
        $query1="insert into  atr_like   (user_id,song_id,like) values ('$uid','$songid',1)";
        }
$rs1 = mysql_query($query1)or  die(mysql_error());
mysql_close($conn);

The error message is:

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 'like=1 where user_id='1' and song_id='1375'' at line 1.

Is it due to a mysql problem that you can not update any records which is present in sql selection of previous query? Syntax looks fine but still showing syntax error.

you are using mysql reserved names as Column name, please prefix table name to atr_like.like = 1 — Saqueib, Feb 22 '13 at 06:31
really thanks for spending valuable time.You make you made my work easy. — mayur bhagat, Feb 22 '13 at 07:24

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

you are using mysql reserved keyword like

Like clause is used specified pattern in a column

what like is

expr LIKE pat [ESCAPE 'escape_char']

Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

so either use backtick so it will behave like column name else better would be dont use reserved keyword as column name

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi

IMHO only use PDO

also avoid use of singleton if there

$like=$row['like'];
echo $like;

i don't see any need of creating variable $like you can simply echo $row['like'];

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 22 '13 at 06:33

NullPoiиteя

56,591
22
125
143

1

+1 for writting before me.. – Dipesh Parmar Feb 22 '13 at 06:35
thanks NullPointer. Really helpfull. Sure I will learn new design pattern and coding style. – mayur bhagat Feb 22 '13 at 07:25
@mayurbhagat your welcome sounds good you can quick start form http://www.youtube.com/feed/UChgECUNwLBQkgeDcb44pMVg ... i watched and those are really good .. you can also use chat .. author of video could be found at chat room ..even he is one of the owner of php chat room – NullPoiиteя Feb 22 '13 at 07:28

score 2 · Answer 2 · answered Feb 22 '13 at 06:30

2

change your query from

$query1="update  atr_like set like=0 where user_id='$uid' and song_id='$songid'";

to

$query1="update  atr_like set `like`=0 where user_id='$uid' and song_id='$songid'";

like is reserved word in mysql. If you name your column same as reserved word of mysql you have to use backtick for column name.

also change this query.

$query1="insert into  atr_like   (`user_id`,`song_id`,`like`) values ('$uid','$songid',1)";

answered Feb 22 '13 at 06:30

Yogesh Suthar

30,424
18
72
100

i didn't downvote but also add the `INSERT` statement in your answer `:D` – John Woo Feb 22 '13 at 06:33
@JW. Thanks. Actually I was just pointing out the problem. – Yogesh Suthar Feb 22 '13 at 06:35

score 0 · Answer 3 · answered Feb 22 '13 at 06:34

0

Try

$query1 = "update atr_like set `like` = 0 where user_id = '".$uid."' and song_id = '".$songid."'";

for insert

$query1 = "insert into atr_like (user_id,song_id,`like`) values ('".$uid."','".$songid."',1)";

answered Feb 22 '13 at 06:34

Dipesh Parmar

27,090
8
61
90

Error at simultaneously select and update in mysql

3 Answers3