Retrieve records and update MySQL table

Hi everyone,

I’m returning some records from a MySQL table. I’m looping out a name, then an empty text input (which will be used to update the name), and then a price. The SELECT query might return any number of names and prices. Now the problem at the moment is that I can enter new names into the form but only the last name I enter will be added to the table – replacing all of the names with the same name. Clearly, I don’t want this to happen. Each text input must only replace the name associated with it. Do I need to add some counter (

name=['name'][$i];

) ?

Could someone please help me?

Thank you!!

if ($num > 0) {
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
print'<p>'.$row['name'].'</p>
<input type="text" name="name" size="" maxlength="" value="';
if (isset($_POST['name'])) echo $_POST['name'];
print'"  /> </p>';

print'<p>'.$row['price'].'</p>';
}
}


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

$phpdummy = mysqli_real_escape_string($dbc, trim($_POST['name']));
$q = "UPDATE  table1 INNER JOIN table2 USING (some_id) SET  name='$phpdummy' WHERE user_id = {$_SESSION['user_id']}  ";	

Yes, that’s your problem - all your input text fields are called ‘name’. If you right-click the form when it’s displayed you’ll see that you just have a row of inputs all called the same thing. I’d probably do something like:


if ($num > 0) {
$cnt = 0;
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
$fldname = "name" . $cnt;
print '<p>'.$row['name'].'</p>
<input type="text" name="' . $fldname . '" size="" maxlength="" value="';
if (isset($_POST['name'])) echo $_POST['name'];
print'"  /> </p>';
print'<p>'.$row['price'].'</p>';
}
}

Not sure where your record ID is coming from but that’s why they’re all being set to the same thing - it must always use the last value for duplicate variable names, just like if you have duplicate column names in your query you’ll get the last value.

Hi droopsnoot,

thank you for the info! I tried the code but could not yet get it to work. How would I re-work this section?

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

$phpdummy = mysqli_real_escape_string($dbc, trim($_POST['name']));
$q = "UPDATE  table1 INNER JOIN table2 USING (some_id) SET  name='$phpdummy' WHERE user_id = {$_SESSION['user_id']}  ";  

Thanks

I don’t think you can, as your table doesn’t have enough information. You’ll need to have another field that contains the record id and also has an incrementing fieldname so you can find it. Then you’d probably need some kind of loop:


$process = 0;
while ($process < $cnt) {
   $q = "update tablename set name = " .$_POST['name' . $process] . " where recordid = " . $_POST['id' . $process];
   // execute the query, not sure of the mysqli syntax
   $process += 1;
   }

You’d maybe want to store the original value somewhere and only do the update if the name has changed. Obviously I don’t know your record structure so I don’t know whether the query would be more complex than this. And I have done any data checking which you would have to do, either with a prepared statement or escape_string or whatever.

Hi droopsnoot,

thanks a lot for getting back to me. I appreciate the help.

The table I’m updating has an auto-incremented primary key (name_id) column and a (name) column. I’ll probably end up updating multiple tables and doing validation etc. For now I’m just trying to get it to work at all.

The code you provided looks helpful - will give it a try. Thanks

Hi guys,

I have not been able to make the updates function as intended. Is it feasbile to use a while loop to loop a query as in the code below?

$process = 0;
while ($process < $cnt) {
   $q = "update tablename set name = " .$_POST['name' . $process] . " where recordid = " . $_POST['id' . $process];
   // execute the query, not sure of the mysqli syntax
   $process += 1;
   }

Where does the

$_POST['id' . $process]

come from? Must I add an id=‘’‘’ to the text input and add the same type of code as in

name="' . $fldname . '"

? And does the “where recordid” conditional refers to the auto-incremented primary key (name_id) column?

I really want this to work. Thank you in advance!

Yes, in your original output loop each line will have to have the id (primary key) as well as the name. Then you can loop through all the names, get the id field for the corresponding name field, and update the database. Probably didn’t help that my example above never incremented the field name counter suffix, but you probably noticed that, also I didn’t pass through the value of ‘count’ to do the second loop.


if ($num > 0) {
print '<form method="post" action="whatever.php">';
$cnt = 0;
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
  $fldname = "name" . $cnt;
  $idname = "id" . $cnt;
  print '<p>'.$row['name'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row['name_id'] .'>
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row['name'];  // this is the name from the query
  print'"  /> </p>';
  print'<p>'.$row['price'].'</p>';
  $cnt +=1;
  }
  print '<p><input type="hidden" name="count" value = "' . $cnt . '"><input type="submit"></p></form>';
}

so the above should give you a table that has the name, price and a hidden var for each field that contains the id, assuming your id is in $row[‘name_id’] in the table, something like:


<form method="post" action="whatever.php">
<p>Jet Black</p>
<input type="hidden" name="id0" value="1001">
<input type="text" name="name0" value="Jet Black">
</p>
<p>15.75</p>
<p>Dave Greenfield</p>
<input type="hidden" name="id1" value="1002">
<input type="text" name="name1" value="Dave Greenfield">
</p>
<p>18.95</p>
<input type="hidden" name="count" value="2">
<input type="submit">
</form>

As you see, it’s created fields with different names for each name entry (which it wouldn’t have in my first example code as I didn’t increment $cnt). Then to store the updated data, in your whatever.php:


$process = 0;
$cnt = $_POST['count'];
while ($process < $cnt) {
   $q = "update tablename set name = " .$_POST['name' . $process] . " where name_id = " . $_POST['id' . $process];
   // execute the query, not sure of the mysqli syntax
   $process += 1;
   }

So it will run around that loop twice, for values 0 and 1 in the example, retrieve name0 and id0, update the table, then retrieve name1 and id1, and update the table, then end the loop. As I mentioned, it would probably be nice to add a feature to only update if the name has changed.

Also obviously some sanitising of data is needed, I haven’t bothered with that in the example. Looking again at your first post, you seem to loop around a query but then try to display the ‘name’ field from $_POST, wouldn’t that be from $row instead? I have assumed so in this post though I didn’t notice it first time around.

Hi droopsnoot,

I must apologize for getting back to you only now. I have been unable to access my computer for the past few days and it might be another week before I can test your code. Thank you again for helping me! You really have gone out of your way to assist me with this issue!

Will post back after I’m able to try out the code. Once more, sorry for the late reply.

Hi droopsnoot,

so I’ve had an opportunity to test the code but somehow I cannot get it to work. Just to confirm, my name_id column consists of numbers from 1 upwards and the name column consists of names.

Could the problem be with the query?

$process = 0;
$cnt = $_POST['count'];
while ($process < $cnt) {
   $q = "update tablename set name = " .$_POST['name' . $process] . " where name_id = " . $_POST['id' . $process];
   // execute the query, not sure of the mysqli syntax
   $process += 1;
   }

I’m unsure of where

$_POST['id']

is coming from because there’s no id=“” in the form. Perhaps someone can figure this out since my PHP skills aren’t that great.

Thank you very much in advance!

There are two groups of variables in the form, one hidden one called ‘id’ and suffixed with the count variable, so they will be called ‘id0’, ‘id1’ and so on. The other is called ‘name’ and also suffixed with the count variable, so ‘name0’, ‘name1’. The ‘id’ fields equate to your column ‘name_id’, and the ‘name’ fields equate to your column ‘name’. Finally the form contains the value of the count, so that when posted, we know how many of each field there are.

In the second section of code, we retrieve the count variable, then do a loop with the value starting from zero in the variable $process, building a query each time. In the query, we set your column ‘name’ to be equal to $_POST[‘name’ . $process] where your column ‘name_id’ is equal to $_POST[‘id’ . $process]. So in the first loop that query will read:


update tablename set name='Jet Black' where name_id = '1001'

and the second time around the loop, it will read:


update tablename set name='Dave Greenfield' where name_id = '1002'

When you say “cannot get it to work”, precisely what is happening? Do you get error messages, or does nothing happen? Have you added in the bit where you actually execute the query, as I left it out above as I can’t remember the syntax for mysqli query execution. If none of this helps, could you post your complete code for the page that draws the table, and the page that updates the database?

Hi droopsnoot,

thank you for your continued support. I didn’t expect to get such a fast reply!

Ok, here is my code:




$q = "SELECT name_id, name FROM testupdate  ";
    $r = @mysqli_query($dbc, $q);

    $num = mysqli_num_rows($r);
if ($num > 0) {
print '<form method="post" action="">';
$cnt = 0;
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
  $fldname = "name" . $cnt;
  $idname = "id" . $cnt;

  print '<p>'.$row['name'].'</p>
  <input type="hidden" name="' . $idname . '" value="' . $row['name_id'] .'">
  <input type="text" name="' . $fldname . '" size="" maxlength="" value="';
  echo $row['name'];  // this is the name from the query
  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'];
while ($process < $cnt) {
   $q = "UPDATE testupdate SET name={$_POST['name' . $process]} WHERE name_id = {$_POST['id' . $process]}   ";	

   $process += 1;
   }



	
			$r = @mysqli_query($dbc, $q);
			
			if (mysqli_affected_rows($dbc) == 1) {}

}


I don’t receive any error codes, but nothing is happening. I have done updates before, which is why I can’t understand why the query doesn’t work.

My intention was that you’d run the query statement within the loop, you have after the loop. It needs to be run once for each value/id pair.


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

That said, it should have run once, but only for the last entry in the table.

Also note, the @ symbol will suppress any error messages, so whil you’re developing / learning, it might be worth leaving it out.

No, it’s still not working.

Adding

$r = mysqli_query($dbc, $q); 

to the while loop hasn’t made any differemce.

OK, instead of that line, add:


echo $q;

and post the results, let’s see what the query is.

One thing just occurred to me - I use PDO and the prepare() and bindColumn() functions to put parameters into queries, and that means I don’t have to worry about quotes. If the queries look like they might be OK - try one in phpmyadmin to see if you get an error - it might be a quotes-related thing. The more I think about it, the more I imagine that’s it, especially if the names are multiple words split by spaces.

Hallelujah, it’s finally working. Echoing out the query proved to be useful since it showed that there weren’t any quotation marks surrounding

{$_POST['name' . $process]}

and

 {$_POST['id' . $process]}

Perhaps I should echo out my queries more often…

Thanks a lot for sticking around and helping me with this problem! :slight_smile:

No problem, glad it’s OK now.