Fetch data from msql into selectpickers for editing

user_12019909 0
Viewed 107

I have a code which inserts data from a modal form into two related tables, namely inventory_order table and inventory_order_product table. That code is working fine but my problem comes when I want to fetch the same data back into the modal form for editing. The code for fetch single is only retrieving data for the first line in the selectpicker while the remaining are not retrieved. Please if my question is not clear refer to the image below and please bear with me I'm a novice in php, ajax and mysql.

Edit Order

        $(document).on('click', '.update', function(){
            var inventory_order_id = $(this).attr("id");
            var btn_action = 'fetch_single';
            $.ajax({
                url:"order_action.php",
                method:"POST",
                data:{inventory_order_id:inventory_order_id, btn_action:btn_action},
                dataType:"json",
                success:function(data)
                {
                    $('#orderModal').modal('show');
                    $('#inventory_order_name').val(data.inventory_order_name);
                    $('#inventory_order_date').val(data.inventory_order_date);
                    $('#inventory_order_address').val(data.inventory_order_address);
                    $('#span_product_details').html(data.product_details);
                    $('#payment_status').val(data.payment_status);
                    $('.modal-title').html("<i class='fa fa-pencil-square-o'></i> Edit Order");
                    $('#inventory_order_id').val(inventory_order_id);
                    $('#action').val('Edit');
                    $('#btn_action').val('Edit');
                }
            })
        });
    if($_POST['btn_action'] == 'fetch_single')
    {
        $query = "
        SELECT * FROM inventory_order WHERE inventory_order_id = :inventory_order_id
        ";
        $statement = $connect->prepare($query);
        $statement->execute(
            array(
                ':inventory_order_id'   =>  $_POST["inventory_order_id"]
            )
        );
        $result = $statement->fetchAll();
        $output = array();
        foreach($result as $row)
        {
            $output['inventory_order_name'] = $row['inventory_order_name'];
            $output['inventory_order_date'] = $row['inventory_order_date'];
            $output['inventory_order_address'] = $row['inventory_order_address'];
            $output['payment_status'] = $row['payment_status'];
        }
        $sub_query = "
        SELECT * FROM inventory_order_product 
        WHERE inventory_order_id = '".$_POST["inventory_order_id"]."'
        ";
        $statement = $connect->prepare($sub_query);
        $statement->execute();
        $sub_result = $statement->fetchAll();
        $product_details = '';
        $count = '';
        $count = $count++;
        foreach($sub_result as $sub_row)
        {
            $product_details .= '
            <script>
            $(document).ready(function(){
                $("#product_id'.$count.'").selectpicker("val", '.$sub_row["product_id"].');
                $(".selectpicker").selectpicker();
            });
            </script>
            <span id="row'.$count.'">
                <div class="row">
                    <div class="col-md-8">
                        <select name="product_id[]" id="product_id'.$count.'" class="form-control selectpicker" data-live-search="true" required>
                            '.fill_product_list($connect).'
                        </select>
                        <input type="hidden" name="hidden_product_id[]" value="'.$sub_row["product_id"].'" />
                    </div>
                    <div class="col-md-3">
                        <input type="text" name="quantity[]" class="form-control" value="'.$sub_row["quantity"].'" required />
                    </div>
                    <div class="col-md-1">
            ';

            if($count == '')
            {
                $product_details .= '<button type="button" name="add_more" id="add_more" class="btn btn-success btn-xs">+</button>';
            }
            else
            {
                $product_details .= '<button type="button" name="remove" id="'.$count.'" class="btn btn-danger btn-xs remove">-</button>';
            }
            $product_details .= '
                        </div>
                    </div>
                </div><br />
            </span>
            ';
            
        }
        $output['product_details'] = $product_details;
        echo json_encode($output);
    }

mysql php ajax javascript
1 Answers

Problem in your code is with variable $count.As in your backend code this is declare outside your foreach.. loop so when first time your loop will run it will have correct value i.e : 0 but you never increment it that's why it is only giving correct value for first selectpicker. Instead you need to increment its value to set value for second value ,third and so on. i.e :

foreach($sub_result as $sub_row)
 {
//you code html and jquery

 $count = $count++;//add this inside for loop
}

Also, i don't why you have given $count = ''; . Instead , it should be $count = 0;

user_106064000
Related