phpjqueryajaxphp-7.3

jQuery Ajax Request fails to bring all data with SQL Query to fields


i have a form that works by filling the next select fields with data based on the previous ones, it works perfect on Localhost / Xampp with PHP8 but now when i try to get it on my server it only works "once" per category.

My problem simplified.

  1. I select category1 and get two results based on that to the next select
  2. Only one of these results works with returning data to the 3rd Select and the other one doesn't return anything with json_encode and only throws an error

More info: Request that works.

{
    "request": "2",
    "subcategoryid": "11",
    "alakategoriaID": "15"
}
[

Response which is correct.

{
    "ID": "23",
    "name": "Puu 25"
},
{
    "ID": "24",
    "name": "Puu 50"
}

Request that doesn't return anything

{
        "request": "2",
        "subcategoryid": "10",
        "alakategoriaID": "15"
    }

Main function that contains the select fields etc..

´´´<script>
  $(function () {

    // Country
    $('#sel_country').change(function () {

      var categoryID = $(this).val();

      // Empty state and city dropdown
      $('#sel_state').find('option').not(':first').remove();
      $('#sel_city').find('option').not(':first').remove();
      $('#varichanger').find('option').not(':first').remove();

      // AJAX request
      $.ajax({
        url: 'helper.php',
        type: 'post',
        data: {
          request: 1,
          categoryID: categoryID
        },
        dataType: 'json',
        success: function (response) {

          var len = response.length;

          for (var i = 0; i < len; i++) {
            var id = response[i]['id'];
            var name = response[i]['name'];

            $("#sel_state").append("<option value='" + id + "'>" + name + "</option>");
          }
        },
        error: function(XMLHttpRequest, textStatus, errorThrown) {
          alert("some error");
        }
      });

      $.ajax({
        url: 'helper.php',
        type: 'post',
        data: {
          request: 3,
          categoryID: categoryID
        },
        dataType: 'json',
        success: function (response) {
          var len = response.length;

          for (var i = 0; i < len; i++) {
            var id = response[i]['id'];
            var name = response[i]['name'];

            $("#varichanger").append("<option value='" + id + "'>" + name + "</option>");
          }
        },
        error: function(XMLHttpRequest, textStatus, errorThrown) {
          alert("some error");
        }
      });

    });

    // State
    $('#sel_state').change(function () {
      var subcategoryid = $(this).val();
      var alakategoriaID = $('#sel_country').val();

      // Empty city dropdown
      $('#sel_city').find('option').not(':first').remove();

      // AJAX request
      $.ajax({
        
        url: 'helper.php',
        type: 'post',
        data: {
          request: 2,
          subcategoryid: subcategoryid,
          alakategoriaID: alakategoriaID
        },
        dataType: 'json',
        success: function (response) {
          console.log(response);

          var len = response.length;

          for (var i = 0; i < len; i++) {
            var id = response[i]['ID'];
            var name = response[i]['name'];

            $("#sel_city").append("<option value='" + id + "'>" + name + "</option>");

          }
        },  
          error: function(XMLHttpRequest, textStatus, errorThrown) {
          alert("some error");
        }
      });
    });
  });
</script>´´´

Helper.php

include "pdoconfig.php";
error_reporting(0);

$request = 0;$request = 0;

if(isset($_POST['request'])){
   $request = $_POST['request'];
}

// Fetch subcategory list by categoryID
if(trim($request) == 1){
   $categoryID = $_POST['categoryID'];

   $stmt = $conn->prepare("SELECT * FROM alakategoriat WHERE kategoriaID=:kategoriaID ORDER BY subcategoryname");
   $stmt->bindValue(':kategoriaID', (int)$categoryID, PDO::PARAM_INT);

   $stmt->execute();
   $subcategorysList = $stmt->fetchAll();

   $response = array();
   foreach($subcategorysList as $subcategory){
      $response[] = array(
        "id" => $subcategory['id'],
        "name" => $subcategory['subcategoryname']
      );
   }
   echo json_encode($response);
   exit;
}

// Fetch city list by subcategoryid
if(trim($request) == 2){
   $kategoriaID = $_POST['alakategoriaID'];
   $alakategoriaID = $_POST['subcategoryid'];

  
   $stmt = $conn->prepare("SELECT * FROM tuotteet 
   WHERE kategoriaID=$kategoriaID
   AND alakategoriaID=$alakategoriaID
   ORDER BY productname");

   $stmt->execute();
   $productslist = $stmt->fetchAll();

   $response = array();
   foreach($productslist as $product){
      $response[] = array(
         "ID" => $product['ID'],
         "name" => $product['productname']
      );
   }
   echo json_encode($response);
   exit;
}
if(trim($request) == 3){
   $categoryID = $_POST['categoryID'];

   $stmt = $conn->prepare("SELECT * 
   FROM kategoriavarit 
   INNER JOIN varit ON kategoriavarit.variID = varit.variID
   WHERE kategoriaID=:kategoriaID");
   $stmt->bindValue(':kategoriaID', (int)$categoryID, PDO::PARAM_INT);

   $stmt->execute();
   $varilist = $stmt->fetchAll();

   $response = array();
   foreach($varilist as $vari){
      $response[] = array(
        "id" => $vari['variID'],
        "name" => $vari['varinimi']
      );
   }
   echo json_encode($response);
   exit;
}

Solution

  • Solved the problem by clearing and reinserting my database values and following @Foramkumar Patel's answer

    EDIT: Cancel that, problem was with scandinavian letters in the response from JSON causing many different problems, solved the problem by utf_8 encoding the response.