CODEWITHSUNDEEP
javascriptphpjsonajax
john c
john c

Reputation: 65

How can I retrieve 1 row at a time from a db on button click?

I'm developing an item picking system and need to iterate through a table one item at a time on the click of a button. Currently, the button only retrieves and displays the first item in the table. How can I change it so it iterates through each one at a time when I click the button? Any help would be appreciated, thank you

Here's my php file to retrieve data

mysqli_select_db($con,"items");


$sql="SELECT * FROM items Limit 1 ";
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_assoc($result))
    $rows[] = array_map('utf8_encode', $row);

header('Content-Type: application/json');
echo json_encode($rows);

And here's my button

<script>
 $(document).ready(function () {
$('#button1').click(function (e) {
    $.getJSON("getItem.php", function(result){
      $.each(result, function(i, field){
         $("#div1").empty();
        $("#div1").append(JSON.stringify(result));     //alert(JSON.stringify(data));
      });
    });
  });
});
</script>

Upvotes: 3

Views: 86

Answers (2)

Ropali Munshi
Ropali Munshi

Reputation: 3006

You need to maintain a variable to handle the offset for the LIMIT query. You can try this way,

JS Code :

<script>
var offset = 0;

$(document).ready(function () {
$('#button1').click(function (e) {
    // pass offset value with GET request
    $.getJSON("getItem.php?offset=" + offset, function(result){

      offset++; // increment the value after successful AJAX call
      $.each(result, function(i, field){
         $("#div1").empty();
        $("#div1").append(JSON.stringify(result));     //alert(JSON.stringify(data));
      });
    });
  });
});
</script>

PHP Code :

mysqli_select_db($con,"items");

// Get the offset value
$offset = empty($_GET['offset']) ? 0 : $_GET['offset']

$sql="SELECT * FROM items Limit 1, $offset"; // pass the offset value to LIMIT query
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_assoc($result))
    $rows[] = array_map('utf8_encode', $row);

header('Content-Type: application/json');
echo json_encode($rows);

Upvotes: 3

Gagich
Gagich

Reputation: 108

I would put an attribute in button1 to keep track of the count.

<div id="button1" count="1">clickme</div>

In your javascript, add code to find that count and send it in your request to your PHP page.

('#button1').click(function (e) {
var thisCount = document.getElementById('button1').getAttribute('count');
// don't forget to increase that count for the next time
document.getElementById('button1').setAttribute('count', (thisCount + 1));
$.getJSON("getItem.php?count=" + thisCount, function(result){
// ... rest of code

In your PHP page, I have to assume you have some kind of ID as the key. item_id = 1, item_id = 2, etc. Change the query to this: 

"SELECT * FROM items WHERE item_id = ".$_GET['count'].";"

Upvotes: 1

Related Questions