fetchall() does not work in Codeigniter 3 configured pdo driver

Question

I can't fetch result using PDO driver in Codeigniter3.

Configured pdo driver in config/database.php as below

$db['default'] = array(
    'dsn'   => 'mysql:host=localhost;dbname=my_db',
    'hostname' => '',
    'username' => 'root',
    'password' => 'root',

...

$autoload['libraries'] = array('database','session');

...

  $query =
            "SELECT * FROM abc LIMIT 10";
            var_dump($this->db);

       $q=$this->db->query($query)->fetchAll();

but results in

An uncaught Exception was encountered

Type: Error

Message: Call to undefined method CI_DB_pdo_result::fetchAll()

But if i try by creating PDO Object directly i can fetch data

$dsn="mysql:host=localhost;dbname=my_db";
        $username = "root";
        $password = "root";
        try
        {
            $db = new PDO($dsn,$username,$password);
            echo "connected";

            $query =
                "SELECT * FROM abc ";

            $q=$db->query($query)->fetchAll();


            echo "<pre>";
            var_dump($q);
            echo "</pre>";

    }
    catch (PDOException $e)
    {
    }

What should i do to fix pdo configured in application/database.php?

Change `'dsn' => 'mysql:host=localhost;dbname=my_db',` to `'pgsql:host=localhost;dbname=my_db'`, and check — Saty, Sep 27 '17 at 11:17
@Saty Thankyou for reply. i tried what you suggested but still same error — ios, Sep 27 '17 at 11:21
there is no fetchall function afaik ? just simply try to `$db->query($query)->result();` — Atural, Sep 27 '17 at 11:23
@sintakonte http://php.net/manual/en/pdostatement.fetchall.php fetchall() works when i manually instantiate pdo db object,please check last part of question . result() works with codeigniter pdo object — ios, Sep 27 '17 at 11:29

score 3 · Accepted Answer · answered Sep 27 '17 at 11:51

3

The codeigniter database functions and Query Builder class do not have a function called fetchAll. Anytime you use $this->db->... you are using database class.

I recommend studying the documentation.

Yes, if you can create and use a PDO object directly.

answered Sep 27 '17 at 11:51

DFriend

8,869
1
13
26

Thankyou for reply . If i want to use PDO directly , should i place $db = new PDO($dsn,$username,$password); in every model class ? . Is that bad ? i am a newbie . – ios Sep 27 '17 at 11:55
imho you should always use the Query Builder no matter what – Atural Sep 27 '17 at 12:10
@sintakonte can i define prepared statements with Query Builder? or i dont have to use prepared statement if i set pdodriver? – ios Sep 27 '17 at 12:13
1

of course you can - just search for something like that (e.g. https://stackoverflow.com/questions/14156421/how-can-i-use-prepared-statements-in-codeigniter) – Atural Sep 27 '17 at 12:54
1

CI has a PDO driver. Read the docs and/or search SO for "[codeigniter]PDO" If you use the PDO driver you are still using the CI db classes and the associated methods.Direct calls to PDO functions won't work. – DFriend Sep 27 '17 at 13:00

score 0 · Answer 2 · answered Sep 06 '18 at 02:17

There's a system database file called DB.php. Everyone seems to just modify (hack) it towards the end of the file. I've write a long bit of code for my personal PDO connection, but the below line is the basic idea.

$DB = new PDO($params['dbdriver'].':host='.$params['hostname'].';dbname='.$params['database'].';', $params['username'], $params['password']);

Comment out the section of code from:

file_exists($driver_file) OR show_error('Invalid DB driver');

to

$DB->initialize();

and then return $DB

fetchall() does not work in Codeigniter 3 configured pdo driver

2 Answers2