Show multiple rows from database(SQL) using php/javascript(ajax) to front end of website

Question

I'm still very new to php and javascript(ajax) and I've tried looking at various posts and websites trying to find my answer, but to no avail.

I'm trying to show all related rows from my database on the front end of a website. My SQL statement is currently picking up all the rows I need, but I can't get it to show all the rows (I can get only one row to show).

Can someone guide me a bit and tell me what to do to get it right? My code is as follows:

PHP-

$result = mysql_query("SELECT lot_num, buyer_id, item_desc, quantity, price FROM auction_items where auction_id = (SELECT id FROM auction ORDER BY date_created desc Limit 1) ORDER BY id DESC");          //query

    $table_data = array();
   while($row = mysql_fetch_array($result)){
       $table_data[]= (array('lot_num' => $row["lot_num"], 'buyer_id' => $row["buyer_id"], 'item_desc' => $row["item_desc"], 'quantity' => $row["quantity"], 'price' => $row["price"]));
    }
   // $array = mysql_fetch_row($result);    

    echo json_encode($table_data);

I'm also going to include my javascript(ajax) code to cover my bases:

$("#submit").click(function(){
    $.ajax({                                      
        url: 'auctionItemLoad.php',      //the script to call to get data          
        dataType: 'json',                //data format      
        success: function(data)          //on recieve of reply
        {
            var lot_num = data[0];              
            var buyer_id = data[1];           
            var item_desc = data[2];
            var quantity = data[3];
            var price = data[4];
            $('#lot_num_result').html(lot_num);
            $('#buyer_id_result').html(buyer_id);
            $('#item_desc_result').html(item_desc);
            $('#quantity_result').html(quantity);
            $('#price_result').html(price);
        }
        });
    });

Answer 1

The reason only one row is showing is because you are only accessing data's first row, you have to iterate on all of its rows to show all of its data. Just like you iterate on all the sql request's result rows in your php file to add them to the $table_data array, you have to iterate on data in your ajax request's success callback, this time outputting the data. You can do this with JQuery's each function.

...
success: function(data) {
    $.each(data, function(i, row) {
        var lot_num = row.lot_num;              
        var buyer_id = row.buyer_id;           
        var item_desc = row.item_desc;
        var quantity = row.quantity;
        var price = row.price;

        $('#lot_num_result').append(lot_num);
        $('#buyer_id_result').append(buyer_id);
        $('#item_desc_result').append(item_desc);
        $('#quantity_result').append(quantity);
        $('#price_result').append(price);
    )};
}
...