Tag Archives: sql

PDO: Execute a prepared statement using array for IN clause

Posted on by

See Example #5 here.

/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));

/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);

Escaping SQL values in PHP without using mysql_real_escape_string

Posted on by
Reply

Found this article which has a handy ‘escape’ function:

// replace any non-ascii character with its hex code.
function escape($value) {
    $return = '';
    for($i = 0; $i < strlen($value); ++$i) {
        $char = $value[$i];
        $ord = ord($char);
        if($char !== "'" && $char !== "\"" && $char !== '\\' && $ord >= 32 && $ord <= 126)
            $return .= $char;
        else
            $return .= '\\x' . dechex($ord);
    }
    return $return;
}

MySQL: Error in SQL: Commands out of sync; you can’t run this command now

Posted on by
Reply

Today I was programming MySQL via PHP and I received an error “Error in SQL: Commands out of sync; you can’t run this command now”. It turned out the problem was that a previous mulit_query had mysqli_results that hadn’t been freed. This article helped me solve the problem, and now my batch mode SQL processor looks like this:

  public function execute_batch( $sql ) {

    $this->write_count++;

    if ( ! $this->link->multi_query( $sql ) ) {

      $this->throw_error( $sql );

    }

    if ( $this->link->more_results() ) {

      do {
        $result = $this->link->use_result();
        if ( $result instanceof mysqli_result ) {
          $result->free();
        }
      }
      while ( $this->link->more_results() && $this->link->next_result() );

    }
  }

This code executes the SQL batch and then frees any mysqli_results that result from the query batch.