Cant get an equivalent mysqli prepared statement to execute sucessfully?

Question

These lines of code seem to work fine:

   if(isset($_POST['result'])) {
    if($_POST['result'] == 'true'){
        $delete_post_query = mysqli_query($con, "UPDATE posts SET deleted='yes' WHERE id='$post_id'");
        if($stmt = mysqli_prepare($con,$delete_post_query)){
        }
    }
   }

However the prepared equivalent doesnt below doesnt seem to execute:

   if(isset($_POST['result'])) {
    if($_POST['result'] == 'true'){
        $delete_post_query = mysqli_query($con, "UPDATE posts SET deleted='yes' WHERE id=?");
        if($stmt = mysqli_prepare($con,$delete_post_query)){
            mysqli_stmt_bind_param($stmt, "s",$post_id);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        }
    }
}

I am quite confused by this and am fairly certain i am missing something obvious. Any suggestions/solution ?

UPDATE/EDIT:

I made a silly mistake copy/pasting the code so i updated that as suggested.
Have also tried the binding parameters as an integer and as a double respectively.
I believe the issue has something to do with binding parameters as when i replace the placeholder (i.e. '?') with the hardcoded variable $post_id, it works just fine.

See [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some good examples. — Alex Howansky, Jun 28 '17 at 18:19
I apologize, I carelessly-copied pasted something silly from my code. I've updated the prepared statement query which was chocfull of silly things. I believe it is correct now. Same question stands :) — , Jun 28 '17 at 18:28

Ali Rasheed · Answer 1 · 2017-06-28T19:20:59.397

0

You haven't passed reference for any parameter for input.

$delete_post_query = mysqli_query($con, "UPDATE posts SET deleted='yes' WHERE id='$post_id'");

This query says to delete and you have also given a $post_id but below

mysqli_stmt_bind_param($stmt, "ss",$friend_array,$user_to_remove);

you have binding parameter which server couldn't find what's it's reference. Your query should be like this

$delete_post_query = mysqli_query($con, "UPDATE posts SET deleted='yes' WHERE id=?");

and binding paramter should be like this. Since you have only one reference ? you can only bind one parameter. Also id is int then use i if string then use s

mysqli_stmt_bind_param($stmt, "i",$user_to_remove);

//New Way

$stmt = $con->prepare("UPDATE posts SET DELETED='yes' WHERE id=?");
$stmt->bind_param("i",$user_to_remove);
$stmt->execute();

edited Jun 28 '17 at 19:20

answered Jun 28 '17 at 18:27

Ali Rasheed

2,765
2
18
31

Hey thanks for that. I made a silly mistake copy pasting my code. Please see the edited code above. Regarding your suggestion to use 'i' for int i tried that but to no avail unfortunately. – Jun 28 '17 at 18:33
Incidentally, i think the problem is likely with binding parameters. (When i remove the place holder with the actual variable being using in bind_params, it seems to work). – Jun 28 '17 at 18:52

score 0 · Answer 2 · answered Jun 28 '17 at 18:35

0

Since you are using MySQLi you could take a different approach to your code, and use something like

if(isset($_POST['result'])) {
    if($_POST['result'] == 'true'){
        $id = $_POST['post_id']; // make sure to define id
        $delete_post_query = "UPDATE posts SET deleted='yes' WHERE id=? LIMIT 1"
        if ($stmt = $mysqli->prepare($delete_post_query))
        {
            $stmt->bind_param("i",$id);
            $stmt->execute();
            $stmt->close();
        }
    }
}

answered Jun 28 '17 at 18:35

Sam

2,856
3
18
29

Thanks for that. Id has a unique constraint so there probably isnt any need to limit (also i am not a hundred percent sure LIMIT would be compatible with UPDATE's grammar). Regarding the above method, it seems to be the object oriented approach. I tried it out to give it a fair chance but it didn't work. (Also the API doesn't seem to suggest a difference between the procedural and OO styles) – Jun 28 '17 at 18:39

Cant get an equivalent mysqli prepared statement to execute sucessfully?

2 Answers2