[SOLVED] call_user_func_array help needed for very long mysqli statement

[deeem]

OK, need a bit of help. I cant quite understand how this works, or if what I want to do is even possible.

 

I'm reading in a CSV file with around 50 columns, and need to put each line in a DB. I want to use mysqli prepared statements since the data in the sheets could be anything, but columns are likely to be added / removed at short notice. Fighting my way through and keeping track of vars and data types the amount of times they'd have to be repeated if doing this the plain vanilla method doesn't appeal to me, so I'd like to find a more structured method.

 

I've come up with the following code, which I'm fairly sure wont work - my understanding is getting quite hazy by the end, so I'm not sure how to finish it: (I've reduced the number of column names for convenience)

 

$mysqli = new mysqli("localhost", "dbname", "uname", "password");

// table column names matched to datatypes for mysqli bind_params, over 50 entries in real script
$csvarray = array(
"col1" => "i",
"col2" => "i",
"col3" => "s",
"col4" => "i",
"col5" => "s",
"col6" => "s");

$csvvars = array();
$dtypes[0] = "";
$cols = "";
$values = "";

foreach ($csvarray as $key => $value) {
array_push($csvvars, $key);
$dtypes[0].= $value;
if ($cols != "") {
	$cols.= ",";
	$values.= ",";
}
$cols.= $key;
$values.= "?";
}

$csvvars = array_merge($dtypes, $csvvars);

$insert_stmt = $mysqli->prepare("INSERT INTO testtable ($cols) VALUES ($values)");
call_user_func_array(array($insert_stmt, 'bind_param'), $csvvars);

 

At this point I've been struggling with this for a while. Never used call_user_func_array before, but it appears to do something like what I'm looking for - loading the list of column names to be used in $cols as the fields to insert into in the mysql table, producing the string of ?s for $values and most importantly the parameters for bind_param.

 

Can anyone see what I'm trying to do here, and can anyone tell me if I'm going in the right direction or need to abandon it and run screaming back to a vanilla list of variables.

[deeem]

Fixed!

 

$mysqli = new mysqli("localhost", "user", "password", "db");

$csvarray = array(
"col1" => "i",
"col2" => "i",
"col3" => "s",
"col4" => "i",
"col5" => "s",
"col6" => "s",
"col7" => "s");

$csvvars = array();
$dtypes[0] = "";
$cols = "";
$values = "";

foreach ($csvarray as $key => $value) {
array_push($csvvars, &$$key);
$dtypes[0].= $value;
if ($cols != "") {
	$cols.= ",";
	$values.= ",";
}
$cols.= $key;
$values.= "?";
}

$params = array_merge($dtypes, $csvvars);

$insert_stmt = $mysqli->prepare("INSERT INTO m ($cols) VALUES ($values)");
call_user_func_array(array($insert_stmt, 'bind_param'), $params);

for ($i=0;$i<10;$i++) {
foreach ($csvarray as $key=>$value) {
        $$key = "whatever";
        }
$insert_stmt->execute();
}
$insert_stmt->close();
$mysqli->close();

 

Passing the variable names by reference, and using the double-dollar ($$) approach to make them named variables from the contents of the array, has done the job. Much neater.