CodeIgniter MySQL Select Data

Once you have inserted data into the database, the next step is to retrieve (select) data and display it in your application.

In CodeIgniter, we can select data using:

1. Active Record (Query Builder) – secure & simple
2. Raw SQL Queries – manual way

🔹 Step 1: Load Database

Make sure your database is connected via autoload.php:

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

Or manually load inside your controller:

$this->load->database();

🔹 Step 2: Fetch All Records (Query Builder)

class SelectController extends CI_Controller {

    public function get_users() {
        $query = $this->db->get('users'); // SELECT * FROM users
        $result = $query->result();

        foreach ($result as $row) {
            echo "ID: " . $row->id . " | Name: " . $row->name . " | Email: " . $row->email . "<br>";
        }
    }
}

👉 URL:

http://your-site/index.php/selectcontroller/get_users

🔹 Step 3: Fetch Single Record (where condition)

public function get_user($id) {
    $query = $this->db->get_where('users', array('id' => $id)); // SELECT * FROM users WHERE id = ?
    $row = $query->row();

    if ($row) {
        echo "User Found: " . $row->name . " - " . $row->email;
    } else {
        echo "No user found!";
    }
}

🔹 Step 4: Custom Query with Conditions

public function active_users() {
    $this->db->select('id, name, email');
    $this->db->from('users');
    $this->db->where('status', 1);
    $this->db->order_by('id', 'DESC');
    $query = $this->db->get();

    foreach ($query->result() as $row) {
        echo $row->id . " - " . $row->name . " - " . $row->email . "<br>";
    }
}

🔹 Step 5: Limit & Offset

public function limit_users() {
    $query = $this->db->get('users', 5, 0); // SELECT * FROM users LIMIT 5 OFFSET 0
    return $query->result();
}

🔹 Step 6: Raw SQL Query

public function raw_query() {
    $sql = "SELECT * FROM users WHERE email LIKE ?";
    $query = $this->db->query($sql, array('%@example.com%'));

    foreach ($query->result() as $row) {
        echo $row->name . " - " . $row->email . "<br>";
    }
}

🔹 Step 7: Fetch Data as Array

public function get_array() {
    $query = $this->db->get('users');
    $result = $query->result_array();

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