Updating Database from Form Arrays

[RestlessThoughts]

Hello,

 

I guess can't quite understand how to properly use arrays. I have a form that generates a different number of fields depending on what's being updated. Specifically here, show results.There's a class/title box then a textarea of winning names, which are split on the line break to be inserted into the database.

 

My problem is every title and textarea is transformed into the very last one, rather than each title and box keeping their own info.

 

This is part of the form that relates to the code:

$stuff = mysql_query("SELECT * FROM `database`.`table` WHERE ShowID=$info") or die(mysql_error());
while ($in = mysql_fetch_assoc($stuff)) {
echo "<TR><TD><input type=\"hidden\" name=\"classID[]\" value=\"".$in['ID']."\"><input type=\"text\" size=\"60\" name=\"class[]\" value=\"".$in['Class']."\"></td></tr><TR><TD>";
echo "<textarea input type=\"text\" name=\"results[]\" rows=10 cols=60>";
}echo "</textarea></td></tr><TR><TD>";

 

And this is the code that's causing me headaches:

foreach($_POST['class'] as $k => $v) {
      // update name in the table  
      $sql = "UPDATE `database`.`table` SET `Class`='$v' ";
}    // end foreach

foreach($_POST['classID'] as $ka => $va) {

$sql .= "WHERE `ID`='$va' LIMIT 1";


      $resultc = mysql_query($sql);
if (!$resultc) {
     echo "<p>Query: $sql</p>";
     echo "<p>MySQL Error: " . mysql_error() . "</p>";
}
}

//As an aside, this doesn't seem to work either, but formating the data is of less concern to me than actually being able to update it!
$vowels = array("RCH:", "CH:", "RCH ", "CH ", "Ch ", "Rch ", "Top Ten", "Champion ", "Res Champion", "Ch:", "Rch:", ":", ".", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9");
$results = str_replace($vowels, NULL, $results);


//I partly modified this code from a post on this message board, it works great when inserting.
//But my use here must be flawed as the last enty ends up replacing everything else.
foreach ($results as $line) {
$insertLine = explode("\n", $line);
for ($i = 0; $i < count($insertLine); $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i]))));

$queryc = "UPDATE `database`.`table` SET `ShowID`='$ShowID', `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = {$classID[$b]}";

$queryc = substr($queryc, 0, -2);
     $resultc = mysql_query($queryc);

if (!$resultc) {
     echo "<p>Query: $queryc</p>";
     echo "<p>MySQL Error: " . mysql_error() . "</p>";
}

echo "Show classes updated.";

 

I get an error currently because the foreach on the Class part causes the 'WHERE...' to tack on twice instead of once. Otherwise the code goes through error free but as I said replaces everything with the last class name & results box instead of keeping stuff seperate.

 

Any help in getting these arrays to properly update would be fabulous!

[Mchl]

You can't use UPDATE to update several records with different data. You will need a separate query for each record.

[RestlessThoughts]

Thanks! That pointed me toward the right terms to search for and this site gave me my answer:

http://www.theblog.ca/update-multiple-rows-mysql

 

My script works now Thank you again!

[RestlessThoughts]

Oh nope, only got part of it working. :-X

 

The results part is defeating me. Still replacing every results box with the last entered, even with a counter and while loop.

 

$insertLine = explode("\n", $line);
for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i]))));

$query = "UPDATE `database.table` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = '{$classID}'";
}

mysql_query($query) or die ("Error in query: $query");

 

 

If anyone has any suggestions for making several results textareas update into a database, spreading out among columns as above and for mulitple rows, I'd love to hear it! This code works for inserting mulitple rows, so why not for updating ??? lol I'm stumped.

[Mchl]

The way you have it it runs query once only (and it is the last query generated). To run query for each item, you must move mysql_query onto the loop.

 

 

$insertLine = explode("\n", $line);
for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i]))));

$query = "UPDATE `database.table` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = '{$classID}'";
mysql_query($query) or die ("Error in query: $query");
}

 

 

[RestlessThoughts]

Thanks anyway, but that does something weird.

 

It puts the last box in the first place and doesn't change any of the other results. It may be doing something else, I'm not totally sure. Seems to be defying my logic. I'm about at my wit's end with this script.

 

I did leave out the first bit of code by accident. This is the full part of what's not working but should (and does when it's just an insert).

 

$results = $_POST['results'];
foreach ($results as $line) {

$insertLine = explode("\n", $line);
for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i]))));

$query = "UPDATE `table` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = '{$classID}'";
}
mysql_query($query) or die ("Error in query: $query");

 

Darn it, I got everything else working but this tiny code makes everything come crashing down, deleting info willy nilly!

[Mchl]

Sorry... maybe that's my fault... Been working all night and might misunderstood you... Anyway I'm  off to get somesleep now...

[RestlessThoughts]

Thanks for your help though, got me on the right track with most of the script

 

Hope you have/had a good night!

[Mchl]

More like a short nap in the morning, but much needed nonetheless.

[RestlessThoughts]

Teehee, yeah long nights staring at code, who needs real sleep after that especially when there's more code waiting!

 

In other news...I still haven't figured out how to fix my own bloody script.

[Mchl]

Ok, let's have another go then.

 

Could you post here `table` table structure?

[RestlessThoughts]

Teehee, right-o Thanks for wanting to help some more.

 

Class table I'm trying to insert the data into:

ShowIDID (which is auto-increment)ClassChampionReserve Champion1stall the way to 10th

 

It inserts fine into the columns that I want it to, just only one row is updated and usually the wrong one haha.

 

Right now it's set up so the Class is updated before Champion-10th, and the Class updates correctly using the ID and the while loop from the link above. Can't get the rest to work, though it's updating from a textarea with an explode... maybe that's why. I think if I put everything into it's own input text box instead of all together into a textarea it'd probably work, but wouldn't it be as easy to update then.

[Mchl]

Ok... how does data in $_POST['results']; look like?

 

[RestlessThoughts]

Sorry I didn't notice the table info wasn't easy to read.

-----------------------------------------------------------------------------------------------------------

|ShowID | ID (which is auto-increment) | Class | Champion | Reserve Champion | 1st | ..all the way to 10th |

-----------------------------------------------------------------------------------------------------------

 

Anyway this is a screenshot of how the form looks.

 

This can go on for a two hundred text boxes.

 

The array prints out like this: [ 0 ] => Name Name Name [1] => Name Name Name [2] => Name Name Name

And it's exploded using the perviously posted code. This works fine.

 

Right now it updates the last box only. I have a seperate ID for each box in a hidden field in the form. Printing out the query shows me it's apparently getting only the one ID number for the WHERE clause. It's not looping through the ID numbers properly and I'm not sure how to make it. If I stick a foreach loop in there, it goes through the IDs fine, but ends up going through all the IDs for each field and hence updating every field with the last result. How annoying. :-\