Trouble updating multiple rows

PHP
1

Hi everyone,

I am trying to update multiple rows and have run into some difficulties. This code is from:

What I am doing is printing out the records of registered users. Each user has a certain number of computer stores, each store containing a number of computers with a few characteristics. So user1 might have 2 computer stores, the first store has 2 computers, the second store has one computer. In addition each store has information such as opening times and the store’s location.

I’m using separate forms to update each store’s information.

The updates work when I have one form input that loops multiple times. With only one form input the source code for the inputs will be:

<input type=“hidden” name=“id0” value=“1001”>
<input type=“text” name=“name0” value=“Jet Black”>
<input type=“hidden” name=“id1” value=“1002”>
<input type=“text” name=“name1” value=“Dave Greenfield”>

But with multiple form inputs in the loop I have inputs with the same name= number. For example, there could be 2 inputs that both have “id0” and “name0”. This messes up the update query. This is probably because I don’t know where to put $cnt +=1;

In addition:
Is it possible to only update the form inputs that have been filled in? If there are 7 inputs, the code will update all of them even if I only wish to update 1 input. This is most likely because of the $cnt variable. Also, is it possible to have only one update in the query instead of the update looping multiple times?

Please let me know if something isn’t clear. Thank you in advance!

This is how the records for user1 might look like. Beneath each title will be a form input for the user to update the information

Jack’s computer store
Opening times
location

Computer 1
speed
price
memory

Computer 2
speed
price
memory

Amy’s computer store
Opening times
location

Computer 5
speed
price
memory

<?php

$q = "SELECT ... WHERE user_id={$_SESSION['user_id']} ";
$r = @mysqli_query ($dbc, $q); 	
$num = mysqli_num_rows($r);

if ($num > 0) {
$cnt = 0;



// loop #1
// loop out store names, opening times, location

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
$fldname = "name" . $cnt;
$idname = "id" . $cnt;	

print '<form method="post" action="">';	
	
print '<p>'.$row['store_name'].'</p>
<p>store information</p>';

print'<p>opening times: '.$row['opening_times'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row['store_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row['opening_times'];
  print'"  /> ';

print'<p>location: '.$row['location'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row['store_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row['location'];
  print'"  /> ';



$storename = $row['store_name'];


$q = "SELECT  ... WHERE user_id={$_SESSION['user_id']} AND store_name  = '$storename'  ";
$r2 = @mysqli_query ($dbc, $q); 	
$num = mysqli_num_rows($r2);
if ($num > 0) {
$cnt = 0;

// loop #2
// looping out each store's computer characteristics

while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {
$fldname = "name" . $cnt;
$idname = "id" . $cnt;	
	
	
print'<p>'.$row2['computer_name'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row2['computer_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row2['computer_name'];
  print'"  /> ';

print'<p>'.$row2['speed'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row2['computer_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row2['speed'];
  print'"  /> ';

print'<p>'.$row2['price'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row2['computer_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row2['price'];
  print'"  /> ';

print'<p>'.$row2['memory'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row2['computer_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row2['memory'];
  print'"  /> ';




$cnt +=1;
}
print '<p><input type="hidden" name="count" value = "' . $cnt . '"><input type="submit"></p></form>';
}
}
}


if ($_SERVER['REQUEST_METHOD'] == 'POST') {

$process = 0;
$cnt = $_POST['count'];
echo $cnt;
while ($process < $cnt) {
   $q = "UPDATE computer SET computer_name='{$_POST['name' . $process]}' WHERE computer_id = '{$_POST['id' . $process]}'   ";
   $r = @mysqli_query($dbc, $q);
   echo $q;
   $process += 1;
   }
}






?>
2

So you want to list all stores, and within each store you’ll allow changes to opening times and location, then under each store you’ll list each computer they stock, and allow each one to be updated? You could do it the way you are doing, but (to me, and it’s a personal thing) you probably wouldn’t want to. Looking at your code there, you are using the $cnt count variable in the stores loop only, so each input field for the computers within each store are the same. You won’t be able to differentiate between each computer for each store that way, also in the loop that outputs the computers list per store, you’re using the same field names in each line, and also the same field names as you do for the store information. You probably need something like this - it’s pseudo-code, not actual PHP:


$store = 0;
'select * from stores'
for each store  {
   $store += 1; // Unique number for each store
   $storeid = $row['store_id'] // link the tables using the ID, not the name of the store. Name of the store shouldn't be in the computers table, only the ID.
   // display the details for the store, name, opening times and location. Field names for the opening time and location form entries would be something like "open" . $store
   // and "location" . $store so they are unique in the form.
   'select * from computers where store_id = ' . $storeid;
   $storecomp =  0;
   for each computer {
      $storecomp +=1; // use this to make the field names, along with the store number.
      // display the computer details. Form input names need to contain the store number AND the computer number, say "store" . $store . "speed" . $storecomp would
      // give you "store1speed1" for the first one, "store1speed2" for the next, then "store2speed1" and so on.
      } // end of for-loop for each computer within each store
   } // end of for-loop for each store

The problem with that is it gets quite complicated quickly. As I type out that pseudo-code, I am thinking you’d need an array to keep the number of computers per store to use in the loop when you have submitted the data. You’ve also got these complicated form input names because we have to split out the store id and the computer id. It’s do-able, but not nice. And think about what a horrible page that will be when you have 100 stores and they each have 200 computers.

I would think it would be more normal to have a search page (or even a simple input table) that would allow you to select the store. Once the user selects the store, you display the details for that store only, so show the store information and list the computers underneath that. But instead of listing them all in a big form and having to separate them out, just have a button or link to “edit” either the store details, or each of the computers details, individually. So you’d link to another separate php routine that gets the computer ID and store ID passed into it, retrieves only that row from the computers table, displays it in a form, user changes what they need to, submit those changes and back to the store display again and display the list.

Or if you’re set on having one single screen, consider a separate form for each section of data. So you’d have one form that updates the store details for store 1, a separate form for computer 1 in store 1, a separate form for computer 2 in store 1, and so on. That way you could stick with input names because they’d all be individual forms, and you’d have a ‘submit’ button for each form so would automatically only be updating that one individual piece of data. Using a single form, the only ways I could see you knowing which had been modified would be either to compare the submitted data to the original, or to have some kind of javascript to set a flag when the field is changed.

I know, not much help, but maybe things to consider.

3

Hi RedBishop,

I’m inclined to agree with droopsnoot, it would be more manageable to allow users to update one store at a time with separate screens.

If for some reason you absolutely must use one big form, you can group the data into arrays like this:

<input name="store[<?php echo $store_id ?>][computer][<?php echo $computer_id ?>][memory]" type="text" value="4GB">

which would give you a $_POST array like this:


Array
(
    [store] => Array
        (
            [7] => Array
                (
                    [computer] => Array
                        (
                            [1] => Array
                                (
                                    [memory] => 4GB
                                )
                        )
                )
        )
)
4

Hi droopsnoot,

thank you for the information and for getting back to me on this. I’m surprised that you replied so quickly to my thread.

I would think it would be more normal to have a search page (or even a simple input table) that would allow you to select the store. Once the user selects the store, you display the details for that store only, so show the store information and list the computers underneath that. But instead of listing them all in a big form and having to separate them out, just have a button or link to “edit” either the store details, or each of the computers details, individually

I’m not sure what you mean by search page. An input table would be a “pull-down” menu? Perhaps I can try this out. One large form might also be problematic for users.

I know, not much help, but maybe things to consider.

No, you have helped! Thank you. I’ll let you know how things turn out.

@fretburner,

thank you for advising me on this.

5

Yes, ‘table’ there was a bit spurious, I just meant some way of selecting the store to maintain, then deal just with that store and its contents, so either a drop-down, or a search on name or location, or whatever.

6

I would think it would be more normal to have a search page (or even a simple input table) that would allow you to select the store. Once the user selects the store, you display the details for that store only, so show the store information and list the computers underneath that. But instead of listing them all in a big form and having to separate them out, just have a button or link to “edit” either the store details, or each of the computers details, individually

Hi droopsnoot,

I followed your advice as per the above quote and the updates are working well. No need for hidden fields or counters and a lot simpler.

Thought I’d let you know, and thanks again for your assistance.

7

Excellent, thanks for the update.