CODEWITHSUNDEEP
phpmysqli
pacificskybeachresort
pacificskybeachresort

Reputation: 27

UPDATING multiple data using while loop SQL

I want to update all data's inside in the while loop. But the problem is only one data is updating. For example:

Imagine that I have 3 data's in my tbl_rooms

columns: id, roomname, capacity

and the data will be fetch in the while loop, then all textboxes will be thrice cause of the while loop. Then I want to update all data's inside of the while loop. Please I need you guys. Thankyou!

Here's the code: 

$db = mysqli_connect('localhost', 'root', '', 'psbr'); 
$query = mysqli_query($db, "SELECT * FROM rooms")
<script src = "http://code.jquery.com/jquery-1.9.1.js"></script>

  <?php while ($row = mysqli_fetch_array($query)) { ?>
  <input type="text" name="room_id" id="room_id" value="<?php echo $row['id']; ?>">
  <?php } ?>
  <input type="submit" name="submit" onclick="return chk()">

<script type="text/javascript">

function chk()
{
var roomid = document.getElementById('room_id').value;
var dataString = 'roomid='+ roomid;
$.ajax({
  type: "post",
  url: "sample_server.php",
  data:dataString,
  cache:false,
  success: function(html){
    alert("success!");
  }
});
return false;
}


</script>

//sample_server.php

<?php 
$db = mysqli_connect("localhost", "root", "", "psbr");

$roomid = $_POST['roomid'];

    $update_status = "UPDATE rooms SET capacity = capacity - 1 WHERE id = '$roomid'";
    mysqli_query($db, $update_status);

 ?>

Upvotes: 0

Views: 1438

Answers (1)

public satanic void
public satanic void

Reputation: 460

You are displaying multiple input fields with the same name, and as such PHP has no idea which input field corresponds with which room ID. Your current code will output something like this:

<input type="text" name="room_id" id="room_id" value="1"></input>
<input type="text" name="room_id" id="room_id" value="2"></input>
<input type="text" name="room_id" id="room_id" value="3"></input>

So if you submitted the form to PHP, it would receive this:

$_POST = array(
    'room_id' => 1,
    'room_id' => 2,
    'room_id' => 3
)

or maybe

$_POST = array(
    'room_id' => 3
)

Either way, $_POST["room_{$id}"] will be null, as all your data is stored under the same name.

What you need to do instead is provide a unique name and id attribute to every field, like this:

while($row = mysqli_fetch_assoc($query)) { ?>
    <input type="text" name="room_<?php echo $row['id'] ?>" id="room_<?php echo $row['id'] ?>" value="<?php echo $row['id'] ?>"></input>
<?php } ?>

This will output:

<input type="text" name="room_1" id="room_1" value="1"></input>
<input type="text" name="room_2" id="room_2" value="2"></input>
<input type="text" name="room_3" id="room_3" value="3"></input>

Which means the your $_POST array will look like:

$_POST = array(
    'room_1' => 1,
    'room_2' => 2,
    'room_3' => 3
)

Then, you can retrieve the values from POST using the following for loop:

for($i = 1; isset($_POST["room_{$i}"]); $i++) {
    mysqli_query($db, "UPDATE rooms SET capacity = capacity - 1 WHERE id={$i}");
    // Or whatever you want to do
}

And I would recommend using checkboxes or something instead of text fields, if you are decrementing by a fixed amount, or number fields (but then you'll need to sanitize your user input with something like intval($_POST["room_{$i}"])

Upvotes: 3

Related Questions