Unable to method chaining in MySQLi prepared statement using PHP

Question

I am a beginner to PHP. I tried not to put $conn->prepare($sql_stmt) in one variable and just applied method chaining. But I got "Error while executing".

<?php
    include_once 'dbh.inc.php';

    if(isset($_POST['submit_btn']))
    {
        $fullname = $_POST['name'];
        $username = $_POST['username'];
        $password = $_POST['password'];
        $sql_stmt = "INSERT INTO signup (name, username, passwrd) VALUES (?,?,?);";

        //prepare and bind
        $conn->prepare($sql_stmt)->bind_param("sss", $fullname, $username, $password);
        
        //execute
        if($conn->prepare($sql_stmt)->execute())
        {
            echo "User created";
        }
        else
        {
            echo "Error while executing";
        }
        
    }
    else
    {
        echo "Unable to sign up.";
    }

However if I instantiate $sql = $conn->prepare($sql_stmt) like below

<?php
    include_once 'dbh.inc.php';

    if(isset($_POST['submit_btn']))
    {
        $fullname = $_POST['name'];
        $username = $_POST['username'];
        $password = $_POST['password'];
        $sql_stmt = "INSERT INTO signup (name, username, passwrd) VALUES (?,?,?);";

        //prepare and bind
        $sql = $conn->prepare($sql_stmt);
        $sql->bind_param("sss", $fullname, $username, $password);
        //execute
        if($sql->execute())
        {
            echo "User created";
        }
        else
        {
            echo "Error while executing";
        }
        
    }
    else
    {
        echo "Unable to sign up.";
    }

It works and returns "User created". Why is that so?

In your first attempt, you’re throwing away the result of `$conn->prepare(…)->bind_param(…)`, and you’re then preparing a new query without binding parameters. You can’t chain a bind-param call, it’s not designed to be chainable. Just do it correctly as in your second sample. — deceze, Aug 02 '21 at 05:55
What have you tried to resolve your problem? Where are you stuck? Why not use the second way, that is way more readable? — Nico Haase, Aug 02 '21 at 05:59

score 1 · Answer 1 · answered Aug 02 '21 at 09:34

Method chaining is not possible with mysqli. The return value of bind_param() is a boolean. It does not return self. You must call the methods like you showed in the second example:

$sql = $conn->prepare($sql_stmt);
$sql->bind_param("sss", $fullname, $username, $password);
$sql->execute();

In fact, mysqli is not very suitable to be used on its own in your application. If you want something simpler, then use PDO. If for some strange reason you must use mysqli, then you require some kind of abstraction layer that will prevent you from dealing with mysqli functions directly.

As of PHP 8.1, you can pass parameters directly in mysqli_stmt::execute(), which enables you to do method chaining in one line:

$sql = $conn->prepare($sql_stmt)->execute([$fullname, $username, $password]);

Also, please stop checking the return value of execute. You should enable mysqli error reporting instead. How to get the error message in MySQLi?

Unable to method chaining in MySQLi prepared statement using PHP

1 Answers1