I'm trying to get specific column from second table by javascript and return data of it to my view but i'm not sure how it can be done.

Logic

1. Products table has price column
2. Discount table has product_id, min, max and amount columns
3. I input number as quantity, if have product id in my discount table base on min and max return the amount as new price

Code

so far this is my codes (I am aware that specially my controller method has identifying issue to find te right data)

JavaScript

<script>
  $(document).ready(function() {
    // if quantity is not selected (default 1)
    $('#newprice').empty();
    var quantity =  parseInt(document.getElementById("quantity").value);
    var shipingcost = parseFloat(quantity);
    var shipingcostnumber = shipingcost;
    var nf = new Intl.NumberFormat('en-US', {
        maximumFractionDigits:0, 
        minimumFractionDigits:0
    });
    $('#newprice').append('Rp '+nf.format(shipingcostnumber)+'');

    // if quantity is changed
    $('#quantity').on('change', function() {
      var quantity = parseInt(document.getElementById("quantity").value);
      var qtyminmax = $(this).val();
      if(qtyminmax) {
        $.ajax({
          url: '{{ url('admin/qtydisc') }}/'+encodeURI(qtyminmax),
          type: "GET",
          dataType: "json",
          success:function(data) {
            $('#totalPriceInTotal').empty();
            var shipingcost = parseFloat(data)+parseFloat(quantity);
            var shipingcostnumber = shipingcost;
            var nf = new Intl.NumberFormat('en-US', {
                maximumFractionDigits:0, 
                minimumFractionDigits:0
            });
            $('#totalPriceInTotal').append('Rp '+nf.format(shipingcostnumber)+'');
          }
        });
      }else{
        //when quantity backs to default (1)
        $('#newprice').empty();
        var quantity = parseInt(document.getElementById("quantity").value);
        var shipingcost = parseFloat(quantity);
        var shipingcostnumber = shipingcost;
        var nf = new Intl.NumberFormat('en-US', {
            maximumFractionDigits:0, 
            minimumFractionDigits:0
        });
        $('#newprice').append('Rp '+nf.format(shipingcostnumber)+'');
      }
    });
  });
</script>

Route

Route::get('qtydisc/{id}', 'ProductController@qtydisc');

Controller

public function qtydisc($id){
      return response()->json(QtyDiscount::where('min', '>=', $id)->orWhere('max', '<=', $id)->pluck('min'));
    }

Question

1. What should I change in my controller method to get the right data?
2. What should I change in my JavaScript code? should I add product ID in my route as well or...?

UPDATE

I'm trying some changes in my code but I can't get right amount

controller

public function qtydisc($id, $qty){
      $price = DB::table('qty_discounts')
              ->where('product_id', $id)
              ->where([
                  ['min', '>=', $qty],
                  ['max', '<=', $qty],
              ])
              // ->where('min', '>=', $qty)
              // ->where('max', '<=', $qty)
              ->select('amount')
              ->first();
      return response()->json($price);
    }

route

Route::get('qtydisc/{id}/{qty}', 'ProductController@qtydisc');

javascript

//as before...

$('#quantity').on('change', function() {
      var idofproduct = ["{{$product->id}}"]; //added
      var quantity = parseInt(document.getElementById("quantity").value);
      var qtyminmax = $(this).val();
      if(qtyminmax) {
        $.ajax({
          url: '{{ url('admin/qtydisc') }}/'+idofproduct+'/'+encodeURI(qtyminmax), //changed
          type: "GET",
          dataType: "json",
          success:function(data) {
            $('#totalPriceInTotal').empty();
            var shipingcost = parseFloat(data)+parseFloat(quantity);
            var shipingcostnumber = shipingcost;
            var nf = new Intl.NumberFormat('en-US', {
                maximumFractionDigits:0, 
                minimumFractionDigits:0
            });
            $('#totalPriceInTotal').append('Rp '+nf.format(shipingcostnumber)+'');
          }
        });
      }else{
//rest of it as before

Screenshot

that's how my database look like and as results for quantity between 2 to 6 i get 7000 while i have to get 5000 from 2 to 5.

From number 7 to 9 i get no results at all.

From number 10 to up all i get is 7000

Any idea?

I'm trying to get specific column from second table by javascript and return data of it to my view but i'm not sure how it can be done.

Logic

1. Products table has price column
2. Discount table has product_id, min, max and amount columns
3. I input number as quantity, if have product id in my discount table base on min and max return the amount as new price

Code

so far this is my codes (I am aware that specially my controller method has identifying issue to find te right data)

JavaScript

<script>
  $(document).ready(function() {
    // if quantity is not selected (default 1)
    $('#newprice').empty();
    var quantity =  parseInt(document.getElementById("quantity").value);
    var shipingcost = parseFloat(quantity);
    var shipingcostnumber = shipingcost;
    var nf = new Intl.NumberFormat('en-US', {
        maximumFractionDigits:0, 
        minimumFractionDigits:0
    });
    $('#newprice').append('Rp '+nf.format(shipingcostnumber)+'');

    // if quantity is changed
    $('#quantity').on('change', function() {
      var quantity = parseInt(document.getElementById("quantity").value);
      var qtyminmax = $(this).val();
      if(qtyminmax) {
        $.ajax({
          url: '{{ url('admin/qtydisc') }}/'+encodeURI(qtyminmax),
          type: "GET",
          dataType: "json",
          success:function(data) {
            $('#totalPriceInTotal').empty();
            var shipingcost = parseFloat(data)+parseFloat(quantity);
            var shipingcostnumber = shipingcost;
            var nf = new Intl.NumberFormat('en-US', {
                maximumFractionDigits:0, 
                minimumFractionDigits:0
            });
            $('#totalPriceInTotal').append('Rp '+nf.format(shipingcostnumber)+'');
          }
        });
      }else{
        //when quantity backs to default (1)
        $('#newprice').empty();
        var quantity = parseInt(document.getElementById("quantity").value);
        var shipingcost = parseFloat(quantity);
        var shipingcostnumber = shipingcost;
        var nf = new Intl.NumberFormat('en-US', {
            maximumFractionDigits:0, 
            minimumFractionDigits:0
        });
        $('#newprice').append('Rp '+nf.format(shipingcostnumber)+'');
      }
    });
  });
</script>

Route

Route::get('qtydisc/{id}', 'ProductController@qtydisc');

Controller

public function qtydisc($id){
      return response()->json(QtyDiscount::where('min', '>=', $id)->orWhere('max', '<=', $id)->pluck('min'));
    }

Question

1. What should I change in my controller method to get the right data?
2. What should I change in my JavaScript code? should I add product ID in my route as well or...?

UPDATE

I'm trying some changes in my code but I can't get right amount

controller

public function qtydisc($id, $qty){
      $price = DB::table('qty_discounts')
              ->where('product_id', $id)
              ->where([
                  ['min', '>=', $qty],
                  ['max', '<=', $qty],
              ])
              // ->where('min', '>=', $qty)
              // ->where('max', '<=', $qty)
              ->select('amount')
              ->first();
      return response()->json($price);
    }

route

Route::get('qtydisc/{id}/{qty}', 'ProductController@qtydisc');

javascript

//as before...

$('#quantity').on('change', function() {
      var idofproduct = ["{{$product->id}}"]; //added
      var quantity = parseInt(document.getElementById("quantity").value);
      var qtyminmax = $(this).val();
      if(qtyminmax) {
        $.ajax({
          url: '{{ url('admin/qtydisc') }}/'+idofproduct+'/'+encodeURI(qtyminmax), //changed
          type: "GET",
          dataType: "json",
          success:function(data) {
            $('#totalPriceInTotal').empty();
            var shipingcost = parseFloat(data)+parseFloat(quantity);
            var shipingcostnumber = shipingcost;
            var nf = new Intl.NumberFormat('en-US', {
                maximumFractionDigits:0, 
                minimumFractionDigits:0
            });
            $('#totalPriceInTotal').append('Rp '+nf.format(shipingcostnumber)+'');
          }
        });
      }else{
//rest of it as before

Screenshot

that's how my database look like and as results for quantity between 2 to 6 i get 7000 while i have to get 5000 from 2 to 5.

From number 7 to 9 i get no results at all.

From number 10 to up all i get is 7000

Any idea?

• javascript
• php
• html
• laravel
• eloquent

• Is there any intentional reason you use an array for idofproduct (var idofproduct = ["{{$product->id}}"]; ) when it looks like you need a scalar value? (var idofproduct = "{{$product->id}}";) – alariva Commented Aug 2, 2018 at 17:21
• @alariva no it was in use in my other part of application so i just copy it from there can be as your suggestion as well, seems the issue was my database column. could you see my question in this ment please? stackoverflow./questions/51585831/… – mafortis Commented Aug 3, 2018 at 0:21
• isn't parseFloat(data)+parseFloat(quantity) should be parseFloat(data)*parseFloat(quantity) ?? – Zakaria Acharki Commented Aug 7, 2018 at 9:30
• Show us your HTML structure please. – Zakaria Acharki Commented Aug 7, 2018 at 9:36
• Why route URL is qtydisc/{id}/{qty} but ajax URL is admin/qtydisc? – Bruno Lebtag Commented Aug 7, 2018 at 17:21

5 Answers 5

The main problem es from your eloquent statement, you should use the >= in both conditions with OR operator using the orWhere helper, so it should be like :

$price = QtyDiscounts::where('product_id', $id)
    ->where('min', '>=', $qty)
    ->orWhere('max', '>=', $qty)
    ->pluck('amount')
    ->first();

But you need really to take a look to your JS structure, I suggest to split your code to function for the DRY concept, first the event listener like :

$('body').off('input', '#quantity').on('input', '#quantity', function () {
    var qty = parseInt( $(this).val() );

    if(qty) {
        getAmount(qty);
    }else{
        getAmount(1);
    }
});

Then the helper functions to call :

function setValue(quantity, amount)
{
    var newPrice = $('#newprice');
    var totalPrice = $('#totalPriceInTotal');

    newPrice.empty();
    totalPrice.empty();

    var nf = new Intl.NumberFormat('en-US', {
        maximumFractionDigits:0,
        minimumFractionDigits:0
    });

    newPrice.val('Rp '+nf.format(amount)+'');
    totalPrice.val('Rp '+nf.format(parseFloat(amount)*parseFloat(quantity))+'');
};

function getAmount(quantity)
{
    var url = $('#qty_url').val();
    var productId = $('#product_id').val();

    $.ajax({
        url: url+'/'+productId+'/'+encodeURI(quantity),
        type: "GET",
        dataType: "json",
        success:function(amount) {
            setValue(quantity, amount);
        }
    });
};

I guess you've a simple HTML structure, so it should work this way, why I suggest here if never using the php variable like url & product_id directly inside your JS code instead attach the values you want to hidden inputs and get the value of these input's using the JS :

<input type="hidden" id="qty_url" value="{{ url('admin/qtydisc') }}" />
<input type="hidden" id="product_id" value="{{ $product->id }}"/>

Use the opposite condition in the backend controller:

$price = DB::table('qty_discounts')
          ->where('product_id', $id)
          ->where('min', '<=', $qty)
          ->where('max', '>=', $qty)
          ->select('amount')
          ->first(); 

So for $id = 2 and $qty = 5 you will get

product id = 2  and `min` <= 5 and `max` >= 5

selecting the first row (row_id = 1)

Stop mixing JavaScript with jQuery, instead of

var quantity = parseInt(document.getElementById("quantity").value);

You could just do

var quantity = +$('#quantity').val();

And expect this to work even in old Internet Explorer

Going further

Routes

Route::get('/ajax/product/{product}/distcounts', 'AjaxController@productDiscounts);

Html

<input type="number" name="quantity" data-product_id="{{ $product->getKey() }}">

PHP

public function productDiscounts(Product $product, Request $request) {
    // $product is just a product you are interested in
    // $request->quantity containts passed amount

    $discounts = DiscountModel::product(     // Scope
        $product->getKey()
    )
    ->get()
    ->filter(function($element) use($request) {
        return ($element->min <= $request->quantity && $element->max >= $request->quantity);
    })
    ->values();

    return response->json(discounts);
}

JavaScript

(function($) {
    var getDiscountAmount(e) {
        var $input      = $(e.target);
        var product_id  = $input.data('product_id');

        $.ajax({
            method: 'GET',
            url: '/ajax/product/' + product_id + '/discount',
            data: {
                quantity: $input.val()
            },
            beforeSend: function() {
                $input.prop('disabled', true);
            },
            success: function(discounts) {
                $input.prop('disabled', false);

                // Dunno what you want to do with list of discounts right there
                // It should be an array containing 1 object, but who knows
            }
        })
    }

    $(input[name="quantity"]).on('change', getDiscountAmount);
})(jQuery);

Hope it helps, didn't test the code so you may need to add some tweaks

If you ever in a situation to share data from controller to JS, then I suggest using this package JsTransformers. It makes you do something like this

// in controller
public function index()
{
    JavaScript::put([
        'foo' => 'bar',
        'user' => User::first(),
        'age' => 29
    ]);

    return view('index');
}

// in view scripts
console.log(foo); // bar
console.log(user); // User Obj
console.log(age); // 29

You should write your query like this:

public function qtydisc($id, $qty){
      $price = DB::table('qty_discounts')
              ->where('product_id', $id)
              ->where($qty, '>=', 'min')
              ->where($qty, '<=', 'max')
              ->select('amount')
              ->first();
      return response()->json($price);
    }