PDO Class and MYSQL request

Question

I have a PDO class wrapper:

class DB {

        private $dbh;
        private $stmt;

        public function __construct($user, $pass, $dbname) {

            $dsn = 'mysql:host=localhost;dbname=' . $dbname;

            $options = array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                PDO::ATTR_PERSISTENT => true
            );

            try {
                $this->dbh = new PDO($dsn, $user, $pass, $options);
            }
            catch (PDOException $e) {
                echo $e->getMessage();
                die();
            }
        }

        public function query($query) {
            $this->stmt = $this->dbh->prepare($query);
            return $this;
        }

        public function bind($pos, $value, $type = null) {

            if( is_null($type) ) {
                switch( true ) {
                    case is_int($value):
                        $type = PDO::PARAM_INT;
                        break;
                    case is_bool($value):
                        $type = PDO::PARAM_BOOL;
                        break;
                    case is_null($value):
                        $type = PDO::PARAM_NULL;
                        break;
                    default:
                        $type = PDO::PARAM_STR;
                }
            }

             $this->stmt->bindValue($pos, $value, $type);
             return $this;
        }

        public function execute() {
            $this->stmt->execute();
        }

        public function resultset() {
            $this->execute();
            return $this->stmt->fetchAll(PDO::FETCH_ASSOC);

        }

        public function single() {
            $this->execute();
            return $this->stmt->fetch();
        }
    }

Question 1: I have request below:

$ids_set = implode(",", $ids); // return 2,4
$sql = 'SELECT `id`, `qty` FROM `products` WHERE `id` IN (:products_ids) ORDER BY `id`';
$arr = $this->db->query($sql)->bind(":products_ids", $ids_set)->resultset();

But this request will return only one element in the array:

Array
(
    [0] => Array
        (
            [id] => 2
            [qty] => 1
        )

)

But it should return two elements. Why and how to modify my class?

Question 2

Is my class bind function safe for db injections?

Question 3

I have dynamic query:

$sql    = 'SELECT COUNT(*) FROM `orders` WHERE 1=1';

if ($filter["order"] != 0) {
    $sql .= ' AND `gatewayid` = '.intval($filter["order"]).'';
}

$count  = $this->db->query($sql)->single()[0];

How can I use my bind function in this case? Thanks!

*"I have a PDO class wrapper"* <- this, don't do this. In particular, don't store statements (`PDOStatement`) as class members. They are transitory and should be used and discarded. — Phil, Sep 04 '13 at 05:46
`catch (PDOException $e) {echo $e->getMessage(); die();}` Also, don't do this either — Phil, Sep 04 '13 at 05:47
possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — Phil, Sep 04 '13 at 05:53

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Frankly, I find your class quite useless at the moment. You can have all the same with raw PDO:

$arr = $this->db->prepare($sql)->execute([":product_id" => $id])->fetchAll();

Speaking of binding complex data types - the only sane solution is to devise additional placeholder types. And to support such placeholders would be a real purpose of this class.

Question 1

You have to create a string with placeholders dynamically, as explained in this answer

Question 2

Yes

Question 3

You have to create your query dynamically, as well as array with data to bind
Shown with PDO as your class is quite unreliable for this:

$sql  = 'SELECT COUNT(*) FROM `orders` WHERE 1=1';
$data = [];
if ($filter["order"] != 0) {
    $sql .= ' AND `gatewayid` = :getewayid';
    $data[":getewayid"] = $filter["order"];
}
$count = $this->db->prepare($sql)->execute($data)->fetchColumn();

PDO Class and MYSQL request

1 Answers1

Question 1

Question 2

Question 3