vb_book/app/Models/BooksModel.php

<?php 
namespace App\Models;
use CodeIgniter\Model;
class BooksModel extends Model
{
    protected $table = 'books';
    protected $primaryKey = 'book_id';
    protected $allowedFields = ['book_id','author','publishers_code','title','cover_picture','publication_date','isbn_code','publisher','category','genre','language','description','page_count','tax','price','created_on','created_by','updated_on','updated_by','isactive','business_id'];

    public function saveData($data, $id = null)
    {
        if ($id === null) {
            // Insert new record
            return $this->insert($data);
        } else {
            // Update existing record
            return $this->update($id, $data);
        }
    }

    public function insertImages($imgData) {
        $this->db->table('book_images')->insert($imgData);
        return $this->db->insertID(); // Return the last inserted ID
    }

    public function insertImagesBatch($imgDataArray) {
        $this->db->table('book_images')->insertBatch($imgDataArray);
        return $this->db->insertID(); // Note: insertID() might not be applicable for batch inserts
    }
    public function getnonMembershiplist($business_id)
{
    $builder = $this->builder();
    $builder->select('books.book_id,books.author, books.wp_api_product_id, books.title, books.price, books.duration, books.isactive, books.publisher, books.genre, books.language, books.publication_date, CASE WHEN category.name THEN GROUP_CONCAT(category.name SEPARATOR \',\') ELSE \'\' END as name, category.id as category_id');
    $builder->join('book_categories', 'book_categories.book_id = books.book_id and book_categories.isactive = 1', 'left');
    $builder->join('category', 'category.id = book_categories.category_id', 'left');
    $builder->where('books.business_id', $business_id);
    $builder->where("LOWER(category.name) != 'membership' OR category.id IS NULL");
    $builder->groupBy('books.book_id');
    $query = $builder->get();

    $books = [];

    foreach ($query->getResult() as $row) {
        $books[$row->book_id] = [
            'title' => $row->title,
            'price' => $row->price,
            'duration' => $row->duration,
            'isactive'=>$row->isactive,
            'book_id'=>$row->book_id,
            'publisher'=>$row->publisher,
            'author'=>$row->author,
            'genre'=>$row->genre,
            'language'=>$row->language,
            'publication_date'=>$row->publication_date,
            'name'=>$row->name,
            'category_name' => $this->getGroupCategoryName($row->book_id),
            'category_id' => $row->category_id,
            'wp_api_product_id'=>$row->wp_api_product_id
        ];
    }

    return $books;
}
public function insertBooksCategory($book_id, $category_id)
{
    $data = [
        'book_id' => $book_id,
        'category_id' => $category_id,
    ];

    $db = \Config\Database::connect();
    $builder = $db->table('book_categories');
    $builder->insert($data);
}
public function getCategories()
    {
        // Use the Query Builder to retrieve categories from the 'categories' table
        $categories = $this->db->table('category')->groupBy('category.name')->get()->getResultArray();
        return $categories;
    }
    public function getBooksAndImagesByBookId($id)
    {
        $builder = $this->db->table('books');
        $builder->select('books.*, book_images.img_name, book_images.is_cover, book_images.type, book_categories.category_id');
        $builder->join('book_images', 'book_images.book_id = books.book_id', 'left');
        $builder->join('book_categories', 'book_categories.book_id = books.book_id and book_categories.isactive = 1', 'left');
        $builder->where('books.book_id', $id);
        $builder->orderBy('book_images.created_on', 'DESC'); 
    
        $query = $builder->get();
    
        return $query->getResultArray();
    }
    
public function insertImage($imageData)
{
    return $this->db->table('book_images')->insert($imageData);
}


public function getData($table, $where = null,$whereIn = null)
{
        $query = $this->db->table($table);
        if ($where) {
            $query->where($where);
        }
        if($whereIn){
            // $query->whereIn($column_name,$missingValues)
            $query->whereIn($whereIn[0],$whereIn[1]);
        }
        return $query->get()->getResult();
}

    public function inactiveMissingDetails($table_name,$dataToUpdate,$where = null,$whereIn = null)
    {
        $this->db->table($table_name);
        $query = $this->db->table($table_name);
        if ($where) {
            $query->where($where);
        }
        if ($whereIn) {
            $query->whereIn($whereIn[0], $whereIn[1]);
        }
        return $query->update($dataToUpdate);
    }

    public function getGroupCategoryName($book_id){
        $query = $this->db->table('category')
        ->select('category.name, book_categories.isactive')
        ->join('book_categories', 'book_categories.category_id = category.id')
        ->where('book_categories.book_id', $book_id)
        ->where('book_categories.isactive', 1)
        ->get();
        $results = $query->getResult();
        $categoryNames = array();
        if ($results) {
            foreach ($results as $result) {
                $categoryName = $result->name;
                $isActive = $result->isactive;
                if ($isActive == 1) {
                    $categoryNames[] = $categoryName;
                }
            }
            $commaSeparatedNames = implode(', ', $categoryNames);
            return $commaSeparatedNames;
        } else {
            return "";
        }
    }
    public function saveBook($bookName, $bookPrice,$isbn_code,$publication_date,$publication_code,$publisher_name) {
        // Prepare data to insert into the database
        $data = array(
            'title' => $bookName,
            'price' => $bookPrice,
            'isbn_code'=>$isbn_code,
            'publication_date'=>$publication_date,
            'publishers_code'=>$publication_code,
            'publisher'=>$publisher_name
            // Add other columns as needed
        );
    
        // Use Query Builder to insert data into the 'books' table
        $this->db->table('books')->insert($data);
    
        // Return the ID of the inserted record
        return $this->db->insertID();
    }
    public function getAllActiveBooks() {
        // Fetch active books from the database
        $query = $this->db->table('books')
                        ->where('isactive', 1)
                        ->get(); // No need to pass 'books' again here
        return $query->getResult(); // Use getResult() instead of result()
    }
    

}