Check where Datatables

0

I have a fetch to show data to a Datatable and I want it to bring me information from MySQL filtered by an id -

The problem is that he keeps showing me all the information.

The query is assembled with:

'SELECT * FROM user WHERE id = 10'

and I concatenate this for each value I have:

'AND first_name LIKE "%' . $_POST["search"]["value"] . '%" 
 OR last_name LIKE "%' . $_POST["search"]["value"] . '%"'

What I finally try is to get a variable POST (but to test I'm putting a number with an id).

The fetch code:

<?php

$connect = mysqli_connect("localhost", "root", "root", "testing");
$columns = array(
    'first_name',
    'last_name'
);

$query = "SELECT * FROM user WHERE id = 10";

if (isset($_POST["search"]["value"])) {
    $query .= '
    AND first_name LIKE "%' . $_POST["search"]["value"] . '%" 
    OR last_name LIKE "%' . $_POST["search"]["value"] . '%" 
    ';
}

if (isset($_POST["order"])) {
    $query .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' 
     ' . $_POST['order']['0']['dir'] . ' 
     ';
} else {
    $query .= 'ORDER BY id DESC ';
}

$query1 = '';

if ($_POST["length"] != -1) {
    $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while ($row = mysqli_fetch_array($result)) {
    $sub_array   = array();
    $sub_array[] = '<div contenteditable class="update" data-
     id="' . $row["id"] . '" data-column="first_name">' . $row["first_name"] . '</div>';
    $sub_array[] = '<div contenteditable class="update" data-
     id="' . $row["id"] . '" data-column="last_name">' . $row["last_name"] . '</div>';
    $sub_array[] = '<button type="button" name="delete" class="btn btn-
     danger btn-xs delete" id="' . $row["id"] . '">Delete</button>';
    $data[]      = $sub_array;
}

function get_all_data($connect)
{
    $query  = "SELECT * FROM user";
    $result = mysqli_query($connect, $query);
    return mysqli_num_rows($result);
}

$output = array(
    "draw" => intval($_POST["draw"]),
    "recordsTotal" => get_all_data($connect),
    "recordsFiltered" => $number_filter_row,
    "data" => $data
);

echo json_encode($output);

?>
    
asked by Vacanito 19.02.2018 в 19:04
source

2 answers

0

Maybe the problem is in the WHERE arguments and the last_name LIKE "" is always running. Try adding parentheses.

$query .= ' AND ( first_name LIKE "%'.$_POST["search"]["value"].'%" OR last_name LIKE "%'.$_POST["search"]["value"].'%" ) ';

    
answered by 19.02.2018 / 19:17
source
0

I recommend you use a PHP library provided by them called ssp.php or as this link shows: link the library called "server_processing.php". With it you have all the POST parameters already contemplated and then "modify" to your liking the setting of SQL statements.

    
answered by 19.02.2018 в 20:24