JOIN queries in DataTable using AJAX throwing Unknown column error

I'm trying to attach products count to the existing categories datatable. Below is the php code for fetching data using ajax.

I tried name LIKE :name OR description LIKE :description and I'm getting error as below.

>Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

Then I tried by adding table name as prefix for each field, like
cat.name LIKE :name OR cat.description LIKE :description

This in turn is throwing the error as below.

>Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat.name' in 'where clause'

This is happening only while searching. Listing and sorting by field are working without any issue.

The below query in phpmyadmin works without any issue but I don't know, am I missing any quote or something??

SELECT cat.*, 
       COUNT(prod.cat_id) AS prod_count
FROM categories cat 
JOIN products prod ON cat.id = prod.cat_id
WHERE cat.name LIKE 'f%' OR cat.description LIKE 'f%'
GROUP BY cat.id, cat.name;

Attaching the whole php code below.

<?php 

    $draw = $_POST['draw'];
    $row = $_POST['start'];
    $rowperpage = $_POST['length']; // Rows display per page
    $columnIndex = $_POST['order'][0]['column']; // Column index
    $columnName = $_POST['columns'][$columnIndex]['data']; // Column name
    $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
    $searchValue = $_POST['search']['value']; // Search value

    $searchArray = array();

    $searchQuery = " ";
    if($searchValue != ''){
        $searchQuery = " AND (cat.name LIKE :name OR 
        cat.description LIKE :description
            ) ";
        $searchArray = array( 
            'name'=>"%$searchValue%",
            'description'=>"%$searchValue%",
            'status'=>"%$searchValue%"            
        );
    }
    
    $conn = $pdo->open();

    $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM categories");
    $stmt->execute();
    $records = $stmt->fetch();
    $totalRecords = $records['allcount'];

    $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM categories WHERE 1 ".$searchQuery);
    $stmt->execute($searchArray);
    $records = $stmt->fetch();
    $totalRecordwithFilter = $records['allcount'];

    $stmt = $conn->prepare("SELECT cat.*, COUNT(prod.cat_id) AS prod_count
    FROM categories cat LEFT JOIN products prod ON cat.id = prod.cat_id
    WHERE 1 ".$searchQuery."
    GROUP BY cat.id, cat.name ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

    foreach ($searchArray as $key=>$search) {
        $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
    }

    $stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
    $stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
    $stmt->execute();
    $item_records = $stmt->fetchAll();

    $data = array();

    foreach ($item_records as $row) {

        $data[] = array(
            "id"=>$row['id'],
            "name"=>$row['name'],
            "description"=>$row['description'],
            "prod_count"=>$row['prod_count'],
            "status"=>$row['status']
        );
     }    
    
    $pdo->close();

    $response = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecords,
        "iTotalDisplayRecords" =>  $totalRecordwithFilter,  
        "data" => $data
    );

    echo json_encode($response);
?>

The lack of proper error reporting made this more difficult to identify.

>Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

is the error message correlating to:

$stmt = $conn->prepare("SELECT cat.*, COUNT(prod.cat_id) AS prod_count
    FROM categories cat LEFT JOIN products prod ON cat.id = prod.cat_id
    WHERE 1 ".$searchQuery."
    GROUP BY cat.id, cat.name ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

because in $searchQuery you are using name without the alias which the system doesn't know which table you want name to be used from. Adding the alias in $searchQuery though then generates an new error in your first query. That variable was also being used there but the alias was not defined.

>Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat.name' in 'where clause'

Solutions:

#1 add the alias to first query

$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM categories cat WHERE 1 ".$searchQuery);

#2 use full table name in the where generation:

$searchQuery = " AND (categories.name LIKE :name OR categories.description LIKE :description) ";