select2 cat-prod

<?php

$dbsetup = [
  'host' => 'localhost', //plz change
  'user' => 'gunawan', //plz change
  'pass' => 'ya4i8ib2', //plz change
  'name' => 'gun_learning' //plz change
];

$conn =new PDO("mysql:host=" . $dbsetup['host'] . ";dbname=" . $dbsetup['name'] . "", $dbsetup['user'], $dbsetup['pass']);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//--------------------
if($act=='data')
{
    $result=[];
    $result[]=[
        'id'=>-1,
        'text'=>'Silahkan memilih dari pilihan dibawah'
    ];
    $type = $_GET['type']??'none';
    $search = $_GET['search']??NULL;
    if($type=='prod')
    {

        $cat = $_GET['category']??NULL;
        if($cat==NULL || (int)$cat == 0)
        {
            $result=[];
            $result[]=[
                'id'=>-1,
                'text'=>'Silahkan memilih Category'
            ];

        }
        else
        {
            $sql="select nama from tb_category where id=?";
            $que = $conn->prepare($sql);
            $que->bindValue(1, $cat);
            $que->execute();
            $cat_name = $que->fetchColumn();
            $raw[ ]=$sql;

            if(strlen($search)<3)
            {
                $sql="select id, nama from tb_prod where cat_id=? limit 5";
                $que = $conn->prepare($sql);
                $que->bindValue(1, $cat);
            }
            else
            {
                $sql="select id, nama from tb_prod where  cat_id=? and nama like ? limit 5";
                $que = $conn->prepare($sql);
                $que->bindValue(1, $cat);
                $que->bindValue(2, "%$search%");
            }
            $raw[ ]=$sql;
            $que->execute();
            $data=[];$children=[];
            while($row = $que->fetch())
            {
                $result0[]=[
                    'id'=>$row['id'],
                    'text'=>$row['nama']
                ];
                $children[]=[
                    'id'=>$row['id'],
                    'text'=>$row['nama']
                ];
                $data[]=$row;
            }
            $result[]=[
                'text'=>$cat_name,
                'children'=>$children
            ];
            $raw['data']=$data;
        }

    }
    if($type=='category')
    {
        if(strlen($search)<3)
        {
            $sql="select id, nama from tb_category";
            $que = $conn->prepare($sql);
        }
        else
        {
            $sql="select id, nama from tb_category where nama like ?";
            $que = $conn->prepare($sql);
            $que->bindValue(1, "%$search%");
        }

        $que->execute();
        $data=[];
        while($row = $que->fetch())
        {
            $result[]=[
                'id'=>$row['id'],
                'text'=>$row['nama']
            ];
            $data[]=$row;
        }
        $raw['data']=$data;
    }

    $return=[
        'results'=>$result,
        'params'=>$_GET,
        'raws'=>@$raw
    ];
    echo json_encode($return);
    die;
}
?>
<html lang="en">
<head>
    <title>jquery select2 ajax php category - Prod</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha256-aAr2Zpq8MZ+YA/D6JtRD3xtrwpEz2IqOS+pWD/7XKIw=" crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha256-OFRAJNoaD8L3Br5lglV7VyLRf0itmoBzWUoM+Sji4/8=" crossorigin="anonymous"></script>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>

<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/select2.min.css" rel="stylesheet" />
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/select2.min.js"></script>
<style>
    .select-js{
        width:200px;
    }
    body{
        padding:10px 20px;
    }
</style>
</head>
<body>
<form>
<!--br/>Test  <select class='select-js js-data-example-ajax'><option>Pilih Repo</option></select-->
<br/>Kategori  <select class='select-js select-cat'><option>Pilih kategori tersedia</option></select>
<br/>Barang  <select class='select-js select-prod'><option>Pilih kategori terlebih dahulu</option></select>
</form>

<script>
$(document).ready(function() {
    $('.js-data-example-ajax, .select-cat').select2({
      ajax: {
        url: 'index.php?act=data',
        dataType: 'json',
        // Additional AJAX parameters go here; see the end of this chapter for the full code of this example
        data: function(params)
        {
            var query={ search:params.term, type:'category'};
            return query;
        },
        processResults: function (data) {
            return {
              results: data.results
            };
        },
      }
    });
    $('.select-cat').on("select2:select", function(e) {
        $(".select-prod").empty();
        //console.log('see');// what you would like to happen
    });

    $('.select-prod').select2({
      ajax: {
        url: 'index.php?act=data',
        dataType: 'json',
        // Additional AJAX parameters go here; see the end of this chapter for the full code of this example
        data: function(params)
        {
            var query={ search:params.term, type:'prod', category:$(".select-cat").val()};
            return query;
        },
        processResults: function (data) {
            return {
              results: data.results
            };
        },
      }
    });

});

</script>

</body>
</html>
<?php
/*****
-----DB CONTOH
-- phpMyAdmin SQL Dump

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `your_db_name`
--

-- --------------------------------------------------------

--
-- Table structure for table `tb_category`
--

CREATE TABLE `tb_category` (
  `id` int(11) NOT NULL,
  `nama` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `modified_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tb_category`
--

INSERT INTO `tb_category` (`id`, `nama`, `created_at`, `modified_at`) VALUES
(1, 'Sofa', '2022-05-01 00:00:35', NULL),
(2, 'Kursi', '2022-05-01 00:00:35', NULL),
(3, 'Meja', '2022-05-01 00:00:35', NULL),
(4, 'Tekstil', '2022-05-01 00:00:35', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `tb_prod`
--

CREATE TABLE `tb_prod` (
  `id` int(11) NOT NULL,
  `nama` varchar(50) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `modified_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tb_prod`
--

INSERT INTO `tb_prod` (`id`, `nama`, `cat_id`, `created_at`, `modified_at`) VALUES
(1, 'Kivik', 1, '2022-05-01 00:29:36', NULL),
(2, 'Sofa 2 dudukan', 1, '2022-05-01 00:29:36', '2022-04-30 17:43:52'),
(3, 'Kursi Berlengan', 2, '2022-05-01 00:29:36', '2022-04-30 17:30:59'),
(4, 'Meja Tamu', 3, '2022-05-01 00:29:36', '2022-04-30 17:31:09'),
(5, 'Meja Makan', 3, '2022-05-01 00:29:36', '2022-04-30 17:31:15'),
(6, 'Gorden', 4, '2022-05-01 00:31:49', NULL),
(7, 'Sarung Bantal', 4, '2022-05-01 00:31:49', NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tb_category`
--
ALTER TABLE `tb_category`
  ADD PRIMARY KEY (`id`),
  ADD KEY `nama` (`nama`);

--
-- Indexes for table `tb_prod`
--
ALTER TABLE `tb_prod`
  ADD PRIMARY KEY (`id`),
  ADD KEY `nama` (`nama`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tb_category`
--
ALTER TABLE `tb_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `tb_prod`
--
ALTER TABLE `tb_prod`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

*****/