求助：基于当前日期获取数据库数据失败（含表结构及MVC配置）

Hey there! Let's figure out why you can't fetch today's data from your cash_transactions table. Based on your setup (Admin controller, Admin_Model, and the table structures you shared), I'll walk you through common fixes and checks:

The most likely issue is an incorrect date comparison in your Admin_Model method. The approach depends on what type your dated_on field is (DATE vs DATETIME/TIMESTAMP):

If dated_on is a DATE field (format: YYYY-MM-DD) #

Use this method in your Admin_Model to safely fetch today's records:

public function get_today_transactions() {
    // Option 1: Use CodeIgniter's query builder with PHP date
    $this->db->where('dated_on', date('Y-m-d'));
    
    // Option 2: Use database's native CURDATE() function (avoids PHP timezone issues)
    // $this->db->where('dated_on', 'CURDATE()', FALSE);
    
    return $this->db->get('cash_transactions')->result();
}

If dated_on is a DATETIME/TIMESTAMP field (format: YYYY-MM-DD HH:MM:SS) #

Comparing the full datetime range is more efficient (and index-friendly) than extracting the date part:

public function get_today_transactions() {
    $today_start = date('Y-m-d 00:00:00');
    $today_end = date('Y-m-d 23:59:59');
    
    $this->db->where('dated_on >=', $today_start);
    $this->db->where('dated_on <=', $today_end);
    
    return $this->db->get('cash_transactions')->result();
}

Make sure your Admin controller is loading the model correctly and calling the method properly. Here's a sample implementation:

public function view_today_transactions() {
    // Load the Admin_Model first
    $this->load->model('Admin_Model');
    
    // Fetch today's transactions
    $data['today_records'] = $this->Admin_Model->get_today_transactions();
    
    // Debug tip: Print the last executed query to check for issues
    // echo $this->db->last_query(); exit;
    
    // Pass data to your view
    $this->load->view('admin/today_transactions', $data);
}

• Typos in Field Names: Double-check that your field is exactly dated_on (no typos like date_on or dated_onn).
• Timezone Mismatch: If your server's timezone doesn't match your expected date, date('Y-m-d') or CURDATE() will return the wrong day. Fix this in CodeIgniter's application/config/config.php:

  $config['timezone'] = 'Asia/Kolkata'; // Replace with your correct timezone
  

• No Data for Today: Run a raw SQL query directly in your database to confirm if there are actually records for today:

  -- For DATE fields
  SELECT * FROM cash_transactions WHERE dated_on = CURDATE();
  
  -- For DATETIME fields
  SELECT * FROM cash_transactions WHERE dated_on >= CONCAT(CURDATE(), ' 00:00:00') AND dated_on <= CONCAT(CURDATE(), ' 23:59:59');
  

• Database Permissions: Ensure your database user has SELECT access to the cash_transactions table.

内容的提问来源于stack exchange，提问作者Manjusha K Vijayan