Re: Making mysqli easier to use with parameters

On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> wrote: > applying the ids array as ... will handle the by-reference > passing. That does help, thanks. But I still wonder if the ability to pass in a single array of parameters to $statement->execute() would remove a step, and be a bit easier to use for all queries. If this was for a search form, where it dynamically creates a SELECT with a variety of different parameters, it gets tricky again. And there was the thing I tacked onto the end, where I would like to use a $statement->result property to skip the use of $statement->get_result(): while ($row = mysqli_fetch_assoc($statement->result)) { } Craig > On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> wrote: > >> <?php >> >> $in_sql = implode(',', array_fill(0, count($ids), '?')); >> >> $sql = 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')'; >> >> if ($statement = $db->prepare($sql)) { >> >> $params = [str_repeat('i', count($ids))]; >> foreach ($ids as $key => $value) { >> $params[] = &$ids[$key]; // Must be a reference, not ideal. >> } >> call_user_func_array(array($statement, 'bind_param'), $params); >> >> $statement->execute(); >> >> $result = $statement->get_result(); >> >> while ($row = mysqli_fetch_assoc($result)) { >> print_r($row); >> } >> >> } >> >> ?> > > Written in my email client without error handling code, so apologies > if it's not quite correct: > > $in = join(',', array_fill(0, count($ids), '?')); > $select = "SELECT id, name FROM user WHERE id IN ({$in});"; > $statement = $mysqli->prepare($select); > $statement->bind_param(str_repeat('i', count($ids)), ...$ids); > $statement->execute(); > > This is part of my [highest score answer on StackOverflow][1]. > Critically, applying the ids array as ... will handle the > by-reference passing. > > I don't think we need to improve the ergonmics of mysqli for this case > specifically, as it's always going to require some dynamic SQL > generation because of the variable number of parameters to bind. > > [1]: https://stackoverflow.com/a/23641033/538216