Hi Guys i hope u fine , when i click on header cols for ordering showing this error

hassankz1

Exception -> string(76) "SQLSTATE[42S22]: Column not found: 1054 Unknown column '2' in 'order clause'"

this my js code .

tbl_petty.DataTable({
      "bProcessing": true,
      "serverSide": true,
      "order": [],
      "ajax": {
        url: "./php/fetchPettyCash.php", // json datasource
        type: "post", // type of method  ,GET/POST/DELETE
      }
    });

server side code

 try {
        $query = '';
        $output = array();
        $query .= "SELECT * FROM tbl_petty_cash ";
        $seachPagin = '';
        if (isset($_POST["search"]["value"])) {
            $searchQuery = $_POST["search"]["value"];
            $searchQuery = trim($searchQuery);
            $query .= ' WHERE pt_amount LIKE "%' . $searchQuery . '%" ';
            $query .= ' OR pt_date LIKE "%' . $searchQuery . '%" ';
            $query .= ' OR pt_note LIKE "%' . $searchQuery . '%" ';
            $query .= ' OR pt_id LIKE "%' . $searchQuery . '%" ';
      
        }
        if (isset($_POST["order"])) {
            $query .= ' ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' ';
        } else {
            $query .= ' ORDER BY pt_id DESC ';
        }
        $seachPagin .= $query;

        if ($_POST["length"] != -1) {
            $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
        }
        $statement = $pdo->prepare($query);
        $statement->execute();
        $result = $statement->fetchAll();
        $data = array();
        $filtered_rows = $statement->rowCount();
        foreach ($result as $row) {
            $done = '';
            $sub_array = array();
            $sub_array[] = $row["pt_id"];
            $sub_array[] = $row["pt_amount"];
            $sub_array[] = $row["pt_note"];
            $sub_array[] = $row['pt_date'];
            $data[] = $sub_array;
        }
        $s = str_replace("*", "", $seachPagin);
        $newQuery = substr_replace($s, " count(*) AS totalRec ", 8, 0);
        $stmt = $pdo->prepare($newQuery);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $output = array(
            "draw"                =>    intval($_POST["draw"]),
            "recordsTotal"        =>     $filtered_rows,
            "recordsFiltered"    =>    $row['totalRec'],
            "data"                =>    $data
        );
        echo json_encode($output);
    } catch (Exception $e) {
        echo 'Exception -> ';
        var_dump($e->getMessage());
    }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

rf1234

Please check your code formatting before posting. Thanks.

$query .= ' ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' ';

This looks like a problem. Parameters in ORDER BY need to be comma separated.