How can I use PDO in CodeIgniter 2?

Question

I would like to use PDO instead of the built-in database-classes. But how can I do it? I tried to do the modifications suggested in a blog post. I commented out a part of system\database\DB.php and also added:

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

return $DB;

But now my PHP-code crashes if I do $this->load->database(); in a Controller. So how should I use PDO and execute database queries using PDO in CodeIgniter?

I have also tried to use this code in a Controller:

foreach($this->db->query('SELECT * FROM users') as $row) {
    print_r($row);
}

but it didn't work.

I believe there is a config issue in your setup. The blog post has worked step by step for over 100 developers so far. Perhaps the database credentials are incorrect? Otherwise I tried to add some clarification below. Will help you to solve this. — csi, May 04 '11 at 16:28

score 16 · Accepted Answer · edited Mar 10 '13 at 02:36

16

$stmt = $this->db->prepare("SELECT * FROM users");  
$stmt->execute();  
foreach ($stmt->fetch(PDO::FETCH_ASSOC) as $row) {
      print_r($row);
}

Or:

foreach ($this->db->query("SELECT * FROM users") as $row) {  
     print_r($row)."\n";
}

Learn more about 3 PDO database calls here...

PDO Query
PDO Exec
PDO Prepare

EDIT: Also check your app/config/database.php file for the following settings:

$active_group = 'default';  
$active_record = FALSE;  

$db['default']['hostname'] = 'YOURHOSTNAME';  
$db['default']['username'] = 'YOURUSERNAME';  
$db['default']['password'] = 'YOURPASSWORD';  
$db['default']['database'] = 'YOURDATABASE';  
$db['default']['dbdriver'] = 'mysql';  
$db['default']['dbprefix'] = '';  
$db['default']['pconnect'] = TRUE;  
$db['default']['db_debug'] = TRUE;  
$db['default']['cache_on'] = FALSE;  
$db['default']['cachedir'] = '';  
$db['default']['char_set'] = 'utf8';  
$db['default']['dbcollat'] = 'utf8_general_ci';  
$db['default']['swap_pre'] = '';  
$db['default']['autoinit'] = TRUE;  
$db['default']['stricton'] = FALSE;

edited Mar 10 '13 at 02:36

Ram

143,282
16
168
197

answered May 04 '11 at 15:04

csi

9,018
8
61
81

It doesn't work, I get this error message from CodeIgniter: `Message: Undefined property: Mycontroller::$db` doesn't I have to load the `$db` variable in some way? – Jonas May 04 '11 at 15:36
1

From that blog post, did you follow step 2? Be sure that `return $DB;` is still active -> NOT COMMENTED OUT. Also, did you autoload the database library? Otherwise add `$this->load->database();` to your constructor. – csi May 04 '11 at 16:03
Yes, I did as you wrote on step 2. But when I run `$this->load->database();` in my controller it doesn't work. I get "HTTP 500 Internal Server Error". – Jonas May 04 '11 at 16:09
1

Is this in your app/config/autoload.php file? `$autoload[‘libraries’] = array(‘database’);` If so, try removing `$this->load->database();` from any models / controllers. Finally, confirm your database credentials are correct. Perhaps there is an issue with hostname, server, password and database. – csi May 04 '11 at 16:21
No, I have it like: `$autoload['libraries'] = array();` – Jonas May 04 '11 at 16:26
My database credentials are correct since it works with the default CodeIgniter settings, before I edited `system\database\DB.php` as in the blog post. – Jonas May 04 '11 at 16:29
1

Last thoughts... 1) include `$autoload[‘libraries’] = array(‘database’);` 2) check your db settings as outlined above 3) re-copy and paste in the DB.php file as outlined in the blog post. Have verification from over 100 developers that this has worked so there must be a missing configuration somewhere... – csi May 04 '11 at 16:32
I had `$active_record = TRUE;` but changed to `FALSE;` now. But it didn't solve the problem. I have also changed to `$autoload['libraries'] = array('database');` now, but the PHP script seem to crash when loading the database class. – Jonas May 04 '11 at 16:43
1

What is the error message? Also, are you using Codeigniter 2.0? – csi May 04 '11 at 16:56
4

There was no error message, just HTTP 500 Internal Server Error. But I have solved it now. I upgraded from CodeIgniter 2.0.1 to 2.0.2 and now your trick works. Thanks! You are helpful! – Jonas May 04 '11 at 17:09
Great work @ChristopherIckes. This thread has given me the best insight as to integrating PDO with Codeigniter after a bit of research.. and looks to have helped Jonas. – Shane May 12 '13 at 17:48
Does ActiveRecord can't be use with PDO driver? – Robbi Nespu Sep 01 '16 at 07:01

How can I use PDO in CodeIgniter 2?

1 Answers1

Linked