the features is not working

novice1222

issues:
1. The sorting is not properly working when I click the sort button on the second column. The first column gets sorted.
2. The search is like when I type something, it's just like moving the tables, and sometimes it disappears.
3. The pagination is not working. I am showing 1 to 10 of 10 entries (filtered from 11 total entries), but I cannot go to the next page.

but the data is showing in the tables, and I believe it has something to do with the format from the serverside or the parameters.

post.js

$(document).ready(function(){
    var postsData = $('#postsList').DataTable({
        "lengthChange": true, // enable length change
        "lengthMenu": [10, 25, 50, -1], // set length menu options
        "processing":true,
        "serverSide":true,
        "order":[],
        "searching": false, 
        "paging": true,
        "ordering":false,
        "ajax":{
            url:"manage_posts.php",
            type:"POST",
            data:{action:'postListing'},
            dataType:"json"
        },
        "columnDefs":[
            {
                "targets":[0, 6, 7],
                "orderable":false,
            },
        ],
        "pageLength": 10
    });     
    $(document).on('click', '.delete', function(){
        var postId = $(this).attr("id");        
        var action = "postDelete";
        if(confirm("Are you sure you want to delete this post?")) {
            $.ajax({
                url:"manage_posts.php",
                method:"POST",
                data:{postId:postId, action:action},
                success:function(data) {                    
                    postsData.ajax.reload();
                }
            })
        } else {
            return false;
        }
    }); 
});

class/post.php

```php
public function getPostsListing()
{
$whereQuery = '';

    // Check if the user is an admin (user_type = 1) or not
    if ($_SESSION['user_type'] == 1) {
        // Admin can see all posts
        $whereQuery = '';
    } elseif ($_SESSION['user_type'] == 2) {
        // User type 2 can only see their own posts
        $whereQuery = " WHERE p.userid = '" . $_SESSION['userid'] . "'";
    }

    $sqlQuery = "
        SELECT p.id, p.title, p.category_id, u.first_name, u.last_name, p.status, p.created, p.updated, c.name 
        FROM " . $this->postTable . " p
        LEFT JOIN " . $this->categoryTable . " c ON c.id = p.category_id
        LEFT JOIN " . $this->userTable . " u ON u.id = p.userid
        $whereQuery";

    if (!empty($_POST["search"]["value"])) {
        $sqlQuery .= ' AND (title LIKE "%' . $_POST["search"]["value"] . '%" ';
        $sqlQuery .= ' OR message LIKE "%' . $_POST["search"]["value"] . '%" ';
        $sqlQuery .= ' OR created LIKE "%' . $_POST["search"]["value"] . '%" ';
        $sqlQuery .= ' OR updated LIKE "%' . $_POST["search"]["value"] . '%") ';
    }

    if (!empty($_POST["order"])) {
        $sqlQuery .= 'ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' ';
    } else {
        $sqlQuery .= 'ORDER BY id DESC ';
    }

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

    $stmt = $this->conn->prepare($sqlQuery);
    $stmt->execute();
    $result = $stmt->get_result();

    $stmtTotal = $this->conn->prepare("SELECT * FROM " . $this->postTable);
    $stmtTotal->execute();
    $allResult = $stmtTotal->get_result();
    $allRecords = $allResult->num_rows;

    $displayRecords = $result->num_rows;
    $posts = array();

    while ($post = $result->fetch_assoc()) {
        $status = '';
        if ($post['status'] == 'published') {
            $status = '<span class="label label-success">Published</span>';
        } else if ($post['status'] == 'draft') {
            $status = '<span class="label label-warning">Draft</span>';
        } else if ($post['status'] == 'archived') {
            $status = '<span class="label label-danger">Archived</span>';
        }

        $rows = array(
            ucfirst($post['title']),
            $post['name'],
            ucfirst($post['first_name']) . " " . $post['last_name'],
            $status,
            $post['created'],
            $post['updated'],
            '<a href="editpost.php?id=' . $post["id"] . '" class="btn btn-warning btn-xs update">Edit</a>',
            '<button type="button" name="delete" id="' . $post["id"] . '" class="btn btn-danger btn-xs delete">Delete</button>'
        );

        $posts[] = $rows;
    }

    $output = array(
        "draw" => intval($_POST["draw"]),
        "iTotalRecords" => $allRecords,
        "iTotalDisplayRecords" => $displayRecords,
        "data" => $posts
    );

    echo json_encode($output);
}

``
manage_post.php

```php
<?php
include_once 'config/Database.php';
include_once 'class/Post.php';
$database = new Database();
$db = $database->getConnection();

$post = new Post($db);

if(!empty($_POST['action']) && $_POST['action'] == 'postListing') {
$post->getPostsListing();
}
if(!empty($_POST['action']) && $_POST['action'] == 'postDelete') {
$post->id = (isset($_POST['postId']) && $_POST['postId']) ? $_POST['postId'] : '0';
$post->delete();
}

<?php > ``` ```

Title	Section	User	Status	Created	Updated

``` ?>

Database Information:
Database Name: archiving_system
Tables:
Table: acc_user

Fields:
id (Primary Key)
first_name
last_name
email
password
type
deleted
Engine: InnoDB
Default Charset: Latin1
Collation: latin1_swedish_ci
This table seems to store user information, including names, email, password, user type, and a flag for deleted users.

Table: posts_archive

Fields:
id (Primary Key)
title
message
capstonemembers
capstone_advisor
capstone_mentor
panel_member
copyright
pdf_name
category_id
userid
status
created
updated
Engine: InnoDB
Default Charset: Latin1
Collation: latin1_swedish_ci
This table appears to store information about archived posts. Each post has a title, message, contributors, advisor, mentor, panel members, copyright information, PDF file name, category ID, user ID, status (published, draft, archived), and timestamps for creation and last update.

Table: tbl_year_and_section

Fields:
id (Primary Key)
name
Engine: InnoDB
Default Charset: Latin1
Collation: latin1_swedish_ci
This table seems to store information about academic years and sections, with a unique identifier and a name.

allan

You have server-side processing enabled, so if sorting / filtering / paging isn't working, there is something wrong with your server-side code. I would suggest you start debugging it by dumping out the SQL that it is generating for each query, so you can check if it is doing what you expect.

Allan

novice1222

response from a server-side script:

{
  "draw": 1,
  "iTotalRecords": 5,
  "iTotalDisplayRecords": 5,
  "data": [
    [
      "ZPPSU CICS Research/ Capstone Archiving Management System",
      "BSIT 4A",
      "Archive administrator",
      "<span class=\"label label-success\">Published</span>",
      "2024-01-07 15:00:19",
      "2024-01-10 14:03:06",
      "<a href=\"editpost.php?id=164\" class=\"btn btn-warning btn-xs update\">Edit</a>",
      "<button type=\"button\" name=\"delete\" id=\"164\" class=\"btn btn-danger btn-xs delete\">Delete</button>"
    ],
    // ... (similar arrays for other posts)
  ]
}

allan

Use recordsTotal and recordsFiltered rather than iTotalRecords and iTotalDisplayRecords. See the documentation here.

What is the response when you click on a column to sort it? Did you try dumping out the generated SQL as I suggested?

Allan

novice1222

sorry i dont know how to do the dumping og the sql im just new but i try to add
echo "Generated SQL: " . $sqlQuery; but when i restart the page its show this
DataTables warning: table id=postsList - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 and i change the recordsTotal and recordsFiltered, like you said

novice1222

i just discover something.
when im a type 1 user i cant search but when im type 2 i can search fine, now i copy the $whereQuery = " WHERE p.userid = '" . $_SESSION['userid'] . "'"; of type 2 to type 1. now the i can search when im a type 1 user but to problem is i cant now see all the post, i want when im a type 1 user i can see all the post of type1 and type2. the search is not properly working when im a type 1 user but when im type 2 it work fine

the code :
``` $whereQuery = '';

    // Check if the user is an admin (user_type = 1) or not
    if ($_SESSION['user_type'] == 1) {
        // Admin can see all posts
        $whereQuery = '';
    } elseif ($_SESSION['user_type'] == 2) {
        // User type 2 can only see their own posts
        $whereQuery = " WHERE p.userid = '" . $_SESSION['userid'] . "'";
    }

i copy the type 2 $wherequary to type 1 

$whereQuery = '';

    // Check if the user is an admin (user_type = 1) or not
    if ($_SESSION['user_type'] == 1) {
        // Admin can see all posts
        $whereQuery = " WHERE p.userid = '" . $_SESSION['userid'] . "'";
    } elseif ($_SESSION['user_type'] == 2) {
        // User type 2 can only see their own posts
        $whereQuery = " WHERE p.userid = '" . $_SESSION['userid'] . "'";
    }

```

allan

Invalid JSON response

Did you look at the Ajax response, per the tech note in the link from the error message? It would contain the SQL statement that you are echoing out.

Your other option is to dump the query into a file using file_put_contents() or similar.

Allan