Warix3 Warix3 - 5 months ago 12
SQL Question

MySqli prepared statment doesn't use updated variable value when executing the statement

My prepared statement isn't using the updated value while executing , it's using the one i set before binding , is this how it should work? because in examples on w3schools it was using updated value, so i'm not sure what am i doing wrong here, under //winner comment is problematic part, $assqlst2 isn't using updated value of $pointswon(I put that echo just to make sure values are correct, and i'm not getting any errors in output).

<?php
$dbserver = ""; //database information is populated in my script, i didn't put it here because of security resons.
$dbusername = "";
$dbpassword = "";
$dbname = "";
$dbrecievedPassword = $_POST['dbPassword'];
$winnerid = intval($_POST['winnerid']);
$winnername = $_POST['winnername'];
$pointswon = intval($_POST['pointswon']);
$loserid = intval($_POST['loserid']);
$losername = $_POST['losername'];
$pointsph = $pointswon;
if($dbrecievedPassword != null && $dbrecievedPassword == $dbpassword)
{
echo "PWD_OK";
}
else
{
echo "PWD_INCORRECT";
}

$conn = new mysqli($dbserver,$dbusername,$dbrecievedPassword);
mysqli_select_db($conn,$dbname);
if($conn->connect_error)
{
die("Connection failed: ".connect_error);
}
if($winnerid != null)
{

if($sqlst = $conn->prepare("SELECT * FROM table_name WHERE uid=?"))
{
if($assqlst2 = $conn->prepare("UPDATE table_name SET as_points=? WHERE as_uid=?"))
{
if($sqlst3 = $conn->prepare("INSERT INTO table_name (as_uid,as_points,as_username) VALUES (?,?,?)"))
{

$sqlst->bind_param("i",$winnerid);
$assqlst2->bind_param("ii",$pointswon,$winnerid);

$sqlst3->bind_param("iis",$winnerid,$pointswon,$winnername);
if($loserid != null)
{
//winner

if(!$sqlst->execute())
{
echo $conn->error;
}
$sqlst->bind_result($b1,$b2,$b3);
$sqlst->fetch();
$pointswon = $pointsph + intval($b3);
echo $pointswon." ".$winnerid;
if(!$assqlst2->execute())
{
echo $conn->error;
}
//loser
$winnerid = $loserid;
if(!$sqlst->execute())
{
echo $conn->error;
}
$sqlst->bind_result($c1,$c2,$c3);
$sqlst->fetch();
$pointswon = intval($c3) - $pointsph;
$sqlst->close();
if(!$assqlst2->execute())
{
echo $conn->error;
}
$assqlst2->close();

}else
{
if(!$sqlst->execute())
{
echo $conn->error;
}
$sqlst->bind_result($k1,$k2,$k3);
$sqlst->fetch();
if($k1 != null)
{
$pointswon = intval($pointsph) + intval($k3);
$sqlst->close();
if(!$assqlst2->execute())
{
echo $conn->error;
}
$assqlst2->close();

}else
{
$pointswon = $pointsph;
$sqlst3->execute();
$sqlst3->close();
}
}
}else
{
echo $conn->error;
}
}else
{
echo $conn->error;
}
}else
{
echo $conn->error;
}

}


?>

Answer

Does it really have to be nested?

You can't just rehash a query and use it multiple times. If you want to, you just have to put it in a function, so you just have to call it when you want it:

function query1($conn, $winnerid){

    if($sqlst = $conn->prepare("SELECT col1, col2, col3 FROM table_name WHERE uid = ?")){
        $sqlst->bind_param("i", $winnerid);
        $sqlst->execute();
        $sqlst->bind_result($b1, $b2, $b3);
        $sqlst->fetch();
        $sqlst->close();
    }

    return array($b1, $b2, $b3);

}

function query2($conn, $pointswon, $id){

    if($assqlst2 = $conn->prepare("UPDATE table_name SET as_points = ? WHERE as_uid = ?"))
        $assqlst2->bind_param("ii", $pointswon, $id);
        $assqlst2->execute();
        $assqlst2->close();
    }

}

function query3($conn, $winnerid, $pointswon, $winnername){

    if($sqlst3 = $conn->prepare("INSERT INTO table_name (as_uid,as_points,as_username) VALUES (?,?,?)")){
        $sqlst3->bind_param("iis", $winnerid, $pointswon, $winnername);
        $sqlst3->execute();
        $sqlst3->close();
    }

}

After setting the functions, we can now proceed with what you want to achieve:

if($winnerid != NULL){

    /*** WINNER UPDATE ***/
    list($b1, $b2, $b3) = query1($conn, $winnerid); /* CALL THE SELECT QUERY */

    if($loserid != NULL){
        $pointswon = $pointsph + intval($b3); /* POINTS GATHERED BY THE WINNER */
        echo $pointswon." ".$winnerid;
    } /* END OF CONDITION $loserid IS NOT NULL */

    query2($conn, $pointswon, $winnerid); /* CALL THE UPDATE QUERY */


    /*** LOSER UPDATE ***/
    list($c1, $c2, $c3) = query1($conn, $loserid); /* CALL THE SELECT QUERY */

    $pointswon = intval($c3) - $pointsph; /* POINT GATHERED BY THE LOSER */

    query2($conn, $pointswon, $loserid); /* CALL THE UPDATE QUERY */


} /* END OF CONDITION $winnerid IS NOT NULL */

else {

    list($k1, $k2, $k3) = query1($conn, $winnerid); /* CALL THE SELECT QUERY */

    if($k1 != null){

        $pointswon = intval($pointsph) + intval($k3);

        query2($conn, $pointswon, $winnerid); /* CALL THE UPDATE QUERY */

    } else {

        $query3($conn, $winnerid, $pointsph, $winnername); /* CALL THE INSERT QUERY */

    }

} /* END OF ELSE */