CODEWITHSUNDEEP
phpmysql
jmg0880
jmg0880

Reputation: 135

Updating multiple SQL records using a single submit button

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!

Upvotes: 0

Views: 1475

Answers (2)

IOrlandoni
IOrlandoni

Reputation: 1828

"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.

Upvotes: 1

Lucas
Lucas

Reputation: 10646

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.

Upvotes: 4

Related Questions