php—如何使用where子句获取范围之间的值

lh80um4z 于 2021-06-19 发布在 Mysql

关注(0)|答案(4)|浏览(266)

数据库结构

我正在尝试在控制器（codeigniter）中进行原始查询，并尝试从用户在newdate和newdate2上提供的范围中获取值。我的问题是它不显示任何内容。如何为此编写查询。我只想根据voucheno（vno）显示qty、productname和bundle。这是我尝试过的代码
控制器代码：

$startdate         = $this->input->post('SDate');
$enddate           = $this->input->post('EDate');
$date              = str_replace('/', '-', $startdate);
$newDate           = date("Y-m-d", strtotime($date));
$date2             = str_replace('/', '-', $enddate);
$newDate2          = date("Y-m-d", strtotime($date2));
$data['startdate'] = $startdate;
$data['enddate']   = $enddate;
$query             = $this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate >= "$newDate"AND billdate <= "$newDate2" ORDER by `vno`')->result_array();
$data['query']     = $query;

视图代码：

<?php foreach ($query as $row): ?>
                                            <tr><td></td><td></td><td></td><td></td>
                                        <td><?=$row['Prdtname'];?></td>
                                        <td><?=$row['Qty'];?></td>
                                        <td><?=$row['bundle'];?></td>

                                        <?php endforeach ?>

mysql php select codeigniter

来源：https://stackoverflow.com/questions/53387737/how-to-get-the-values-in-between-the-range-by-using-where-clause

4条答案

按热度按时间

7uzetpgm1#

你可以用between

SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate BETWEEN "$newDate" AND "$newDate2" ORDER by `vno`

赞(0）回复(0）举报 2021-06-19

7kjnsjlb2#

将查询更改为下面的查询和检查。
你必须用单引号或双引号来传递日期。

$query = $this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate >= "'.$newDate.'" AND billdate <= "'.$newDate2.'" ORDER by `vno`')->result_array();

如果还有问题，请告诉我。

赞(0）回复(0）举报 2021-06-19

whitzsjs3#

如果只存储日期，我将尝试使用unix时间戳。

$snewsDate = strtotimr($startdate);
$newDate2 = strtotime($enddate);

SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate BETWEEN 
UNIX_TIMESTAMP($newDate) AND UNIX_TIMESTAMP($newDate2) ORDER by `vno

赞(0）回复(0）举报 2021-06-19

mrfwxfqh4#

试试这个：

$startdate         = $this->input->post('SDate');
$enddate           = $this->input->post('EDate');
$newDate           = date_format(date_create($startdate),"Y-m-d");
$newDate2          = date_format(date_create($enddate),"Y-m-d");
$data['startdate'] = $startdate;
$data['enddate']   = $enddate;
$query             = $this->db->query("SELECT vno,Prdtname,Qty,bundle FROM purchaseitem WHERE billdate BETWEEN '$newDate' AND '$newDate2' ORDER by vno")->result_array();
$data['query']     = $query;

赞(0）回复(0）举报 2021-06-19

我来回答

php—如何使用where子句获取范围之间的值

4条答案

相关问题

热门标签

最新问答