javascript - Posting a js array to PHP and using it in a database query

Solution:

You need to tell PHP how to deal with the array, e.g. by usingimplode (PHP documentation):

//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;
}
Copy code

implode(',', $array); converts["grape", "mango", "apple"] to a string:grape,mango,apple. Now SQL is able to deal with it.

Documentation for thepg_prepare() prepared statement: PHP Documentation

EDIT

1. I was missing the" around the indices of the arrays
2. implode() was the right idea but I used it for the wrong thing because it will generate"grape, mango, apple" so your database will look exactly for this string. Instead, we need to look for"grape", "mango", "apple".
3. Using the operator of PHP to disassemble$myVarArrayPHP dynamically.

Inspiration from https://supunkavinda.blog/php-mysqli-prepared-where-in.

2ND EDIT Answer to another question by thread opener to execute several queries based oncount($results) of previous statements.