[SOLVED] Form Arrays and a Database

[RestlessThoughts]

This is a repost from this topic. Thanks to Mchl I've weeded out what I believe to be the problem, and figured it'd be best to post a new topic so others wouldn't have to go through the whole thread.

 

I am using a modified code to draw out individual values from a textarea using foreach and explode, that I got from here. This works fine, except it updates every row with the same data.

 

The problem is it's not reading the ID of the rows. I have a seperate ID for each box in a hidden field in the form. Printing out the query shows me it's 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 another foreach loop in there, it goes through the IDs fine, but ends up going through all the IDs for each row and hence updating every row with every result, ending with every row having the last result. How annoying.

 

This is the relating form 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 I can't get to update properly.

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 `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 `ID` = '{$classID}'";

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

 

The class updates fine with the id using a while loop, but I can't get a while loop to work correctly with the foreach loop (causes it to repeat a lot). Help, please?

[lordphate]

Can you paste your entire function, i don't see where the {$classID} is set, or how

[RestlessThoughts]

Sorry! Yes of course.

 

//Update Class name.

$size = count($_POST['class']);

// start a loop in order to update each record.
$i = 0;
while ($i < $size) {
// define each variable.
$class= $_POST['class'][$i];
$classID = $_POST['classID'][$i];
$results = $_POST['results'];

$query = "UPDATE `database`.`table` SET `Class`='$class' WHERE `ID` = '$classID' LIMIT 1";

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


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 `database`.`sametableasbefore` 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 `ID` = '{$classID}'";

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

[RestlessThoughts]

This is a bump, cause I'm still stumped.

[RussellReal]

question 1: did you name every field in your form results[] otherwise your $line variable is probably just 1 lkine long from 1 result (results) and thats why its sending the same data..

 

link me to the page and I'll try and assist you

[RestlessThoughts]

Thanks but I'm sure that's not the problem, every field is a properly named array as that part of the form code is in a while loop. I've figured out how to print the queries out properly (go me ) so I know the results array is working correctly. The problem seems to be that it's inserting every result into the same ID row. I can't get the ID array to advance properly.

 

These are the query readouts for Show 16:

 

UPDATE `table` SET `Champion`='Quine', `Reserve Champion`='Steve', `1st`='Bob', `2nd`='Frank', `3rd`='Francies', `4th`='', `5th`='', `6th`='', `7th`='', `8th`='', `9th`='', `10th`='' WHERE `ID` = '404' LIMIT 1

UPDATE `table` SET `Champion`='Filly', `Reserve Champion`='Tester', `1st`='Test', `2nd`='Testing', `3rd`='OMG', `4th`='', `5th`='', `6th`='', `7th`='', `8th`='', `9th`='', `10th`='' WHERE `ID` = '404' LIMIT 1

UPDATE `table` SET `Champion`='Freaking', `Reserve Champion`='Animals', `1st`='Dog', `2nd`='Cat', `3rd`='Meow', `4th`='', `5th`='', `6th`='', `7th`='', `8th`='', `9th`='', `10th`='' WHERE `ID` = '404' LIMIT 1

 

And this is my live testing site: clicky

 

I'd really appericate any help I can get in working this snag out. :-\

[sasa]

try

//form part
<?php
$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=\"text\" size=\"60\" name=\"class[".$in['ID']."]\" value=\"".$in['Class']."\"></td></tr><TR><TD>";
echo "<textarea input type=\"text\" name=\"results[".$in['ID']."]\" rows=10 cols=60></textarea></td></tr>";
}
echo "<TR><TD>";
?>

//update part
<?php
foreach ($results as $classID => $line) {

$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 `ID` = '{$classID}'";

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

[RestlessThoughts]

Hey cool, that almost worked!

 

Queries now read as follows:

UPDATE `table` SET `Champion`='Quine', `Reserve Champion`='Steve', `1st`='Bob', `2nd`='Frank', `3rd`='Francies', `4th`='', `5th`='', `6th`='', `7th`='', `8th`='', `9th`='', `10th`='' WHERE `ID` = '0'

UPDATE `table` SET `Champion`='Filly', `Reserve Champion`='Tester', `1st`='Test', `2nd`='Testing', `3rd`='OMG', `4th`='', `5th`='', `6th`='', `7th`='', `8th`='', `9th`='', `10th`='' WHERE `ID` = '1'

UPDATE `table` SET `Champion`='Freaking', `Reserve Champion`='Animals', `1st`='Dog', `2nd`='Cat', `3rd`='Meow', `4th`='', `5th`='', `6th`='', `7th`='', `8th`='', `9th`='', `10th`='' WHERE `ID` = '2'

 

 

It's advancing the ID array without repeating everything 100x, yay! Now to only get it to read the proper ID, haha. Thank you for your help Sasa. Might you have any more gems to share?

[sasa]

are you chahge form part too?

[RestlessThoughts]

Oh my that was stupid of me. No I had not, and now it WORKS!

 

My god you're a genius Sasa! Thank you sooo very much! That was a simple and elegant solution and it works amazingly now, thankyou thankyou thankyou!