Update multiple records using foreach (or while?)

[Yikes]

Hi, I am trying to make an admin page for the admin to update information for users. Eventually, I want to be able to update the SAME field (like, "hair color" -- just an example) for each user at one time on one screen. The code I am posting here is a little different. This code actually draws up all of the fields for all of the users at once. Then you have the choice of whether to update a field. (In this code, since I am trying to get it working, there is only one item being written into the database.)

The trouble is: instead of writing something new into the database, it just erases the old information. So... you could say the UPDATE is working, but it is not putting the new information in there.

Actually, I was thinking the foreach part was working, but that the variable wasn't being assigned correctly.

Finally, if you see an easier way to do this using some more efficient code, suggestions are welcome.

(Sorry for the formatting in the code below. Hope you can see the meat of it without trouble.)

<?php

if(isset($_POST['submit'])) {
        foreach($_POST['changed'] as $index_id) {
        $title = $_POST['this_title_$index_id'];
        $query = ("UPDATE user_info SET this_title='$title' WHERE index_id='$index_id'");
        $now = mysql_query($query);
            }
    }



$mysql = mysql_query("select * from user_info");

echo "<table><form method=post action=". $_SERVER['PHP_SELF'] . " name=updateform>";

while($data = mysql_fetch_assoc($mysql)) {

echo "<tr><td colspan=2>Record" . $data['index_id'] . "</td></tr>";

//using foreach, list all the data that was returned in the $data array from mysql_fetch_assoc
foreach($data as $key => $value) {

echo "<tr><td bgcolor=yellow>" . $key . "</td><td><input name=" . $key . "_" . $data[index_id] . " type=text></td></tr>";
}

echo "<tr><td colspan=2 bgcolor=C0C0C0>Update this record? <input type=checkbox value=" . $data[index_id] ." name=changed[]></td></tr>";

}


echo "<tr><td colspan=2><input type=submit name=submit value=Submit></td></tr></form></table>";


?> 

[eoghain]

So I looked at your code and I see a couple of issues.

$title = $_POST['this_title_$index_id'];

This line probably won't work the way you expect it to. It should probably be this:

$key = 'this_title_' . $index_id;
$title = $_POST[$key];

PHP doesn't expand variables inside single quotes for strings, while I've never tested it in this exact circumstance I assume that it's the same even if the string is inside an array key.

Also from looking at the rest of your code I don't see a form item named 'this_title_x'. Don't you want to be using 'key_x'?

[Yikes]

Thanks! I was thinking it was the variable, and had tried it a few different ways, but hadn't gotten it. Many thanks, even if it didn't take much of your time, because it saved a WHOLE lot of mine!

(OK... maybe I shouldn't push it... but my next step is to add all of the fields in the row so that any or all of them can be updated. On top of that, I don't want records that are not updated to be replaced by blanks.... I'll get to work on it, but if anyone wants to throw in some suggestions, I am more than pleased to get the help.)

[Yikes]

Maybe I should just show you what I mean. Right now, I can update everything by including distinct code for each field, such as

$key = 'this_title_' . $index_id; 
$title = $_POST[$key];

$key = 'this_author_' . $index_id; 
$author = $_POST[$key];

But instead of having that code for each field, I want to just cycle through them.

Also, on the topic of not replacing fields with blanks if nothing new is typed into the text box, I just used the data from the database as the "value" of each box. One odd thing, though, is that when I have more than one word, only the first word is re-entered into the table. I mean, if I want to update the title as "This is the title," only "This" gets put into the database.

That's where I'm at! Thanks again (and in advance) for your help.

[Weedpacket]

Originally posted by Yikes
Maybe I should just show you what I mean. Right now, I can update everything by including distinct code for each field, such as

$key = 'this_title_' . $index_id; 
$title = $_POST[$key];

$key = 'this_author_' . $index_id; 
$author = $_POST[$key];

But instead of having that code for each field, I want to just cycle through them. [/b]

You can make an array of field names ('this_title', 'this_author', or whatever) then loop through that.

[Yikes]

Thanks Weedpacket. Here's what I have done so far... (the table column names are different now)...

if(isset($_POST['submit'])) { 

    foreach($_POST['changed'] as $fields => $index_id) { 

$fields = array('writings_title', 'writings_author', 'writings_rating');


$count = 0;

while ($count < 3 ) { //counting thru 3 fields in array.  Easier way to do this?

$key = $fields[$count] . "_" . $index_id; 

$value = $_POST[$key];

$explode = explode("_", $key); //so I can build the actual column name, as it is almost the same as key

$field = $explode[0] . '_' . $explode[1]; //assembling column name

    $query = ("UPDATE class_writings SET " . $field . "='$value' WHERE index_id='$index_id'"); 
    $now = mysql_query($query); 

$count++;
       }
     } 
   } 

This seems to be working, with one exception: I am unable to update the very first record in the database. The others work fine, but the first one never updates. I know this is going to be an obvious (after seeing it) error, but... it's hard to find things under your own nose.

As alays, I appreciate the help, and I am so glad so many people volunteer their time, energy, and resources to newbies like myself. Hope to return the favor someday!

-Yikes

[Weedpacket]

Well, I fiddled around with your code a bit. Nothing leaps out and says "I'm broken!".... Oh, wait a bit - you're clobbering the $fields value you got from $POST['changed'] by your array called $fields. That could do it. Lessee, rename the latter to $db_fields (I think I renamed the right $fieldss). But then it looked like you weren't using $POST['changed'][$fields] at all. So I took out the "$fields=>" bit. Then I changed $db_fields back into $fields. Never even attempted to test this, so can't say if it even approximates working properly.

if(isset($_POST['submit'])) { 
	$fields = array('writings_title', 'writings_author', 'writings_rating');
	foreach($_POST['changed'] as $index_id) { 
		foreach($fields as $field) {
			$key = $field . "_" . $index_id; 
			$value = $_POST[$key];
			$query = ("UPDATE class_writings SET " . $field . "='$value' WHERE index_id='$index_id'"); 
			$now = mysql_query($query); 
		}
	} 
}

[Yikes]

Yeah, I know my code is a bit... uhm... well...

Anyway, your streamlined code works -- but it works the same as mine. The first record doesn't get updated.

In my actual page, I added some lines to echo the values of $key and $value, and it shows that variables carry to all records except the very first record in the database.

This makes me wonder if the problem is actually something related to the database (a mySQL / PHP communication idiosyncrasy?).

By the way, thanks! I have to admit, I have been trying to get a hold on foreach for some time, but can't find ANY good tutorials for it. Or maybe I "array-challenged."

I'll see if I can look up any more info that might be related to this...

[Yikes]

For whatever reason -- I changed NOTHING -- it is working now... (Gee, what if I try it again in 3 minutes ~ :queasy: )

Anyway -- matter solved! Thanks again!