我正在嘗試為使用者輸入編寫準備好的語句。參數數量根據使用者輸入而變化。 Oam 正在嘗試此程式碼
PHP 程式碼:
$string = "my name"; $search_exploded = explode( " ", $string ); $num = count( $search_exploded ); $cart = array(); for ( $i = 1; $i <= $num; $i ++ ) { $cart[] = 's'; } $str = implode( '', $cart ); $inputArray[] = &$str; $j = count( $search_exploded ); for ( $i = 0; $i < $j; $i ++ ) { $inputArray[] = &$search_exploded[ $i ]; } print_r( $inputArray ); foreach ( $search_exploded as $search_each ) { $x ++; if ( $x == 1 ) { $construct .= "name LIKE %?%"; } else { $construct .= " or name LIKE %?%"; } } $query = "SELECT * FROM info WHERE $construct"; $stmt = mysqli_prepare( $conn, $query ); call_user_func_array( array( $stmt, 'bind_param' ), $inputArray ); if ( mysqli_stmt_execute( $stmt ) ) { $result = mysqli_stmt_get_result( $stmt ); if ( mysqli_num_rows( $result ) > 0 ) { echo $foundnum = mysqli_num_rows( $result ); while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) { echo $id = $row['id']; echo $name = $row['name']; } } }
當我 print_r($inputArray)
時,輸出為:
Array ( [0] => ss [1] => my [2] => name )
錯誤日誌中沒有顯示錯誤。
出了什麼問題?
P粉8264299072024-03-26 14:50:12
編寫一個通用查詢處理程序並向其傳遞您的查詢、參數陣列和參數類型清單。傳回一組結果或訊息。這是我自己的 mysqli 個人版本(我主要使用 PDO,但也為此設定了類似的功能)。對插入、更新和刪除執行相同的操作。然後只需維護您的一個庫並將其用於您所做的所有事情:) 請注意,如果您以此開始,您可能希望更好地處理連接錯誤等。
connect_error) { return false; } return $con; } // generic select function. // takes a query string, an array of parameters, and a string of // parameter types // returns an array - // if $retVal[0] is true, query was successful and returned data // and $revVal[1...N] contain the results as an associative array // if $retVal[0] is false, then $retVal[1] either contains the // message "no records returned" OR it contains a mysql error message function selectFromDB($query,$params,$paramtypes){ // intitial return; $retVal[0]=false; // establish connection $con = getDBConnection(); if(!$con){ die("db connection error"); exit; } // sets up a prepared statement $stmnt=$con->prepare($query); $stmnt->bind_param($paramtypes, ...$params); $stmnt->execute(); // get our results $result=$stmnt->get_result()->fetch_all(MYSQLI_ASSOC); if(!$result){ $retVal[1]="No records returned"; }else{ $retVal[0]=true; for($i=0;$iclose(); return $retVal; } $myusername=$_POST['username']; $mypassword=$_POST['password']; // our query, using ? as positional placeholders for our parameters $q="SELECT useridnum,username FROM users WHERE username=? and password=?"; // our parameters as an array - $p=array($myusername,$mypassword); // what data types are our params? both strings in this case $ps="ss"; // run query and get results $result=selectFromDB($q,$p,$ps); // no matching record OR a query error if(!$result[0]){ if($result[1]=="no records returned"){ // no records // do stuff }else{ // query error die($result[1]); exit; } }else{ // we have matches! for($i=1;$i $val){ print("key:".$key." -> value:".$val); } } } ?>
P粉7878060242024-03-26 12:36:45
%
環繞參數,而不是佔位符。
我的程式碼片段將使用物件導向的 mysqli 語法,而不是您的程式碼演示的過程語法。
首先您需要設定必要的成分:
我將把 #2 和 #3 組合成一個變量,以便使用 splat 運算子(...
)進行更簡單的「解包」。資料類型字串必須是第一個元素,然後一個或多個元素將代表綁定值。
作為邏輯包含,如果 WHERE 子句中沒有條件,則使用準備好的語句沒有任何好處;直接查詢表即可。
程式碼:(PHPize.online 示範)
$string = "Bill N_d Dave"; $conditions = []; $parameters = ['']; foreach (array_unique(explode(' ', $string)) as $value) { $conditions[] = "name LIKE ?"; $parameters[0] .= 's'; // $value = addcslashes($value, '%_'); // if you want to make wildcards from input string literal. https://stackoverflow.com/questions/18527659/how-can-i-with-mysqli-make-a-query-with-like-and-get-all-results#comment132930420_36593020 $parameters[] = "%{$value}%"; } // $parameters now holds ['sss', '%Bill%', '%N_d%', '%Dave%'] $query = "SELECT * FROM info"; if ($conditions) { $stmt = $mysqli->prepare($query . ' WHERE ' . implode(' OR ', $conditions)); $stmt->bind_param(...$parameters); $stmt->execute(); $result = $stmt->get_result(); } else { $result = $conn->query($query); } foreach ($result as $row) { echo "{$row['name']}\n"; }
對於任何尋找類似動態查詢技術的人: