問(wèn)題描述

我正在嘗試使用動(dòng)態(tài) where 子句和動(dòng)態(tài)參數(shù)創(chuàng)建一個(gè)選擇查詢，但我總是收到錯(cuò)誤:

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

警告:mysqli_stmt::bind_param():類型中的元素?cái)?shù)定義字符串與綁定變量的數(shù)量不匹配

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

我真的不明白，因?yàn)榭雌饋?lái)計(jì)數(shù)沒(méi)問(wèn)題.所以這就是代碼在其粗魯格式下的真實(shí)樣子.我看不出我做錯(cuò)了什么.

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

推薦答案

因?yàn)?

1. 您正在使用用戶提供的數(shù)據(jù)，您必須假設(shè)您的查詢?nèi)菀资艿綈阂庾⑷牍舨⑶?/li>
2. 要構(gòu)建到查詢中的數(shù)據(jù)量是可變的/不確定的，并且
3. 您只是在單個(gè)表列上編寫(xiě)條件檢查

您應(yīng)該使用準(zhǔn)備好的語(yǔ)句并將所有 WHERE 子句邏輯合并到一個(gè) IN 語(yǔ)句中.

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

構(gòu)建這個(gè)動(dòng)態(tài)準(zhǔn)備好的語(yǔ)句比使用 pdo 更復(fù)雜(在語(yǔ)法方面)，但這并不意味著你需要僅僅因?yàn)檫@個(gè)任務(wù)而放棄 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']
}

對(duì)于任何正在尋找類似動(dòng)態(tài)查詢技術(shù)的人:

For anyone looking for similar dynamic querying techniques:

• SELECT 帶有動(dòng)態(tài)數(shù)量的 LIKE 條件
• INSERT 具有一個(gè) execute() 的動(dòng)態(tài)行數(shù)打電話

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

這篇關(guān)于帶有動(dòng)態(tài)參數(shù)的動(dòng)態(tài)選擇 mysqli 查詢返回錯(cuò)誤與綁定變量的數(shù)量不匹配的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！