serverside datatable

[frank1]

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 ?";
}

and this

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

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

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();

[tybalt89]

At first glance, it looks like you are generating the following SQL statement:

SELECT COUNT(*) FROM t_infosWHERE product LIKE ? OR descp LIKE ?

Do you really have a table called "t_infosWHERE" ?

[Corion]

Have you printed the complete SQL statement before you try to run it? Most likely the concatenation of your SQL goes wrong somewhere.

My suggestion is to copy the request that Datatables makes from the browser and run your script from the command line with the request parameters. Alternatively, re-run the request, and have your script call die with the SQL statement if you don't have access to your log files.

[frank1]

Thanks i figured it out