I'm trying to create a select query with dynamic where clause and dynamic parameters but I always get error :
Warning: mysqli_stmt::bind_param(): Number of elements in type
definition string doesn't match number of bind variables
Which I sincerely do not understand since it seems the count is alright. So this is what the code really looks like in its rude format. I can't see what I'm doing wrong.
//get variables
$mediaArray ='Facebook,Twitter,Twitch,';
$otherMedia = 'House';
//convert string to array
$socialArray = explode(',', $mediaArray)
//declare some variables to be used later
$andwhere = '';
$bp = '';
$socialmarray = ''
//get every value from array of social media
foreach($socialArray as $socialmedia){
$socialmarray .=$socialmedia.',';
$andwhere .= " AND socialmedianame=?";
$bp .='s';
}
//test strings
echo $wheres = $andwhere;//AND socialmedianame=? AND socialmedianame=? AND socialmedianame=?
echo $bip = $bp.'s';//ssss
echo $validarayy = rtrim($socialmarray,',');//Facebook,Twitter,Twitch
//select query
$selectquery = $conn->prepare("select * from mediaservices where socialmedianame=? $wheres");
$selectquery->bind_param("$bip",$otherMedia,$validarayy);
$selectquery->execute();
$resultquery = $selectquery->get_result();
Because:
- You are using user-supplied data, you must assume that your query is vulnerable to a malicious inject attack
- The amount of data that is to be built into the query is variable/indefinite
- You are only writing conditional checks on a single table column
You should use a prepared statement and merge all of the WHERE clause logic into a single IN statement.
Building this dynamic prepared statement is more convoluted (in terms of syntax) than using pdo, but it doesn't mean that you need to abandon mysqli simply because of this task.
I have baked in every possible error check point so that you can easily debug if something unexpected arises. I must stress that you must never show error details to the public as a matter of best security practices.
I have tested this solution with my own db data on my localhost to be a working solution.
$_POST['userMedia'] ='Facebook,Twitter,Twitch,';
$media = explode(',', rtrim($_POST['userMedia'], ',')); // build array from csv string
$presetMedia = 'House';
$media[] = $presetMedia; // push known media string into array
$media = array_unique($media); // make sure there are no duplicates
var_export($media); // see what this generates
if (!$count = count($media)) {
echo "There are no values in media, so a prepared statement is overkill and IN won't work when empty";
// handle this case however you wish (if it is even possible within your project)
} elseif (!$conn = new mysqli("localhost", "root", "", "myDB")) { // use your own credentials
echo "Database Connection Error: " , $conn->connect_error;
} else {
$csph = implode(',', array_fill(0, $count, '?')); // comma-separated placeholders e.g: ?,?,?,?
echo "<div>Placeholders: $csph</div>";
$query = "SELECT * FROM `mediaservices` WHERE `socialmedianame` IN ($csph)";
echo "<div>Query: $query</div>";
if (!$stmt = $conn->prepare($query)) {
echo "<div>Syntax Error @ prepare: {$conn->error}</div>"; // NEVER show error details to the public
}else{
array_unshift($media, str_repeat('s', $count)); // prepend the type values string e.g: ssss
var_export($media); // see what this generates
foreach ($media as &$v) {
$ref[] = &$v; // call_user_func_array requires array that is passed-by-reference
}
call_user_func_array([$stmt, 'bind_param'], $ref); // call bind_param() upon stmt object, using each media value
if (!$stmt->execute() || !$result = $stmt->get_result()) { // execute statement and get the resultset while checking for falsey returned feedback
echo "<div>Error @ execute/get_result: {$stmt->error}</div>"; // NEVER show error details to the public
} elseif (!$result->num_rows) { // check for empty rows, if you wish
echo "<div>No Rows Found</div>";
} else {
echo "<pre>";
while ($row = $result->fetch_assoc()) {
var_export($row); // do what you like with the associative-keyed elements
echo "<br>";
}
echo "</pre>";
}
$stmt->close();
}
}
Output should be like:
array ( 0 => 'Facebook', 1 => 'Twitter', 2 => 'Twitch', 3 => 'House' )
Placeholders: ?,?,?,?
Query: SELECT * FROM `mediaservices` WHERE `socialmedianame` IN (?,?,?,?);
array ( 0 => 'ssss', 1 => 'Facebook', 2 => 'Twitter', 3 => 'Twitch', 4 => 'House', )
array (
// whatever column names and values you have in the row
)
array (
// whatever column names and values you have in the row
)
array (
// whatever column names and values you have in the row
)
...
In your query:
$selectquery = $conn->prepare("select * from mediaservices where socialmedianame=? $wheres");
The ? represents one parameter to pass in, and the evaluation of $wheres adds another three, giving you four total parameters.
bind_param() should take a string representing the types of the variables to insert as the first parameter, and the variables themselves as the subsequent parameters.
In your bind:
$selectquery->bind_param("$bip",$otherMedia,$validarayy);
$bip evaluates to ssss and $otherMedia is a single string ("House"). You might expect $validarayy to be three strings, but rtrim() returns a string. Thus, it is only one string ("Facebook,Twitter,Twitch"). You pass through two variables when the query is expecting four:
$conn->prepare("select * from mediaservices where socialmedianame=House AND socialmedianame=Facebook,Twitter,Twitch AND socialmedianame=? AND socialmedianame=? AND socialmedianame=?"
To correct this, you'll want to convert $validarayy back to an array, and use the index for the various inputs:
$socialmarray2 = explode(',', $validarayy);
$selectquery->bind_param("$bip", $otherMedia, $socialmarray2[0], $socialmarray2[1], $socialmarray2[2]);
Also note that your sample code has a few missing semicolons; you'll need to fix these in order for your code to work correctly.
This can be seen working here.
Finally, note that even if you were to split the three strings out correctly, the selection of ... AND socialmedianame=Facebook AND socialmedianame=Twitter AND socialmedianame=Twitch will never match any results; socialmedianame can only contain one value. You're probably looking to substitute your AND statements with OR statements.
