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);
        }
        });
    });

what does console.log(data) shows in the console if you type it instead of var lot-.num=... and the whole stuff? success: function(data) { console.log(data); } — Marouen Mhiri
– Marouen Mhiri, Commented Sep 4, 2016 at 20:37
Well, after I added Nich's addition, it now shows all my objects that I have in the database. — PACIFIC_STATE
– PACIFIC_STATE, Commented Sep 4, 2016 at 23:27

nich · Accepted Answer · 2016-09-04 21:12:10Z

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);
    )};
}
...

edited Sep 4, 2016 at 21:12

answered Sep 4, 2016 at 21:06

nich

1083 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PACIFIC_STATE Over a year ago

This seems to be showing me everything, but it's all on one line. How would I go about break the information up so that after it iterates through a row (or variable) it will go to the next line down?

nich Over a year ago

@PACIFIC_STATE If the id selectors (#lot_num_result, #buyer_id_result, etc.) were columns, I'd add each item in them on a different row in divs for the sake of the simplicity of this example. Instead of doing $('#lot_num_result').append(lot_num);, I'd do $('#lot_num_result').append('<div>' + lot_num + '</div>'); This is probably not exactly what you are looking for though. This solution to dynamically create tables might be more useful: stackoverflow.com/a/8302187/5709703

PACIFIC_STATE Over a year ago

Thank you very much. You've been a big help!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related