PHP PDO post string list IN Where Clause

Question

I almost have the following code working, but unfamiliar with how to post my list from enduser to $ids in array form like my test. The end user would send a list to me as "3,5,7,8...)

How do I simulate "$ids = array(1,2,3)" when converting to a POST statement?

<?php
//1. Create a database connection

require_once('config.php');
    $mysql_host = DB_HOST;
    $mysql_database = DB_NAME;
    $mysql_username = DB_USER;
    $mysql_password = DB_PASS;

$ids = $_POST["idsvar"]; //Doesn't return values
//$ids =  array(1,2,3); //This does work when used for testing purposes

$inQuery = implode(',', array_fill(0, count($ids), '?'));


try {
    $conn = new PDO("mysql:host=$mysql_host; dbname=$mysql_database", $mysql_username, $mysql_password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

    //2. Perform database query if Connected Successfully
    $stm = $conn ->prepare(
    'SELECT `schema`.`table`.`column1` AS `DiffNameA`,
    `schema`.`table`.`column2` AS `DiffNameB`
     FROM `schema`.`table`
     WHERE id IN(' . $inQuery . ')');

foreach ($ids as $k => $id)
    $stm->bindValue(($k+1), $id);
    $stm->execute();

$field = $stm->fetchAll();

foreach ($field as $row) {
print $row["DiffNameA"] . "|" .$row["DiffNameB"] ."\n\r"; //extra comma so can have notes hidden area
}   

  $conn = null;        // Disconnect    
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage().'<br />';
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
    }
?>

Thank you for any help.

===============================================================

In case it helps anyone, this is the complete script:

<?php
//1. Create a database connection

require_once('config.php');
    $mysql_host = DB_HOST;
    $mysql_database = DB_NAME;
    $mysql_username = DB_USER;
    $mysql_password = DB_PASS;



$ids = explode(',', $_POST["idsvar"]);
$inQuery = implode(',', array_fill(0, count($ids), '?'));


try {


    $conn = new PDO("mysql:host=$mysql_host; dbname=$mysql_database", $mysql_username, $mysql_password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

    //2. Perform database query if Connected Successfully
    $stm = $conn ->prepare(
    'SELECT `schema`.`table`.`column1` AS `DiffNameA`,
    `schema`.`table`.`column2` AS `DiffNameB`
     FROM `schema`.`table`
     WHERE id IN(' . $inQuery . ')');

foreach ($ids as $k => $id)
    $stm->bindValue(($k+1), $id);
    $stm->execute();

$field = $stm->fetchAll();

foreach ($field as $row) {
print $row["DiffNameA"] . "|" .$row["DiffNameB"] ."\n\r"; //extra comma so can have notes hidden area
}


  $conn = null;        // Disconnect    
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage().'<br />';
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
    }
?>

I was planning to send a string of variables separated by commas. — newpie, Jul 11 '16 at 01:06
Strange, when I tested it with my actual table data I just listed ids like above array(1,2,3) and it returned data correctly. So I can't just insert a variable like "1,2,3" for idsvar and have it work I guess? — newpie, Jul 11 '16 at 01:11
Possible duplicate of [Binding parameters for WHERE IN clause with PDO](http://stackoverflow.com/questions/7044144/binding-parameters-for-where-in-clause-with-pdo) — Drew, Jul 11 '16 at 01:17
@Drew The OP is trying to use multiple placeholders here so I think that is a different issue. — chris85, Jul 11 '16 at 01:18
@Drew `$inQuery = implode(',', array_fill(0, count($ids), '?'));` will be generating `?,?, etc` so the prepared query would be proper (`IN(' . $inQuery . ')'`). Then the OP is binding the multiple values as he/she should `$stm->bindValue(($k+1), $id);` but the `POST` is just not an array so the `implode` isn't working as expected. In the dupe the OP was using one placeholder for all values. — chris85, Jul 11 '16 at 01:22
Thank you for your help both of you, I am new to PHP PDO. The answer below worked when I tested. — newpie, Jul 11 '16 at 01:25
ah the foreach loop. I see said the blind man. So it is a dupe of some other. — Drew, Jul 11 '16 at 01:32
I tried to change the title to be more appropriate for future searches, if you have a better title just let me know. Thanks again — newpie, Jul 11 '16 at 01:33

score 1 · Accepted Answer · answered Jul 11 '16 at 01:15

Your $_POST["idsvar"] is a string, not an array. Use explode to split it to an array at each comma.

$test = explode(',', '1,2,3');

Demo: https://eval.in/603133

In your case:

$ids = explode(',', $_POST["idsvar"]);

The count($ids) works in your static example because you defined $ids as an array ($ids = array(1,2,3)).

or if you wanted to validate the input as well you could use a regex.

(\d+)(?:,|\z)

https://regex101.com/r/dI5fN4/1

PHP PDO post string list IN Where Clause

1 Answers1