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
– t.niese, Commented Aug 2, 2014 at 11:39
If the values are hardcoded prepared statements are a bit overkill. — Mihai
– Mihai, Commented Aug 2, 2014 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
– Raj, Commented Aug 2, 2014 at 12:13

avisheks · Accepted Answer · 2014-08-02 12:17:21Z

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 2, 2014 at 12:17

answered Aug 2, 2014 at 11:30

avisheks

1,1801 gold badge11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

avisheks Over a year ago

you can have this ref: stackoverflow.com/questions/920353/…

Raj Over a year ago

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.

avisheks Over a year ago

It should, yes please edit the question and also add the error if any

Raj Over a year ago

Solved..:)..Thanks a lot Avishek

Mahder · Accepted Answer · 2014-08-02 14:30:34Z

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

Collectives™ on Stack Overflow

Sql query using pdo in mysql

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related