PHP PDO SQL Server database issue

Question

I'm using SQL Server as a database and PHP PDO to connect, When creating a registration page, I get this error when executing the query

Notice: Array to string conversion in C:\webdev\classes\DB.php on line 36 There was a problem creating an account.PHP Notice: Array to string conversion in C:\webdev\classes\DB.php on line 36

Line 36 - $this->_query->bindValue($x, $param);

<?php
  class DB {
public static $instance = null;

private     $_pdo = null,
            $_query = null,
            $_error = false,
            $_results = null,
            $_count = 0;

private function __construct() {
    try {

        $this->_pdo = new PDO('sqlsrv:server=' . Config::get('sqlsrv/servername') . ';database=' . Config::get('sqlsrv/db'), Config::get('sqlsrv/username'), Config::get('sqlsrv/password'));
    } catch(PDOExeption $e) {
        die($e->getMessage());
    }
}

public static function getInstance() {
    // Already an instance of this? Return, if not, create.
    if(!isset(self::$instance)) {
        self::$instance = new DB();
    }
    return self::$instance;
}

public function query($sql, $params = array()) {

    $this->_error = false;

    if($this->_query = $this->_pdo->prepare($sql)) {
        $x = 1;
        if(count($params)) {
            foreach($params as $param) {
/* Line 36 */       $this->_query->bindValue($x, $param);
                $x++;
            }
        }

        if($this->_query->execute()) {
            $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
            $this->_count = $this->_query->rowCount();
        } else {
            $this->_error = true;
        }
    }

    return $this;
}

public function get($table, $where) {
    return $this->action('SELECT *', $table, $where);
}

public function delete($table, $where) {
    return $this->action('DELETE', $table, $where);
}

public function action($action, $table, $where = array()) {
    if(count($where) === 3) {
        $operators = array('=', '>', '<', '>=', '<=');

        $field      = $where[0];
        $operator   = $where[1];
        $value      = $where[2];

        if(in_array($operator, $operators)) {
            $sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";

            if(!$this->query($sql, array($value))->error()) {
                return $this;
            }

        }

        return false;
    }
}

public function insert($table, $fields = array()) {
    $keys   = array_keys($fields);
    $values = null;
    $x      = 1;

    foreach($fields as $value) {
        $values .= "?";
        if($x < count($fields)) {
            $values .= ', ';
        }
        $x++;
    }

    $sql = "INSERT INTO {$table} (`" . implode('`, `', $keys) . "`) VALUES ({$values})";

    if(!$this->query($sql, $fields)->error()) {
        return true;
    }

    return false;
}

public function update($table, $id, $fields = array()) {
    $set    = null;
    $x      = 1;

    foreach($fields as $name => $value) {
        $set .= "{$name} = ?";
        if($x < count($fields)) {
            $set .= ', ';
        }
        $x++;
    }

    $sql = "UPDATE users SET {$set} WHERE id = {$id}";

    if(!$this->query($sql, $fields)->error()) {
        return true;
    }

    return false;
}

public function results() {
    // Return result object
    return $this->_results;
}

public function first() {
    return $this->_results[0];
}

public function count() {
    // Return count
    return $this->_count;
}

public function error() {
    return $this->_error;
}}

Why would this be, I used the same code and had a mysql database and it sent the data to the db no problems, why would it be the case for SQL Server?

Answer 1

One of the $param iterations is coming in as an array probably:

if(count($params)) {
    foreach($params as $param) {

        if(is_array($param) || is_object($param)){ $param=''; }

        $this->_query->bindValue($x, $param);
        $x++;
    }
}

Recommendation for debugging public function insert()

// add a debug parameter
public function insert($table, $fields = array(), $debug = false) {

    $return = false;

    if(is_array($fields) && count($fields) > 0 && $table != ''){

        // build SQL and debug SQL
        $sql = "INSERT INTO '$table' (";
        $debug_sql = $sql;

        // declare variables
        $sql_fields = '';
        $values = '';
        $debug_values = '';

        foreach($fields as $k=>$v) {

            // encase fields and values in quotes
            $sql_fields.= "'$k',";
            $values.= "?,";
            $debug_values.= "'$v',";
        }

        // remove trailing commas
        $sql_fields = substr($sql_fields, 0, -1);
        $values= substr($values, 0, -1);
        $debug_values= substr($debug_values, 0, -1);

        // finish SQL and debug SQL
        $sql.= "$sql_fields) VALUES ($values)";
        $debug_sql.= "$sql_fields) VALUES ($debug_values)";

        if($debug === true) {
            $return = $debug_sql;
        }
        else {
            if(!$this->query($sql, $fields)->error()) {
                $return = true;
            }
        }
    }

    return $return;
}

// now change the insert call to look like this
die($this->_db->insert('dbo.users', $fields, true)); // <-- notice the true parameter

/**
 * Use the output to directly run the SQL from the MSSQL admin console or whatever they call it and it will provide a much more useful error description
 */