I really need some help on my script, am trying to create sever-side back-end script for datatable and its working, am getting the data, seems working for me very well, but the only problem i have is search (filter results)

actually this part in my script is the one disturbing me

if ($search_value) {
    $sql .= "WHERE product LIKE ? OR descp LIKE ?";
    $count_sql .= "WHERE product LIKE ? OR descp LIKE ?";
}

[download]

and this 

# Add LIMIT for pagination 
# $sql .= " LIMIT ?, ?"; # i disabled it by comment. because was getti
+ng alot errors with it

[download]

this is the error i get 

file.pl: DBD::mysql::st execute failed: You have an error in your SQL 
+syntax; check the manual that corresponds to your MariaDB server vers
+ion for the right syntax to use near 'LIKE '%tam%' OR product LIKE '%
+tam%'' at line 1 at file.pl

[download]

i really need help if someone can look into my script for errors and advice. because i want to hold 1million record and access them via datatable server-side

#!/usr/bin/perl

use strict;
use warnings;
use CGI;
use JSON;
use DBI;

my $q = CGI->new;
print $q->header('content-type: application/json; charset=UTF-8');

my $dsn = "DBI:mysql:database_name:host=localhost";
my $db_user = "user";
my $db_pass = "password";
my $dbh = DBI->connect($dsn, $db_user, $db_pass, { RaiseError => 1, Au
+toCommit => 1 });

my $draw = $q->param('draw') || 1;
my $start = $q->param('start') || 0;
my $length = $q->param('length') || 5;
my $search_value = $q->param('search[value]') || '';

my $total_info = $dbh->prepare("SELECT count(*) FROM t_infos WHERE sta
+tus = ?");
$total_info->execute('bought');
my $infos = $total_info->fetchrow_array(); 
$total_info->finish;

my $count_sql = "SELECT COUNT(*) FROM t_infos";

my $sql = '';

if ($infos >= 1) {

 $sql = "SELECT 
  snd.avatar, snd.fullname, m.product, m.descp, m.price, m.tag, m.seal
+ FROM
  t_infos as m
  JOIN users as snd ON snd.accountnumber = m.accountnum WHERE CASE WHE
+N (SELECT SUM(status = ?) FROM  t_infos) > 0 THEN m.status = ? ELSE m
+.status = ?  OR m.status = ? OR m.status = ? END
  ORDER BY created ASC";
}

if ($infos <= 0) {

  $sql = "SELECT 
  snd.avatar, snd.fullname, m.product, m.descp, m.price, m.tag, m.seal
+ FROM
  t_infos as m
  JOIN users as snd ON snd.accountid = m.accountnum WHERE CASE WHEN (S
+ELECT SUM(status = ?) FROM  t_infos) > 0 THEN m.status = ? ELSE m.sta
+tus = ?  OR m.status = ? OR m.status = ? END
  ORDER BY created ASC LIMIT 100";
}


if ($search_value) {
    $sql .= "WHERE product LIKE ? OR descp LIKE ?";
    $count_sql .= "WHERE product LIKE ? OR descp LIKE ?";
}

# Add LIMIT for pagination but not working if i remove comment
# $sql .= " LIMIT ?, ?";

my $count_sth = $dbh->prepare($count_sql);
if ($search_value) {
    my $search_term = "%$search_value%";
    $count_sth->execute($search_term, $search_term);
} else {
    $count_sth->execute();
}
my ($recordsTotal) = $count_sth->fetchrow_array;
my $recordsFiltered = $recordsTotal; 

my $data_sth = $dbh->prepare($sql);
if ($search_value) {
     my $search_term = "%$search_value%";
     $data_sth->execute($search_term, $search_term, 'bought', 'bought'
+, 'available', 'outstock', 'sold');
} else {
     $data_sth->execute('bought', 'bought', 'available', 'outstock', '
+sold');
}

my @rows;
while (my $row_hash = $data_sth->fetchrow_hashref) {
    push @rows, {
        avatar         => $row_hash->{'avatar'},
        fullname       => $row_hash->{'fullname'},
        product   => $row_hash->{'product'},
        descp     => $row_hash->{'descp'},
        price => $row_hash->{'price'},
        tag     => $row_hash->{'tag'},
        seal     => $row_hash->{'seal'},
    };
}


my %output = (
    "draw" => int($draw),
    "recordsTotal" => int($recordsTotal),
    "recordsFiltered" => int($recordsFiltered),
    "data" => \@rows,
);


my $jsonOutput = encode_json \%output;
print $jsonOutput;


$dbh->disconnect();

[download]

In reply to serverside datatable by frank1

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.