Update MySql database record in while loop with ajax - updates only first row

Question

When I click on button "Upisi" it works fine for the first record, bt on all other records it won't work.

ocitanja.php:

<?php
include_once("header.php");
require("spajanje.php");
if(!isset($_POST["ulica"]))
{
	header('Location: index.php');
}
else $_SESSION["ulica"] = $_POST["ulica"];
echo "Očitač: " . $_SESSION["ocitac"] . "</br></br>";
echo "<form action='ulica.php'><input type='submit' value='Odabir ulice'></br></br>Odabrana ulica:</br>" . $_POST['ulica'] . "</form>";
echo "</br></br>";
?>
<table>
<?php
$korisnici = mysqli_query($conn, "SELECT * FROM ocitanja WHERE ocitac ='".$_SESSION["ocitac"]."' AND ulica ='".$_POST["ulica"]."'");

while ($row = $korisnici->fetch_assoc()){
    echo "<tr><td><b>" . $row['prezime_ime'] . "</b></td></tr>";
    echo "<tr><td>Kućni broj: <b>" . $row['kbr'] . "</b></td></tr>";
    echo "<form><tr><td>
		<label>Broj plinomjera: </label><input type='text' id='bb' size='8' disabled value = '". $row['bb'] ."'></td></tr>";
    echo "<tr><td>Zadnje očitanje: " . $row['staro_stanje'] . " m<sup>3</sup></td></tr>";
    echo "<tr><td>
		<label>Očitanje: </label>
		<input id='ocitanje' type='text' size='6' value = '". $row['ocitanje'] ."'> m<sup>3</sup>
		<input onclick='myFunction()' type='button' value='Upisi'>
		</td></tr></form>";
}
?>

</table>
<?php
include_once("footer.php");
?>

ajax.js.php:

<?php
$bb2 = $_POST['bb1'];
$ocitanje2 = $_POST['ocitanje1'];
require("spajanje.php");
if (isset($_POST['ocitanje1'])) {
    mysqli_query($conn, "UPDATE ocitanja SET ocitanje='$ocitanje2' WHERE bb='$bb2'"); //Insert Query
    echo "Uspješno upisano očitanje!";
}
?>

script.js:

function myFunction() {
    var bb = document.getElementById("bb").value;
    var ocitanje = document.getElementById("ocitanje").value;
// Returns successful data submission message when the entered information is stored in database.
    var dataString = 'bb1=' + bb + '&ocitanje1=' + ocitanje;
    if (ocitanje == '') {
        alert("Unesi očitanje!");
    } else {
// AJAX code to submit form.
        $.ajax({
            type: "POST",
            url: "ajaxjs.php",
            data: dataString,
            cache: false,
            success: function(html) {
                alert(html);
            }
        });
    }
    return false;
}

What am I doing wrong? Any other ideas how to update every record separately in while loop? But without refreshing the page. Thanks

Answer 1

Problem

Issue is simple. In ocitanjia.php, in this line:

        <label>Broj plinomjera: </label><input type='text' id='bb' size='8' disabled value = '". $row['bb'] ."'></td></tr>";

every input field have the same id ('bb'), so in script.js, when you call this statement:

    var ocitanje = document.getElementById("ocitanje").value;

jQuery will return the value of first text box since this is how jQuery behaves when it encounters multiple elements with same id.

Solution Though many things need to be corrected. Shortest way is to do this: In ocitanjia.php, change this

echo "<tr><td>
    <label>Očitanje: </label>
    <input id='ocitanje' type='text' size='6' value = '". $row['ocitanje'] ."'> m<sup>3</sup>
    <input onclick='myFunction()' type='button' value='Upisi'>
    </td></tr></form>";

to this:

echo "<tr><td>
    <label>Očitanje: </label>
    <input id='ocitanje' type='text' size='6' value = '". $row['ocitanje'] ."'> m<sup>3</sup>
    <input onclick='myFunction(\'". $row['bb'] ."\')' type='button' value='Upisi'>
    </td></tr></form>";

AND in script.js change this:

function myFunction() {
var bb = document.getElementById("bb").value;

to this:

function myFunction(bb) {

Explanation: Pass value of bb as a parameter to the function that will pass unique value of bb every time the function is called

Answer 2

It's hard to know where exactly the problem is but I can see where some issues might arise.

First, IDs are unique as in two elements should not have the same IDs. That can be a reason why it is not working correctly as you are always using the same pair of elements into your AJAX call. So let's turn them into classes.

Second, let's leverage jQuery since you are using it. If you are using this library, there's really no reason to ever use onclick (or any other event) attribute. Let's remove that attribute on your button and instead give it a class e.g. upisi that we'll be referencing shortly.

Third, you cannot have <form/> tag placed in between <tr/>. That is invalid HTML. Since we don't need, remove it. If you want to group <tr/> elements, use <tbody/> element.

while ($row = $korisnici->fetch_assoc()){
    echo "<tbody>";
    echo "<tr><td><b>" . $row['prezime_ime'] . "</b></td></tr>";
    echo "<tr><td>Kućni broj: <b>" . $row['kbr'] . "</b></td></tr>";
    echo "<tr><td>
        <label>Broj plinomjera: </label><input type='text' class='bb' size='8' disabled value = '". $row['bb'] ."'></td></tr>";
    echo "<tr><td>Zadnje očitanje: " . $row['staro_stanje'] . " m<sup>3</sup></td></tr>";
    echo "<tr><td>
        <label>Očitanje: </label>
        <input class='ocitanje' type='text' size='6' value='". $row['ocitanje'] ."'> m<sup>3</sup>
        <input type='button' value='Upisi' class='upisi'>
        </td></tr>";
    echo "</tbody>";
}

Now, in your JavaScript, let's attach an onclick event handler to the .upisi buttons.

$('.upisi').click(function () {
    // find bb and ocitange relative to the tbody
    var tbody = $(this).closest('tbody');
    // var bb = tbody.find('.bb').val(), ocitanje = tbody.find('.ocitanje').val();
    var bb = $('.bb', tbody).val(), ocitanje = $('.ocitanje', tbody).val();

    alert('bb: ' + bb + ', ocitanje: ' + ocitanje);

    if (ocitanje == '') {
        alert('Unesi očitanje!');
    } else {
        $.ajax({
            type: 'POST',
            url: 'ajaxjs.php',
            data: { bb1: bb, ocitanje1: ocitanje }, // cleaner
            cache: false,
            success: function (html) {
                // Returns successful data submission message when the entered information is stored in database.
                alert(html);
            }
        });
    }
});

Last, you should always check whether you have the POSTed fields before using them.

<?php
require("spajanje.php");
if (isset($_POST['bb1'], $_POST['ocitanje1'])) {
    $bb2 = $_POST['bb1'];
    $ocitanje2 = $_POST['ocitanje1'];
    if (mysqli_query($conn, "UPDATE ocitanja SET ocitanje='$ocitanje2' WHERE bb='$bb2'")) {
        echo "Uspješno upisano očitanje!";
    } else {
        echo "Something went wrong"; // I have no idea what language you are using, so I'm not even going to try
    }
}
?>

Side-remark: you should be using prepared statements in your PHP. As it is outside the scope of your problem, I have not included it.