0

In wordpress I have database tables like this

product name

id product_name

1   A
2   B
3   C
4   D

product_buying_price

id product_name  product_buying_price
1   A                               10
2   B                           12
3   C                               15
4   D                               18

product_selling_price

id product_name product_selling_price
1   A                               12
2   B                           13
3   C                               19
4   D                               23

So I have a dropdown like this

<select name="product_name" id="product_name">
    <option value="">Products</option>
    <option value="A">A</option>
    <option value="B">B</option>
    <option value="C">C</option>
    <option value="D">D</option>
</select>

and I want to show the values for product buying price and product selling price inside a table like this

<table>
    <tr>
        <td>
            <select name="product_name" id="product_name">
                <option value="">Products</option>
                <option value="A">A</option>
                <option value="B">B</option>
                <option value="C">C</option>
                <option value="D">D</option>
            </select>
        </td>
        <td class="product_buying_price"></td>
        <td class="product_selling_price"></td>
    </tr>
</table>

Like this I have more around 3-4 such fields and I want to get those values for the product.

In the js file I have made the ajax like this

$('body').on('change','#product_name', function() {
    var selected = $(this).val;

    //Get product buying price
    $.post( test.ajaxUrl, { 'selected' : selected,  'action' : 'get_product_buying_price' }, function(data){
        data = $.parseJSON(data);
        selected.find('td.product_buying_price').html(data);
    });

    //Get product selling price
    $.post( test.ajaxUrl, { 'selected' : selected,  'action' : 'get_product_selling_price' }, function(data){
        data = $.parseJSON(data);
        selected.find('td.product_selling_price').html(data);
    }); 
});

Inside function I have the function like this

function get_product_buying_price() {
    global $wpdb;
    $selected_product = $_POST['selected'];
    $get_product_price = $wpdb->get_row("SELECT `product_buying_price` FROM `product_buying_price` WHERE `product_name` = '.$selected_product.' ");
    $product_buying_price = $get_product_price->product_buying_price;
    echo json_encode($product_buying_price);
    exit;
}

function get_product_selling_price() {
    global $wpdb;
    $selected_product = $_POST['selected'];
    $get_product_price = $wpdb->get_row("SELECT `product_selling_price` FROM `product_selling_price` WHERE `product_name` = '.$selected_product.' ");
    $product_selling_price = $get_product_price->product_selling_price;
    echo json_encode($product_selling_price);
    exit;
}

Here its working fine. But don't you think that doing multiple ajax request on one click makes it slow? I have around 3-4 request for one change. So can someone tell me some smarter way to achive this? Any help and suggestions will be really appreciable. Thanks.

1 Answer 1

3

I appreciate your question is quite old and you no doubt already have your solution but hopefully this may help someone else.

You could replace your javascript post calls with a single call to a single function that extracted both parts of data and returned them as an object.

$('body').on('change','#product_name', function() {
    //Get product prices
    $.post( test.ajaxUrl, {
            'selected' : $(this).val(),
            'action' : 'get_product_prices'
         }, function(data){
             data = $.parseJSON(data);
             $(this).parent('tr').find('td.product_buying_price').text(data.buying)
             .end().find('td.product_selling_price').text(data.selling);
    });
});

Then your php function would look like this.

function get_product_prices(){
    global $wpdb;
    $returnData = array();
    $selected_product =  esc_sql($_POST['selected']);
    // please see the notes on your database structure
    $get_product_price = $wpdb->get_row("SELECT `product_buying_price` FROM `product_buying_price` WHERE `product_name` = '.$selected_product.' ");
    $returnData['buying'] = $get_product_price->product_buying_price;
    $get_product_price = $wpdb->get_row("SELECT `product_selling_price` FROM `product_selling_price` WHERE `product_name` = '.$selected_product.' ");
    $returnData['selling'] = $get_product_price->product_selling_price;
    echo json_encode($returnData);
    exit;
}

I'm not hugely familiar with the wpdb class; it may be possible to replace your sql queries with a single query like this

SELECT `product_buying_price`.`product_buying_price`,  `product_selling_price`.`product_selling_price`
FROM `product_buying_price`
INNER JOIN `product_selling_price`
ON `product_buying_price`.`product_name` = `product_selling_price`.`product_name`
WHERE `product_buying_price`.`product_name` = '.$selected_product.'"

However from the look of your database tables, you appear to have an unnecessary number of tables and would be better to just store all data in one table

product_data

id   product_name  product_selling_price  product_buying_price
1    A             12                     10
2    B             13                     12
3    C             19                     15
4    D             23                     18

I'd also apply some checks within the PHP and Javascript to ensure you have all the required data for input into your html table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.