时间戳

首页 > 解决方案 > 将 js 数组发布到 PHP 并在数据库查询中使用它

javascript - 将 js 数组发布到 PHP 并在数据库查询中使用它

问题描述

我希望我的 javascript 函数将多个变量传递给 PHP，然后让 PHP 在 select 语句中使用这些变量。目前，当我只传递字符串变量时，我的代码就可以工作，但是只要我将这一行添加AND newVar IN ('+$myVarArrayPHP+')到查询中，查询就不会从数据库中提取任何内容（肯定有一行与查询匹配的数据）。非常感谢！！

JS：

var varSring1= "test";
var varString2= "testing";
var varArray= [""] // the number of elements in the array is determined dynamically and are all strings for example: ["grape","mango","apple"]
$.ajax({
   type: 'POST',
   url: "myPHPFile.php",
   data: {
       myVar1: varSring1,
       myVar2: varString2,
       myVarArray: varArray
     },
   dataType: "json",
   success: function (response)
       if ((response[0]['var1']) != null) {
       document.getElementById("tc-unique-ID-1").value = (response[0]['var1']);}

      if ((response[0]['var2']) != null) {
      document.getElementById("tc-unique-ID-2").value = (response[0]['var2']);}
   error: function (err) {
            console.error(err.responseText);
        }
    });


}

PHP：


if(isset($_POST[myVar1]) && ($_POST[myVar2]) && ($_POST[myVarArray])){  //check if $_POST[''] exists
  $myVar1PHP= $_POST[myVar1];
  $myVar2PHP= $_POST[myVar2]; 
  $myVarArrayPHP= $_POST[myVarArray]; 

  $ret = pg_query($connection, "SELECT * FROM table 
  WHERE  var1= '$myVar1PHP' AND var2= '$myVar2PHP' AND newVar IN ('+$myVarArrayPHP+');")

  $results=array();
  while($row = pg_fetch_assoc($ret) ){
    array_push( $results,$row);
  }  


}

标签： javascriptphpsqlarrayswhere-clause

解决方案

您需要告诉 PHP 如何处理数组，例如通过使用implode（PHP 文档）：

//check if $_POST['...'] exists
if(isset($_POST["myVar1"]) && isset($_POST["myVar2"]) && isset($_POST["myVarArray"]) && isset($_POST["differentPostedVar"]) && isset($_POST["lastPostedVar"])){
    $myVar1PHP= $_POST["myVar1"];
    $myVar2PHP= $_POST["myVar2"]; 
    $myVarArrayPHP= $_POST["myVarArray"];
    $differentPostedVar = $_POST["differentPostedVar"]; // assumption
    $lastPostedVar = $_POST["lastPostedVar"];

    // newVar IN ($3, $4, $5); and so on
    $first_sql = "SELECT * FROM table WHERE  var1= $1 AND var2= $2 AND newVar IN (put_placeholders_here);";
    $results = execute_prepared_statement($connection, $first_sql, "first_sql", array($myVar1PHP, $myVar2PHP), $myVarArrayPHP);

    if(0 == count($results)) {
        $second_sql = "SELECT * FROM table WHERE differentVar= $1 AND var2= $2 AND newVar IN (put_placeholders_here);";
        $results = execute_prepared_statement($connection, $second_sql, "second_sql", array($differentPostedVar, $myVar2PHP), $myVarArrayPHP);

        if(0 == count($results)) {
            $third_sql = "SELECT * FROM table WHERE 3rdQ= $lastPostedVar;";
            $results = execute_prepared_statement($connection, $third_sql, "third_sql", array($differentPostedVar, $myVar2PHP), $myVarArrayPHP);
        }
    }

    echo json_encode($results);
}

function execute_prepared_statement($connection, $sql, $query_name, $normal_params, $in_array = null) {
    $elementsCount = count($in_array);
    $no_of_other_params = count($normal_params); // you need to start with $3 because of $myVar1PHP and $myVar2PHP

    // generate an array that holds a placeholder ($3, $4 etc.) for every value in $myVarArrayPHP
    $binding_placeholders = array();
    for($i = 0; $i < $elementsCount; $i++) {
        $binding_placeholders[] = "$" . ($i + $no_of_other_params + 1);
    }

    // array to string conversion (will produce "$3,$4,$5" etc.)
    $placeholders = implode(",", $binding_placeholders);
    // replace placeholder string with actual placeholder string 
    $sql = str_replace('put_placeholders_here', $placeholders, $sql);

    $ret = pg_prepare($connection, $query_name, $sql);

    // using array_merge to create one array having all parameters
    $parameters = array_merge($normal_params, $in_array); 

    $result = pg_execute($connection, $query_name, $parameters);

    $results=array();

    while($row = pg_fetch_assoc($ret) ){
        array_push( $results, $row );
    }

    return $results;
}

implode(',', $array);转换["grape", "mango", "apple"]为字符串：grape,mango,apple. 现在 SQL 能够处理它。

pg_prepare()准备好的语句的文档： PHP 文档

编辑

  1. 我错过"了数组的索引
  2. implode()是正确的想法，但我将它用于错误的事情，因为它会生成"grape, mango, apple"，因此您的数据库将完全查找此字符串。相反，我们需要寻找"grape", "mango", "apple".
  3. 使用splatPHP的运算符$myVarArrayPHP动态反汇编。

灵感来自https://supunkavinda.blog/php-mysqli-prepared-where-in。

2ND EDIT 由线程开启者回答另一个问题，以根据count($results)先前的语句执行多个查询。

推荐阅读