I have student ordered information data stored in DB. I want to search those orders in between by dropdown selected from-to date and time-wise. Normally, I can search all data from DB but could not work when I select the dropdown from-to date and time-wise options.
my files are in below :
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>
index.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');
};
index.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;
}
Can anyone please help me to get the solution of this?
Your SQL looks wrong. You are appending the WHERE clause after the GROUP BY. That's an SQL syntax error. Essentially, you are producing SQL like this:
SELECT * FROM foo GROUP BY id AND WHERE bar = 1;
This will throw an error similar to
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE bar = 1' at line 1
We don't see where your $dbh gets created, but I assume you don't have error checking on. Your $dbh is also not even available in GetOrderSearch. You take it out of $self in the other method where you construct the SQL and never use it, but not where you actually run the query, so your code is likely not to compile at all.
In any case, you should rearrange the creation of your SQL code and break it down into steps.
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';
This way the SQL will be in the right order.
In the future, when your SQL doesn't work, debug it into your server log and run it manually against your mysql server to see what's going on.