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.....'

You can't have the same key twice in an array, as of this `array(':type' => 'address', ':type' => 'multipleimage')` results in `array(':type' => 'multipleimage')`. And in PDO placeholders like `?` and `:name` only take _primitive_ values (strings, number, ...) but not complex objects like arrays. There is nothing in PDO that will _magically_ create a `AND` or `OR` condition for you. — t.niese, Aug 02 '14 at 11:39
If the values are hardcoded prepared statements are a bit overkill. — Mihai, Aug 02 '14 at 11:45
I have edited the questions with an array which I am using inside the execute but I think the format is wrong. — Raj, Aug 02 '14 at 12:13

avisheks · Accepted Answer · 2014-08-02T12:17:21.067

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();
?>

edited Aug 02 '14 at 12:17

answered Aug 02 '14 at 11:30

avisheks

1,178
10
27

you can have this ref: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition – avisheks Aug 02 '14 at 11:35
I should change the question as I have few other params.I followed the link and was trying to implement that array thing but not working. – Raj Aug 02 '14 at 11:59
It should, yes please edit the question and also add the error if any – avisheks Aug 02 '14 at 12:05
1

Solved..:)..Thanks a lot Avishek – Raj Aug 02 '14 at 12:16

score 0 · Answer 2 · answered Aug 02 '14 at 14:30

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

Sql query using pdo in mysql

2 Answers2