带有动态参数的动态选择 mysqli 查询返回错误与绑

Dynamic select mysqli query with dynamic parameters returns error doesn#39;t match number of bind variables(带有动态参数的动态选择 mysqli 查询返回错误与绑定变量的数量不匹配)

问题描述

我正在尝试使用动态 where 子句和动态参数创建一个选择查询，但我总是收到错误:

I'm trying to create a select query with dynamic where clause and dynamic parameters but I always get error :

警告:mysqli_stmt::bind_param():类型中的元素数定义字符串与绑定变量的数量不匹配

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();

推荐答案

因为:

1. 您正在使用用户提供的数据，您必须假设您的查询容易受到恶意注入攻击并且
2. 要构建到查询中的数据量是可变的/不确定的，并且
3. 您只是在单个表列上编写条件检查

您应该使用准备好的语句并将所有 WHERE 子句逻辑合并到一个 IN 语句中.

You should use a prepared statement and merge all of the WHERE clause logic into a single IN statement.

构建这个动态准备好的语句比使用 pdo 更复杂(在语法方面)，但这并不意味着你需要仅仅因为这个任务而放弃 mysqli.

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.

$mediaArray ='Facebook,Twitter,Twitch,';
$otherMedia = 'House';

$media = array_unique(explode(',', $mediaArray . $otherMedia));
$count = count($media);

$conn = new mysqli("localhost", "root", "", "myDB");
$sql = "SELECT * FROM mediaservices";
if ($count) {
    $stmt = $conn->prepare("$sql WHERE socialmedianame IN (" . implode(',', array_fill(0, $count, '?')) . ")");
    $stmt->bind_param(str_repeat('s', $count), ...$media);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($sql);
}
foreach ($result as $row) {
    // access values like $row['socialmedianame']
}

对于任何正在寻找类似动态查询技术的人:

For anyone looking for similar dynamic querying techniques:

• SELECT 带有动态数量的 LIKE 条件
• INSERT 具有一个 execute() 的动态行数打电话

• SELECT with dynamic number of LIKE conditions
• INSERT dynamic number of rows with one execute() call

这篇关于带有动态参数的动态选择 mysqli 查询返回错误与绑定变量的数量不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！