Using fewer foreach loops to bind values to parameters in PDO

I want to make a query to order by the IN clause items. I read from another thread that I can use ORDER BY FIELD(a.id, val1,val2......) When binding values to parameters for two occurrences of $in, I ended up using two foreach loops:

foreach ($tid as $t) {                 
        $users->bindValue($c++, $t);
      }
      foreach ($tid as $t) {
        $users->bindValue($c++, $t);
      }

Would you prefer other solutions? Is there any way to reduce it to one foreach loop?

try 
    {
      $dbh = new PDO("mysql:host=$hostname;dbname=$databasename", $username, $password);
      $tid = $_POST["tid"];
      $comma = "";
      $in  = str_repeat('?,', count($tid) - 1) . '?';    
      $sql = "
              SELECT b.name,a.users
              FROM `table1` a INNER JOIN
              `table2` b ON a.id = b.id 
              WHERE a.id IN ($in)
              GROUP BY a.id
              ORDER BY FIELD(a.id, $in)
             ";
      $users = $dbh->prepare($sql);
      $users->bindValue(1, $_SESSION["user_id"]);  
      $c = 2;
      foreach ($tid as $t) {                 
        $users->bindValue($c++, $t);
      }
      foreach ($tid as $t) {
        $users->bindValue($c++, $t);
      }
      $users->execute();
      $dbh = null;

    }
    catch(PDOException $e) 
    {
     echo $e->getMessage();
    }

$_POST["tid"] Content Example:

Array
(
    [0] => 215
    [1] => 391
)

The PDOStatement class (which is what PDO::prepare returns, allows you to pass an array of values to the execute method. If you use that, you don't have to loop over any arrays, calling bindValue over and over:

$vals = array(
    $_SESSION['user_id']
);
$vals = array_merge(
    $vals,
    $_POST['tid']
);
$in = array_fill(
    0, count($vals),
    '?'
);//construct array containing ?
$stmt = $pdo->prepare(
    'SELECT b.name,a.users
     FROM `table1` a
       INNER JOIN `table2` b
         ON a.id = b.id 
     WHERE a.id IN ('.implode(',',$in).')
     GROUP BY a.id
     ORDER BY FIELD(a.id, '.implode(',',$in).')'
);
$stmt->execute($vals);

The added benefit of this approach is that you're able to use named placeholders, with just a few minor adjustments:

$vals = array(
    ':sessionUid' => $_SESSION['user_id']
);
foreach ($_POST['tid'] as $idx => $val) {
    $vals[':uid'.$idx] => $val;
}
$in = implode(',', array_keys($vals));
$stmt = $pdo->prepare(
    'SELECT b.name,a.users
     FROM `table1` a
       INNER JOIN `table2` b
         ON a.id = b.id 
     WHERE a.id IN ('.$in.')
     GROUP BY a.id
     ORDER BY FIELD(a.id, '.$in.')'
);
$stmt->execute($vals);

Of course, there are a few tradeoffs. Nothing too bad, but still:

• PDOStatement::execute(array $bind) binds all of the values as PDO::PARAM_STR (ie string values). This implies you can't use array binding if you are using placeholders in, for example, LIMIT clauses. In those cases, a loop + $stmt->bindValue(':offset', $value, PDO::PARAM_INT); is required. Writing a wrapper that does this for you is not uncommon, and in some cases desirable.

• re-use of placeholders is only supported if you're using PDO's emulated prepared statements:

Because you seem to be re-using the same placeholders here, you cannot disable emulated prepares. MySQL does not support the re-use of named placeholders. If you do disable emulated prepares, you will need to add a loop:

//create vals as before
$in = array_keys($vals);
$query = 'SELECT b.name,a.users
     FROM `table1` a
       INNER JOIN `table2` b
         ON a.id = b.id 
     WHERE a.id IN ('.implode(','$in).')
     GROUP BY a.id
     ORDER BY FIELD(a.id, ';
foreach ($in as $idx => $key) {
    $vals[$key.$idx] = $vals[$key];//add value a second time, using a different key
    $in[$idx] .= $idx;//change values in $in
}
$query .= implode(',', $in).')';//add new placeholders to query
$stmt = $pdo->prepare($query);
$stmt->execute($vals);//should work now

Apart from that, I'd suggest you set PDO's error mode so that it actually throws exceptions. As it stands, PDO will throw an exception if the connection fails, but not if any of the other calls fail (prepare, execute,...).

To fix this, either call PDO::setAttribute, or pass an array to the constructor:

$pdo = new PDO(
    $dsn,
    $user,
    $pass,
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    )
);
//or
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

All in all, loops like you have do very little harm, avoiding them can improve readability, but their impact on performance is so minute that getting rid of them, for the sake of optimization is not a great idea. Remember "Premature optimization is the root of all evil".

StackExchange Code Review Q#61376, answer score: 3