UPDATING multiple data using while loop SQL

Question

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

 ?>

"the problem is only one data is updating."... ok so 1) all your textboxes will have the same "room_id" name, you're just going to be sending back 3 variables with the same name, so the server can't tell the difference between them. See this post for a solution: https://stackoverflow.com/questions/7880619/multiple-inputs-with-same-name-through-post-in-php 2) where's your PHP code for doing the updates? We need to check there isn't a problem in there too. — ADyson, Jun 12 '18 at 19:34
ok so follow the solution in my first link to allow submitting multiple values of the same name. Then in the PHP you can loop through the "room_id" POST variable (since it will now be an array) and execute one UPDATE query for each item submitted. P.S. Your code is currently vulnerable to SQL injection attacks - a malicious user could enter values which could allow them to steal, corrupt or delete your data. See http://bobby-tables.com/ for an explanation and also some examples of writing queries safely using PHP / mysqli. — ADyson, Jun 12 '18 at 19:42
sir @ADyson I don't really get the point in the link, please help me out of this problem. :(( — pacificskybeachresort, Jun 12 '18 at 19:43
And your app should never log in to MySQL as "root" - root can do whatever it likes, including deleting all the databases et etc. Instead create a separate SQL login account for the application which has only the privileges it actually needs in order to work. Combined with the SQL injection vulnerability, this leaves your database an open book for hackers to mess with — ADyson, Jun 12 '18 at 19:43
Okay thankyou sir for additional info, wait sir I'll try first. — pacificskybeachresort, Jun 12 '18 at 19:44
sir @ADyson how can I declare the variable in my server? $roomid = $_POST[]; like this? — pacificskybeachresort, Jun 12 '18 at 19:45
The point (from the accepted answer in that question) is that your textbox would need to be declared as `name="room_id[]"` so that when you print 3 of them it can be submitted as a list of values. Then in the PHP you still do `$roomid = $_POST['roomid'];` but `$roomid` is now an array so you can loop over it with `for` or `foreach`. Within the loop you can get each of the separate submitted values, and run a SQL query to update that row. — ADyson, Jun 12 '18 at 19:47
error: array to string conversion :((( i don't know what to do :(( — pacificskybeachresort, Jun 12 '18 at 19:52
at some point you've tried to convert an array to a string. Did you do something like `$roomid[0]` to get a single value from the $roomid array? Or if you're in a for loop it might be like `$roomid[$i]` where $i is the loop counter. I feel like maybe you are missing some basic PHP knowledge about arrays here which might benefit from a little more studying. — ADyson, Jun 12 '18 at 19:57

score 3 · Accepted Answer · answered Jun 12 '18 at 19:52

3

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}"])

answered Jun 12 '18 at 19:52

public satanic void

450
4
12

Thankyou so much sir! But the problem is, still not working. What should I do in to my sampleserver.php `` LIKE THAT? – pacificskybeachresort Jun 12 '18 at 20:04
sir, how can I declare the id using jquery? Please sir help me out of this problem :(((( – pacificskybeachresort Jun 12 '18 at 20:08
@pacificskybeachresort Assuming that you want to subtract 1 from the capacity of every room in the input field, yes. – public satanic void Jun 12 '18 at 20:08
If you want to do that via an AJAX POST request to sampleserver.php, you would do pretty much the same thing except in JavaScript: – public satanic void Jun 12 '18 at 20:09
`dataString = ''; for(var i = 1; document.getElementById("room_" + i).value != null; i++) { dataString += 'room_' + i + document.getElementById("room_" + i).value; }` – public satanic void Jun 12 '18 at 20:10
it's not working sir :( even in the first code :( it's still not working :(( – pacificskybeachresort Jun 12 '18 at 20:19
1

@plato to make it easier if the textbox and button were wrapped in a form tag (as they conventionally should), and the "submit" event handled using a jQuery handler, then the the ajax could simply do `data: $(this).serialize()` to get all the fields, much neater and easier – ADyson Jun 12 '18 at 20:21
sir @ADyson please how can I correct the code? I don't know what to do. pleaseplease – pacificskybeachresort Jun 12 '18 at 20:34
@pacificskybeachresort well first explain your exact problem. What precisely is "not working"? What debugging have you done? If you don't know how to debug javascript or PHP, now would be a good time to learn. – ADyson Jun 12 '18 at 21:02
@ADyson true, hadn't thought about that. Serializing would definitely be a better option. – public satanic void Jun 13 '18 at 23:11

UPDATING multiple data using while loop SQL

1 Answers1