mysqli准备的问题变量数量不匹配

Question

I am receiving error 'Number of variables doesn't match number of parameters in prepared statement'

 $stmt = $this->conn->prepare( "INSERT INTO  user 
            ( 
                st1, u1, e1, sa1, 
                h1, roles_id, name_titles_id, first_name, 
                last_name, phone,  mobile, address_road,
                address_area, address_region, post_code, city, 

                country_id, creation_date, activated_at, modified_date_time, 
                created_by, referred_by, gender, ad1, status 
            ) 
            VALUES 
            ( 
                ?, ?, ?, ?, 
                ?, ?, ?, ?, 
                ?, ?, ?, ?, 
                ?, ?, ?, ?, 

                ?, ?, ?, ?,
                ?, ?, ?, ?, ?
                 )" 
            ) 


        $stmt->bind_param('i',$st1);
        $stmt->bind_param('s',$u1);
        $stmt->bind_param('s',$e1);
        $stmt->bind_param('s',$sa1);

        $stmt->bind_param('s',$h1);
        $stmt->bind_param('i',$roles_id);
        $stmt->bind_param('i',$name_titles_id);
        $stmt->bind_param('s',$first_name);

        $stmt->bind_param('s',$last_name);
        $stmt->bind_param('s',$phone);
        $stmt->bind_param('s',$mobile);
        $stmt->bind_param('s',$address_road);

        $stmt->bind_param('s',$address_area);
        $stmt->bind_param('s',$address_region);
        $stmt->bind_param('s',$post_code);
        $stmt->bind_param('s',$city);

        $stmt->bind_param('i',$country_id);
        $stmt->bind_param('s',$creation_date);
        $stmt->bind_param('s',$activated_at);
        $stmt->bind_param('s',$modified_date_time);

        $stmt->bind_param('i',$created_by);
        $stmt->bind_param('i',$referred_by);
        $stmt->bind_param('s',$gender);
        $stmt->bind_param('s',$ad1);
        $stmt->bind_param('i',$status);

Edit:

Just make a small test and it confirms, we can't use multiple bind_param with mysqli.

Not work:

$stmt->bind_param('s',$a);
$stmt->bind_param('s',$b); 

Work:

$stmt->bind_param('ss',$a, $b);

Hopefully it'll be useful for future searches.

Answer 1

Your problem is simple. You are trying to do the thing manually, while the number of data asks for the automated process. You have to make a program to create a query for you.

Suppose You have an array with data already. All you need is to define the list of fields to insert

$fields  = "st1,u1,e1,sa1,h1,roles_id,name_titles_id,first_name,last_name,phone,";
$fields .= "mobile,address_road,address_area,address_region,post_code,city,";
$fields .= "country_id,creation_date,activated_at,modified_date_time,"; 
$fields .= "created_by,referred_by,gender,ad1,status" ;
$fields  = explode(",",$fields);

and then use some programming. Luckily, it's already done:

include 'safemysql.class.php';
$db = new safeMysql();
$insert = $db->filterArray($_POST,$fields);
$db->query("INSERT INTO user SET ?u", $insert);

And yeah, you are using bind_param wrong way. Correct usage can be seen in the manual page.