如何以Codeigniter的方式编写数据库查询以在日期字段中搜索月份？

发布于 2024-12-23 16:36:38 字数 779 浏览 2 评论 0原文

您能告诉我如何以 Codeigniter 的方式编写数据库查询以在日期字段中搜索月份吗？或者您能告诉我如何以 codeigniter 的方式编写以下查询吗？

提前致谢:)

 "SELECT attendanceno FROM attendance WHERE (barcodeid = '$studentid') AND  (month(attendancedate)='$month' AND year(attendancedate)='$year') ";

编辑

感谢 Stefan，他在下面回答了我的问题。阅读他的文章后，我还尝试了以下脚本并发现它有效。这就是为什么我只是想分享以防有人想使用它。但所有功劳都归功于斯特凡。 :)

  $this->db->select('*');
  $this->db->from('attendance');
  $this->db->where('barcodeid', $studentid);
  $this->db->where('month(attendancedate)', $month);
  $this->db->where('year(attendancedate) ', $year);
  $this->db->order_by('attendancedate ','ASC');     
  $getData = $this->db->get('', $perPage, $uri);

原文

Could you please tell me how to write database query to search month in date field in Codeigniter's way? Or could you please show me how to write the following query in codeigniter's way?

Thanks in Advance :)

 "SELECT attendanceno FROM attendance WHERE (barcodeid = '$studentid') AND  (month(attendancedate)='$month' AND year(attendancedate)='$year') ";

Edit

Thanks to Stefan, who answered my question below. After reading his post I also tried the following script and found it working. That's why I just wanted to share in case anyone wants to use it. But all credit goes to Stefan. :)

  $this->db->select('*');
  $this->db->from('attendance');
  $this->db->where('barcodeid', $studentid);
  $this->db->where('month(attendancedate)', $month);
  $this->db->where('year(attendancedate) ', $year);
  $this->db->order_by('attendancedate ','ASC');     
  $getData = $this->db->get('', $perPage, $uri);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

权谋诡计 2024-12-30 16:36:38

如果日期和月份是字符串：

$this->db->query("SELECT attendanceno FROM attendance WHERE (barcodeid = ?) AND  (month(attendancedate)=? AND year(attendancedate)=?)", array($studentid, $month,$year));

如果它们是整数

$this->db->query("SELECT attendanceno FROM attendance WHERE (barcodeid = ?) AND  (MONTH(attendancedate)='?' AND YEAR(attendancedate)='?')", array($studentid, $month,$year));

If date and month are strings:

$this->db->query("SELECT attendanceno FROM attendance WHERE (barcodeid = ?) AND  (month(attendancedate)=? AND year(attendancedate)=?)", array($studentid, $month,$year));

if they are integers

$this->db->query("SELECT attendanceno FROM attendance WHERE (barcodeid = ?) AND  (MONTH(attendancedate)='?' AND YEAR(attendancedate)='?')", array($studentid, $month,$year));

回复收藏 0 原文

孤独患者 2024-12-30 16:36:38

假设：

CodeIgniter 2.2.0

Oracle 11 数据库

//选项1，原始SQL
 $sql = "
  选择不同的
  来自<表名>
  其中 1=1
    AND EXTRACT(来自  的月份) = $month
    AND EXTRACT(year FROM ) = $year
 ”;
 $query = $this->db->query($sql);

//选项2，CI db函数
$this->db->distinct();
$this->db->select('');
$this->db->where('EXTRACT(month FROM ) =', $month);
$this->db->where('EXTRACT(year FROM ) =', $year);
$query = $this->db->get('<表名>');

都将在 $month = 3 和 $year = 2017 时生成以下内容：

     CI_DB_oci8_result Object
     (
         [stmt_id] => Resource id #9
         [curs_id] => 
         [limit_used] => 
         [conn_id] => Resource id #1
         [result_id] => 1
         [result_array] => Array
             (
                 [0] => Array
                     (
                         [<DATE_COLUMN>] => 22-MAR-17
                     )

                 [1] => Array
                     (
                         [<DATE_COLUMN>] => 14-MAR-17
                     )

                 [2] => Array
                     (
                         [<DATE_COLUMN>] => 02-MAR-17
                     )

                 [3] => Array
                     (
                         [<DATE_COLUMN>] => 06-MAR-17
                     )

             )

         [result_object] => Array
             (
             )

         [custom_result_object] => Array
             (
             )

         [current_row] => 0
         [num_rows] => 4
         [row_data] => 
     )

Assumptions:

CodeIgniter 2.2.0

Oracle 11 database

//Option 1, raw SQL
 $sql = "
  SELECT DISTINCT <DATE_COLUMN>
  FROM <TABLE NAME>
  WHERE 1=1
    AND EXTRACT(month FROM <DATE_COLUMN>) = $month
    AND EXTRACT(year FROM <DATE_COLUMN>) = $year
 ";
 $query = $this->db->query($sql);

//Option 2, CI db functions
$this->db->distinct();
$this->db->select('<DATE_COLUMN>');
$this->db->where('EXTRACT(month FROM <DATE_COLUMN>) =', $month);
$this->db->where('EXTRACT(year FROM <DATE_COLUMN>) =', $year);
$query = $this->db->get('<TABLE NAME>');

both will produce the following with $month = 3 and $year = 2017:

     CI_DB_oci8_result Object
     (
         [stmt_id] => Resource id #9
         [curs_id] => 
         [limit_used] => 
         [conn_id] => Resource id #1
         [result_id] => 1
         [result_array] => Array
             (
                 [0] => Array
                     (
                         [<DATE_COLUMN>] => 22-MAR-17
                     )

                 [1] => Array
                     (
                         [<DATE_COLUMN>] => 14-MAR-17
                     )

                 [2] => Array
                     (
                         [<DATE_COLUMN>] => 02-MAR-17
                     )

                 [3] => Array
                     (
                         [<DATE_COLUMN>] => 06-MAR-17
                     )

             )

         [result_object] => Array
             (
             )

         [custom_result_object] => Array
             (
             )

         [current_row] => 0
         [num_rows] => 4
         [row_data] => 
     )

回复收藏 0 原文

~没有更多了~