mysql - 如何从 mysql DB 中搜索与 mojolicious perl 中选定的下拉列表相关的内容
问题描述
我有学生订购的信息数据存储在数据库中。我想通过从日期和时间选择的下拉菜单来搜索这些订单。通常,我可以搜索数据库中的所有数据,但当我选择下拉列表从日期和时间选项时无法工作。
我的文件在下面:
SrchList.html.ep
<%
my $SrchResult = stash('SrchResult');
my $Date = stash('Date');
my @Date =(['--' => '']);
foreach my $key (sort keys %{$Date}){
push(@Date, ($Date->{$key}->{name}) => $key);
}
my $Time = stash('Time');
my @Time =(['--' => '']);
for(my $i = 1; $i <= $Time->{max}; $i++){
push(@Time, [$i.'H' => $i]);
}
%>
<form action="<%= url_for('/SrchList') %>" method="post" class="form-horizontal frmSrchList">
<div class="row">
<div class="col-lg-10 col-lg-offset-1">
<div class="input-group">Order Start<%= select_field 'Date'=>\@Date, class=>'form-control', id=>'Date_start' %></div>
<div class="input-group">Time Start<%= select_field 'Time'=>\@Time, class=>'form-control', id=>'Time_start' %></div>
<div> ~ </div>
<div class="input-group">Order End<%= select_field 'Date'=>\@Date, class=>'form-control', id=>'Date_end' %></div>
<div class="input-group">Time End<%= select_field 'Time'=>\@Time, class=>'form-control', id=>'Time_end' %></div>
</div>
<div class="col-lg-10 col-lg-offset-1">
<button type="submit" class="btn btn-success">Search</button>
</div>
</div>
<div class="row"><table class="table table-striped">
<thead><tr><th>recptid</th><th>student_id</th><th>price</th><th>total</th><th>upd_date</th></tr></thead>
<tbody >
% foreach my $reserve_id (sort {$b <=> $a} keys %{$SrchResult}){
% $SrchResult->{$reserve_id}->{upd_date} =~ /(\d{4})\-(\d{2})\-(\d{2}) (\d{2}):(\d{2}):(\d{2})/;
% $SrchResult->{$reserve_id}->{price} =~ s/\,/<br>/g;
<tr>
<td><%= uc($SrchResult->{$reserve_id}->{recptid}) %></td>
<td><%= uc($SrchResult->{$reserve_id}->{student_id}) %></td>
<td align="right"> <%== decode('utf-8', $SrchResult->{$reserve_id}->{price}) %> </td>
<td align="center"> <%== decode('utf-8', $SrchResult->{$reserve_id}->{total}) %></td>
<td> <%= decode('utf-8', $SrchResult->{$reserve_id}->{upd_date}) %> </td>
</tr>% }
</tbody>
</table></div>
</form>
索引.cgi
get '/SrchList'=>{page => undef} => sub {
my $self = shift;
my @breadcrumb = ('SrchList');
$self->stash('breadcrumb'=> \@breadcrumb);
my $Date = $TextBookCommon->GetDate();
$self->stash('Date' => $Date);
$self->stash('Time' => $TextBookCommon->{'CONFIG'}->{'SELECT'}->{'Time'});
$session->clear('_SRCH_DATA');
$self->render('SrchList');
};
post '/SrchList/:page' => {page => undef} => sub {
my $self = shift;
my @breadcrumb = ('SrchList');
$self->stash('breadcrumb'=> \@breadcrumb);
my %SRCH_DATA = (defined $session->param('_SRCH_DATA') ? %{$session->param('_SRCH_DATA')} : '');
my $Date;
my $Time;
if(! defined $self->param('return')){
$Date = ($self->param('Date') ne '' ? $self->param('Date') : $Date);
$Time = ($self->param('Time') ne '' ? $self->param('Time') : $Time);
} else {
$Date = (exists($SRCH_DATA{'Date'}) ? $SRCH_DATA{'Date'} : '');
$Time = (exists($SRCH_DATA{'Time'}) ? $SRCH_DATA{'Time'} : '');
}
my $SrchData = {
'Date' => $Date,
'Time' => $Time
} ;
my $DateData = $TextBookCommon->GetDate();
$session->param(-name=>'_SRCH_DATA', -value=>$SrchData);
my $SrchResult = $TextBookCommon->GetOrderSearch($SrchData);
$self->stash('Date' => $DateData);
$self->stash('Time' => $TextBookCommon->{'CONFIG'}->{'SELECT'}->{'Time'});
$self->stash('SrchResult'=> $SrchResult);
$self->stash('SrchData'=> $SrchData);
$self->stash('TextBookCommon' => $TextBookCommon);
$self->render('SrchList');
};
索引.cgi.conf
{
'SELECT' => {
'Time' =>{
'max' => 24
},
},
}
TextBookCommon.pm
sub CreateOrderSearchSQL(){
my $self = shift;
my $StudentData = shift;
my $Date_start = $StudentData->{Date_start};
my $Time_start = $StudentData->{Time_start};
my $Date_end = $StudentData->{Date_end};
my $Time_end = $StudentData->{Time_end};
my $sql;
my $dbh = $self->{'dbh'};
my $data = '';
my @param = ();
$sql =<<" SQL_EOL";
SELECT tr.reserve_id, tr.recptid, tr.student_id, GROUP_CONCAT(tr.price) AS 'price',
SUM(tr.price)+1000 AS 'total', tr.upd_date AS 'upd_date'
FROM
( SELECT ra.reserve_id, RIGHT(ra.reserve_id,5) AS recptid,
m.student_id, rb.price, rb.upd_date FROM t_reserve_a AS ra
INNER JOIN t_reserve_b AS rb ON ra.reserve_id=rb.reserve_id
INNER JOIN m_student AS m ON m.student_id=ra.student_id ) AS tr
GROUP BY tr.reserve_id
SQL_EOL
if($Date_start ne '' || $Date_end ne ''){
$sql .="and WHERE DATE(t_reserve_a.upd_date) BETWEEN DATE(t_reserve_a.upd_date) = ? AND DATE(t_reserve_a.upd_date) =?";
push(@param, $Date_start, $Date_end);
}
if($Time_start ne '' || $Time_end ne ''){
$sql .="and WHERE TIME(t_reserve_a.upd_date) BETWEEN TIME(t_reserve_a.upd_date) = ? AND TIME(t_reserve_a.upd_date) = ? ";
push(@param, $Date_start, $Date_end);
}
my $result = {
'sql' => $sql,
'param' => \@param,
};
return $result;
}
sub GetOrderSearch(){
my $self = shift;
my $StudentData = shift;
my $result = $self->CreateOrderSearchSQL($StudentData);
my $sql = $result->{'sql'};
my @param = @{$result->{'param'}};
$sql .= " order by tr.reserve_id ";
my $SrchResult = $dbh->selectall_hashref($sql,'reserve_id',undef, @param);
return $SrchResult;
}
谁能帮我解决这个问题?
解决方案
你的 SQL 看起来不对。您WHERE
在GROUP BY
. 这是一个 SQL 语法错误。本质上,您正在生成这样的 SQL:
SELECT * FROM foo GROUP BY id AND WHERE bar = 1;
这将引发类似于
SQL 错误 (1064):您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以在第 1 行的“WHERE bar = 1”附近使用正确的语法
我们看不到您$dbh
的创建位置,但我假设您没有错误检查。您$dbh
甚至在GetOrderSearch
. 您在构建 SQL 并且从不使用它的另一种方法中取出$self
它,但不是在您实际运行查询的地方,因此您的代码可能根本无法编译。
在任何情况下,您都应该重新安排 SQL 代码的创建并将其分解为多个步骤。
my $sql = 'SELECT * FROM foo';
my @where, @args;
if ($some_condition) {
push @where, 'foo BETWEEN ? AND ?';
push @args, $datetime_from, $datetime_to;
}
if ($another_condition) {
push @where, 'id > ?';
push @args, $id;
}
if (@where) {
$sql .= ' WHERE ' . join( ' AND ', @where);
}
$sql .= ' GROUP BY id';
这样,SQL 将按正确的顺序排列。
将来,当您的 SQL 不工作时,将其调试到您的服务器日志中并针对您的 mysql 服务器手动运行它以查看发生了什么。
推荐阅读
- clojure - 是否可以定义一个宏:在函数上调度其他任何东西?
- python - aiohttp中的自定义访问日志
- scala - 错误:未找到:值 SparkSession
- maven - JHipster Spring Boot 模块化拆分包
- haskell - Haskell 列出错误
- javascript - 绑定数组中不同元素的值 (JavaScript)
- r - Y 和 M 模型中样本量不均匀的 R 多级中介
- sql - 如何为我的 Reporting Services 目录中的所有报表添加内容管理员权限?
- java - 使用循环访问 Freemarker 根对象
- dart - 如何使用 Flutter 创建考试准备?