Sql query using pdo in mysql

Question

I have a very simple select query using pdo but it is not working.

The generic sql query is

$sql = "select * from table where type != 'address' and type != 'multipleimage'";

Now in pdo I have

$fieldtype = array('address','multipleimage');
             $query = $this->db->prepare("SELECT * from table where 
(type not in $type) and (userid !=:userid) ");

$query->execute(array(':userid' => 2, $fieldtype ));

Now getting notice + warning

Notice is ' Array to string conversion....' Warning is 'Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation.....'

Answer 1

I have created my own ORM like class called DBConnection.php. It does all CRUD functionalities for any table in the specified database. It uses PDO as well. Feel free to play with it, customize and use in your code if you would like to....

---

<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of DBConnection
 *
 * @author alemayehu
 */
require_once '../lib/PHPDebug.php';

class DBConnection {
    private static $_instance = null;
    private $_pdo;
    private $_query;
    private $_error = false;
    private $_results;
    private $_count = 0;

    private function __construct() {
        try{
            $this->_pdo = new PDO("mysql:host=".Config::get("mysql/host").";dbname=".Config::get("mysql/db"),
                    Config::get("mysql/username"),Config::get("mysql/password"));

        } catch (Exception $ex) {
            PHPDebug::printLogText("Connection Failed : ". $ex->getMessage() , "../lib/debug.txt");
            die($ex->getMessage());
        }
    }

    public static function getInstance(){
        if(!isset(self::$_instance)){
            self::$_instance = new DBConnection();
        }
        return self::$_instance;
    }

    public function fetchResultSet($sql, $params = array()){
        //var_dump($params);passed
        $this-> _error = false;
        if($this-> _query = $this->_pdo->prepare($sql)){
            $x = 1;
            if(count($params)){
                foreach($params as $param){
                    $this->_query->bindValue($x, $param);
                    $x++;
                }

            }else{
                echo 'something wrong with the array';
            }

            var_dump($this->_query);

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

    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){
                    $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 error(){
        return $this->_error;
    }

    private 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;//was 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 insert($table, $fields = array()){
        if(count($fields)){
            $keys = array_keys($fields);
            $values = '';
            $x = 1;

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

            $sql = "INSERT INTO {$table} (`" . implode('`, `', $keys) . "`) VALUES( {$values} )";
            //var_dump($sql);
            if( ! $this->query($sql, $fields)->error()){
                return true;
            }
        }
        return false;
    }

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

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

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


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

    }

    public function fetchAllRecords($table, $where){
        return $this->query("SELECT * FROM {$table} WHERE $where");
    }

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

    public function getResults(){
        return $this->_results;
    }

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

}//end class

Answer 2

Why don't you use NOT IN clause like:

$sql = "select * from table where type not in ('address','multipleimage')";

Something like this, you may have to do some minor changes

<?php
$fieldtype = array('address','multipleimage');
$inQuery = implode(',', array_fill(0, count($fieldtype), '?'));

$db = new PDO(...);
$stmt = $db->prepare(
    'select * from table where type not in(' . $inQuery . ')'
);

// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $fieldtype);

$stmt->execute();
?>