Loop through array in bind parameters - prepared statement

Question

I am writing a function in which I can do a couple of database actions, in this case an insert data based on a simple array

"insert" => array (
    "1" => array (
        "tnt_role" => array (
            "rolename" => array (
                "value" => "administrator",
                "notation" => "string"
            )
        )
    ),  
    "2" => array (
        "tnt_role" => array (
            "rolename" => array (
                "value" => "user",
                "notation" => "string"
            )
        )
    ),
    "3" => array (  
        "tnt_users" => array (
            "username" => array (
                "value" => "administrator",
                "notation" => "string"
            ),
            "userpassword" => array (
                "value" => md5('admin', FALSE),
                "notation" => "string"
            ),
            "email" => array (
                "value" => "[email protected]",
                "notation" => "string"
            ),
            "roleid" => array (
                "value" => "1",
                "notation" => "int"
            )
        )
    )   
)

and here is the specific part of the function

case "insert":
    foreach ($tables as $instance => $inserttables) {
        foreach ($inserttables as $table => $fields) {
            // create a count on the number of fields that are being parsed
            $countfields = count($fields);
            $sql = "INSERT INTO ". $table ." (" ;
            $i = 0;
            // set up the columns for the insert statement
            foreach ($fields as $field => $value) {     
                $i++;
                $sql .= $field;
                if ($countfields != $i ) {
                    $sql .= ", ";
                }
            }
            // close the column statement, open the value statement, since this is prepared, we will add question marks and add later the values
            $sql .= ") ";
            $sql .= "VALUES (";
            $i = 0;
            $parameters = "";
            $notation = "";
            foreach ($fields as $field => $value) {     
                $i++;
                $sql .= "?";
                // set up the notation in the bind parameters
                switch($value['notation']) {
                    case "int":
                        $notation .= "i";
                        break;
                    case "string":
                        $notation .= "s"    ;
                        break;  
                }
                // need to escape the email and username values 
                $parameters .= "'".$value['value']."'" ;
                if ($countfields != $i ) {
                    $sql .= ", ";
                    $parameters .= ", ";
                }
            }
            $sql .= ")";

            $stmt = mysqli_prepare($con, $sql);
            mysqli_stmt_bind_param($stmt, $notation, $parameters);
            if(mysqli_stmt_execute($stmt)) {
                    echo "data entered";
            } else {
                echo "error in following query:". $sql; 
            }                               
        }
    }
    break;

This works all fine except for 1 tiny thing and that is when I enter more than 1 item in the database. It gives me the following error

mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in .... line 647

I realized after a while that it is the parameter variable that is the case. The bind parameter here is only 1 variable in which I separate it all nicely with a comma (in order to mimic the list). Viewing this optical would say this looks fine, however I think the bind parameter statement really requires separate variables. At this point it sees actually just one variable, rather than the 4 in my test case.

I tried looping it this way:

mysqli_stmt_bind_param($stmt, $notation, 
    foreach ($fields as $field => $value) {     
        echo $value['value'];
        if ($countfields != $i ) {
            echo ",";
        }
    }
);

But to no avail, since it will spit out the following.

Parse error: syntax error, unexpected 'foreach' (T_FOREACH) in

Does anybody have an idea how to solve this issue?

== edit ==

table structure as requested, although I doubt it is that problem, since I get a bind parameter error, not an error in executing the statement.

== edit 2 ==

also tried the following, which didn't help, since it didn't stack (I saw this in PDO)

foreach ($fields as $field => $value) { 
    switch($value['notation']) {
        case "int":
            $notation = "i";
            break;
        case "string":
            $notation = "s" ;
            break;  
    }
    mysqli_stmt_bind_param($stmt, $notation, $value['value']);
}

This has nothing to do with my database, its pure that part, since i can fill my roles table without any problem (its just an id and a description and then i enter simply ust the description). If you must know, i added a picture of it's structure, very simple at this point @gview — Dorvalla
– Dorvalla, Commented Dec 26, 2019 at 22:36
On a side note, it would be easier for you to use PDO or even something better like a DB abstraction class, e.g. EasyDB. — Dharman
– Dharman ♦, Commented Dec 26, 2019 at 22:43
I would strongly advice to take a look at some of the tutorials on phpdelusions.net/mysqli/simple They explain how to use mysqli properly if you are already stuck with it. — Dharman
– Dharman ♦, Commented Dec 26, 2019 at 22:53
Ok. Look into password_hash() and the related links in there. There is also no need to salt, it takes care of all that. Plus, if you're on PHP 7, you can benefit from using Argon2 which is even more powerful. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 26, 2019 at 23:36

Nick · Accepted Answer · 2019-12-26 22:41:23Z

5

You need to pass each variable individually to mysqli_stmt_bind_param, so $parameters needs to be an array, not a string. Change the following lines of code:

$parameters = "";

to:

$parameters = array();

and

$parameters .= "'".$value['value']."'" ;

to:

$parameters[] = $value['value'];

(note there is no need to escape values when you are using prepared statements)

remove this line:

$parameters .= ", ";

and finally, change

mysqli_stmt_bind_param($stmt, $notation, $parameters);

to:

mysqli_stmt_bind_param($stmt, $notation, ...$parameters);

and it should work fine.

answered Dec 26, 2019 at 22:41

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nick Over a year ago

@Dharman I spent a lot of time looking for a duplicate but couldn't find one - do you have a good link?

Dharman Over a year ago

No, my collection of links is very small. I am not a fan of forcibly closing questions as duplicates.

Nick Over a year ago

@Dharman I'm with you but had an argument with someone earlier about that very thing - there's definitely two schools of thought about it on the site.

Dorvalla Over a year ago

@Nick thank you very much for this solution, It does indeed the trick. Can you explain what the periods are before the parameter variable? I never seen this. I knew somewhere an array could be passed, but i had no idea how to access it, this is obviously the solution for my problem. I accepted it as awnser.

Nick Over a year ago

@Dorvalla the periods are the splat operator. It can be used to assemble arrays but also to unpack them into an argument list. There's more information here

Collectives™ on Stack Overflow

Loop through array in bind parameters - prepared statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related