php delete sql query not working

Question

<?php
    include('session.php');
?>

<?php
    $conn = new mysqli("127.0.0.1","root","","foo");
    if ($conn->connect_errno) {
        echo "Failed to connect to MySQL: (" . $conn->connect_errno . ") " .         $conn->connect_error;
    }
    $sew = $_SESSION['login_user'];
    $a = $_GET["en"];
    $l = 1;
    $d = -1;

    if($a == 1)
    {
        $sqlw = " INSERT into dlkeuser VALUES('$a','$sew')" ;

        if ($conn->query($sqlw) === FALSE) 
        {
            echo "you have already disliked the song";
        }
        else
        {
        //query1
            $sql = " DELETE FROM lkeuser WHERE userid = '$sew' AND songid = '$a' ";

        //query2
            $sql = "UPDATE liking
            SET count = count - 1 ";

            if ($conn->query($sql) === TRUE) {
                echo "you disliked the song";
            } 
            else {
                echo "Error: " . $sql . "<br>" . $conn->error;
            }
        }

In this php code snippet, query1 is not working whereas query 2 is fine.

I am trying to insert (songid, userid) in dlkeuser(dislike) table against user i/p($_GET["en"]) and delete the record(songid,userid) from lkeuser(like) table if it exists. (songid,userid) pair is the composite primary key here. count is the net like/dislike of a song.

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Mar 29 '17 at 16:08
Your code is wide open to SQL injection, so you're currently not even in control of what SQL code you execute. At runtime, what is that actual SQL code? `echo` your `$sql` variable to find out. Is that code what you expect it to be? When you manually execute that code on the database, what happens? — David, Mar 29 '17 at 16:09
You replace you delete query with an insert query by setting $sql to a different string before executing it. Also, take what @Jay Blanchard says to heart. — Sloan Thrasher, Mar 29 '17 at 16:09
you're also trying to update your entire db, unless that's what you want. — Funk Forty Niner, Mar 29 '17 at 16:14

score 0 · Answer 1 · answered Mar 29 '17 at 16:12

You need to execute query1, before reuse your $sql variable.

 //query1
   $sql  = " DELETE FROM lkeuser WHERE userid = '$sew' AND songid = '$a' " ;
   $conn->query($sql);

 //query2
       $sql = "UPDATE liking
       SET count = count - 1 ";

       if ($conn->query($sql) === TRUE) {

score 0 · Answer 2 · answered Mar 29 '17 at 16:13

You are not executing your query1 anywhere. Just the following code won't execute your query

$sql  = " DELETE FROM lkeuser WHERE userid = '$sew' AND songid = '$a' " ;

You need another line like the following (as you did for query2)

if ($conn->query($sql) === TRUE) {

    echo "you liked the song";
} 
else {
      echo "Error: " . $sql . "<br>" . $conn->error;
}

This executes the query and also checks for errors.

score 0 · Accepted Answer · edited Jul 30 '18 at 15:19

let's try this,

it will work

<?php
    include('session.php');
?>

<?php
    $conn = new mysqli("127.0.0.1","root","","foo");
    if ($conn->connect_errno) {
       echo "Failed to connect to MySQL: (" . $conn->connect_errno . ") " .           $conn->connect_error;
    }
    $sew = $_SESSION['login_user'];
    $a = $_GET["en"];
    $l = 1;
    $d = -1;

    if($a == 1)
    {
    $sqlw = " INSERT into dlkeuser VALUES('$a','$sew')";

        if ($conn->query($sqlw) === FALSE) 
        {
            echo "you have already disliked the song";
        }
        else
        {
        //query1
            $sql  = " DELETE FROM lkeuser WHERE userid = '$sew' AND songid = '$a' " ;

        //query2
            $sql1 = "UPDATE liking
            SET count = count - 1 ";

            if ($conn->query($sql) === TRUE) {
                echo "deleted the song";
            }

            if ($conn->query($sql1) === TRUE) {
                echo "you disliked the song";
            } 
            else {
                echo "Error: " . $sql . "<br>" . $conn->error;
            }
        }

php delete sql query not working

3 Answers3