Updating multiple SQL records using a single submit button

Question

Scenario: I have multiple text boxes in which a user will enter data into some of them / all of them / or none of them.

Goal: I need to be able to UPDATE multiple records based on what is in the text boxes where the users has entered their data.

Problem: The update statement is not working when I try to update each record for each text box.

Below is the code:

$conn = mysql_connect ($localhost, $user, $pass);
mysql_select_db($db_name, $conn) or die (mysql_error());

$myFile = "/var/www/html/JG/LSP/lsp_ref.txt";
$fh = fopen($myFile, 'r');
$theData = fread($fh, 5);
fclose($fh);


if (isset($_POST['submit'])) {
        foreach ($_POST['notes'] as $key=>$value) {
        echo $_POST['notes'][$key];
        #echo "<br/>";
        //echo "$key";
        //echo "<br/>";
                $query_update  = "UPDATE lsp_active SET Notes = ".$_POST['notes'][$key];
                $result_update = mysql_query($query_update);

        }
#header ('Location:lsp_display.php');
}


        $query = "SELECT * FROM lsp_active";
        $result = mysql_query($query);

$field_num = mysql_num_fields($result);
echo "<form method='post' action='lsp_display.php'>";
echo "<table border=1>";
$cols = 0;
while ($row = mysql_fetch_assoc($result)) {
if ( $cols == 0) {
        $cols = 1;
        echo "<tr>";
        foreach ($row as $col => $value) {
                print "<th>$col</th>";
        }

        print "<th>Insert Ticket / Notes</th>";
        echo "</tr>";
}
        echo "<tr>";
        foreach ($row as $cell) {
                echo "<td>$cell</td>";
        }
        echo "<td><input type='text' name='notes[]'/></td>";
        echo "</tr>\n";
}
echo "<tr><td colspan=8><input type='submit' name='submit' value='Update'/></td></tr>";
echo "</form>";

mysql_free_result($result);


?>

Now when I print out $_POST['notes'][$key] it spits back out what I give it in the text boxes.

However, the update statement that I have for my SQL isn't updating the database with what I put in.

I am not sure what could be wrong with it :(.

Any help is appreciated!

Thank you!

Careful ... you're prone to an SQL Injection attack. Use a prepared statement or addSlashes() — Yzmir Ramirez
– Yzmir Ramirez, Commented Oct 21, 2011 at 2:50
I have also tried to display what I am giving the SQL command: for example: $_POST['notes'][$key] gives me what I put in each text box $key is also increasing as expected, but it seems there is something wrong with my UPDATE statement for SQL — jmg0880
– jmg0880, Commented Oct 21, 2011 at 2:55
Does your update query work if you try it outside your program? — MikeJerome
– MikeJerome, Commented Oct 21, 2011 at 2:56
@MikeJerome Hey Mike. I have tried the UPDATE Query with multiple variations but the only variation that worked was $query_update = "UPDATE lsp_active SET Notes = ".$_POST['notes'][$key]; The only problem is that this variation updates all of the records at once. — jmg0880
– jmg0880, Commented Oct 21, 2011 at 2:59

Lucas · Accepted Answer · 2011-10-21 03:04:42Z

4

It looks like you probably need to surround your $_POST in single quotes.

Also use a function to clean the $_POST variable.

For example:

function escape($data) {
    $magicQuotes = get_magic_quotes_gpc();

    if(function_exists('mysql_real_escape_string')) {
        if($magicQuotes) {
            $data = stripslashes($data);
        }

        $data = mysql_real_escape_string($data);
    }
    else {
        if(!$magicQuotes) {
            $data = addslashes($data);
        }
    }

    return $data;
}

And then your query:

$query_update  = "UPDATE lsp_active SET Notes = '" . escape($_POST['notes'][$key]) . "'";

Edit:

You also might want to put a WHERE statement on the end of your UPDATE query, so that you don't update everything in the table.

edited Oct 21, 2011 at 3:04

answered Oct 21, 2011 at 2:59

Lucas

10.7k7 gold badges42 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

jmg0880 Over a year ago

I tried the WHERE statement as $query_update = "UPDATE lsp_active SET Notes = '" . escape($_POST['notes'][$key]) ."' WHERE Index = $key";

Lucas Over a year ago

Do you get any kind of error message? Have you tried using the mysql_error() function? for example $result = mysql_query($yourQuery) or die(mysql_error());

jmg0880 Over a year ago

Opps Here is the error I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Index = 0' at line 1

Lucas Over a year ago

In that case my first guess would be to also wrap your $key variable in single quotes too.

jmg0880 Over a year ago

Still getting the same error when I use single quotes around $key Same error

|

IOrlandoni · Accepted Answer · 2011-10-21 03:31:34Z

1

"UPDATE lsp_active a SET a.Notes = '" . mysql_real_escape_string($_POST['notes'][$key]) ."' WHERE a.Index = '" . ($key + 1). "'"

Index is a keyword thar refers to indexes, not your column. So I defined an alias, and made it explicit that Im referring to the column. Also, the + 1 on the Where $key since Index is not zero-based like PHP arrays.

edited Oct 21, 2011 at 3:31

answered Oct 21, 2011 at 3:24

IOrlandoni

1,82813 silver badges31 bronze badges

7 Comments

jmg0880 Over a year ago

How do I make the $key start at 1?

jmg0880 Over a year ago

The issue is that the first value in $key isnt being recorded because it starts at 0 and needs to start at 1

IOrlandoni Over a year ago

Sorry. Edited. Should add 1 to the $key on the where.

jmg0880 Over a year ago

Hmmm even after I changed it at the WHERE clause it still starts at 0 $query_update = "UPDATE lsp_active a SET a.Notes = '" . escape($_POST['notes'][$key]) ."' WHERE Counter = '$key+1'";

IOrlandoni Over a year ago

Are you sure you are not just echoing $key? The record did't get updated?

|

Collectives™ on Stack Overflow

Updating multiple SQL records using a single submit button

2 Answers 2

8 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related