CodeIgniter MySQL Connection

To interact with databases in CodeIgniter, we need to configure a MySQL connection.

CodeIgniter provides a simple and secure way to connect and run queries using the Database Library.

👉 Database connection settings are managed inside:

application/config/database.php

🔹 Step 1: Open Database Configuration File

Go to:

application/config/database.php

You will see an array named $db['default'] which holds database settings.

🔹 Step 2: Configure Database Settings

Example configuration for MySQL:

$db['default'] = array(
    'dsn'      => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'ci_tutorial',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
);

Explanation of Important Parameters:

• hostname → Database server (usually localhost)
• username → Database username (root by default in XAMPP/WAMP)
• password → Password for MySQL user
• database → Name of your database (e.g., ci_tutorial)
• dbdriver → Driver type (mysqli for MySQLi)
• pconnect → Persistent connection (FALSE recommended)
• db_debug → Shows database errors (ON in dev, OFF in production)

🔹 Step 3: Autoload the Database Library

Instead of loading DB manually each time, you can autoload it.

Open:

application/config/autoload.php

Add database inside libraries:

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

Now database will load automatically.

🔹 Step 4: Test Database Connection

Create a controller and fetch data to confirm connection.

Example: Welcome.php

class Welcome extends CI_Controller {
    public function index() {
        // Fetch all users
        $query = $this->db->get('users'); // SELECT * FROM users
        $result = $query->result();

        echo "<pre>";
        print_r($result);
        echo "</pre>";
    }
}

👉 If your DB connection is correct, it will show the records.

🔹 Step 5: Run Queries

CodeIgniter provides two main ways to query MySQL:

1. Query Builder (Recommended) 

$this->db->select('name, email');
$this->db->from('users');
$query = $this->db->get();
$result = $query->result();

2. Manual Query

$query = $this->db->query("SELECT * FROM users WHERE status = 1");
$result = $query->result();

🔹 Best Practices

• Keep database passwords safe (don’t upload to GitHub).
• Use Query Builder for cleaner and secure queries.
• Use separate DB settings for development, testing, production.
• Always validate and escape inputs when working with queries.