CodeIgniter中SELECT语法使用及重复milk记录数量累加展示方法

Hey there! Let's walk through how to craft SELECT statements in CodeIgniter and tackle that problem where you need to aggregate duplicate milk records by summing their quantities.

CodeIgniter gives you two main approaches for running SELECT queries: using its Query Builder (the recommended, secure way) or executing raw SQL. Let's cover both.

Using Query Builder (Active Record) #

This method abstracts SQL syntax, handles database compatibility, and helps prevent SQL injection automatically. Here are common use cases:

Basic SELECT #

Fetch specific columns from a table:

// Select id and name from the "products" table
$this->db->select('id, name');
$this->db->from('products');
$query = $this->db->get();

// Get results as an array of objects
$results = $query->result();
// Or as an associative array
$results = $query->result_array();

SELECT with Conditions #

Add filters, sorting, and limits:

$this->db->select('id, name, price');
$this->db->from('products');
$this->db->where('price >', 10); // Filter items over $10
$this->db->order_by('name', 'ASC'); // Sort by name ascending
$this->db->limit(20); // Fetch only 20 records
$query = $this->db->get();
$filtered_results = $query->result_array();

Using Raw SQL #

If you need more control or complex queries, use raw SQL with parameter binding to stay secure:

// Raw query with parameter binding (prevents injection)
$sql = "SELECT id, name, price FROM products WHERE price > ? ORDER BY name ASC LIMIT 20";
$query = $this->db->query($sql, [10]); // Pass parameters as an array
$results = $query->result_array();

Let's say you have a table milk_records with columns milk_name (e.g., "Whole Milk", "Skim Milk") and quantity (the amount of each entry). To combine duplicates and sum their quantities, you'll use GROUP BY and the SUM() aggregate function.

With Query Builder #

$this->db->select('milk_name, SUM(quantity) AS total_quantity');
$this->db->from('milk_records');
$this->db->group_by('milk_name'); // Group rows by milk name
$query = $this->db->get();

// Result will look like:
// [
//   ['milk_name' => 'Whole Milk', 'total_quantity' => 150],
//   ['milk_name' => 'Skim Milk', 'total_quantity' => 80]
// ]
$aggregated_milk = $query->result_array();

Add Filters or Post-Grouping Conditions #

• Filter before grouping (e.g., only records from 2024):

  $this->db->select('milk_name, SUM(quantity) AS total_quantity');
  $this->db->from('milk_records');
  $this->db->where('created_at >=', '2024-01-01');
  $this->db->group_by('milk_name');
  $query = $this->db->get();
  

• Filter after grouping (e.g., only totals over 100):

  $this->db->select('milk_name, SUM(quantity) AS total_quantity');
  $this->db->from('milk_records');
  $this->db->group_by('milk_name');
  $this->db->having('total_quantity >', 100); // Use HAVING for grouped results
  $query = $this->db->get();
  

With Raw SQL #

// Basic aggregation
$sql = "SELECT milk_name, SUM(quantity) AS total_quantity FROM milk_records GROUP BY milk_name";
$query = $this->db->query($sql);
$aggregated_milk = $query->result_array();

// With filters and having clause
$sql = "SELECT milk_name, SUM(quantity) AS total_quantity 
        FROM milk_records 
        WHERE created_at >= ? 
        GROUP BY milk_name 
        HAVING total_quantity > ?";
$query = $this->db->query($sql, ['2024-01-01', 100]);
$filtered_aggregates = $query->result_array();

Key Notes #

• Always use parameter binding (the ? placeholders) when inserting variables into raw SQL to avoid SQL injection.
• GROUP BY groups rows with identical values in the specified column(s), and SUM() calculates the total of the quantity column for each group.
• Using AS total_quantity gives the summed value a readable alias to reference in your code.

内容的提问来源于stack exchange，提问作者Yaden Mustopa