Save array result back into MySQL database

[insejn6]

Hi there,
First of all, I'm not an expert of PHP whatsoever, so bear with me here   
 

But I've been trying to understand how to "save back" an array of results from a simple table I have in an MySQL database. It contains of 6 rows with 4 columns (bank_id, bank, bank_sum, bank_note). I'm trying to create a simple overview page of the different income places I have for my band and would like to update the bank_sum result easily by displaying those in an input field from an array in a form. So I'd just display all the rows nicely and update if the bank_sum changes and click on a Submit-button.

It displays everything exaclty how I'd like it, so that's good so far! But I can't seem to figure out how I can save those array results back into the database... you'll probably easily see the issue below, I've marked it in red. I've tried with a "single input field update" but it doesn't work  . I've tried some other things as well by asking my dear friend Google but I can't seem to ask the question right to get to a solution so I thought I'd try here again!

This is most likely simple, but I have no idea how to get the array results to update back into the database. This form below does just refresh the page for me.
Thanks in advance.

<?
// -----------------------------------------
//  UPDATE "EKONOMIKOLLEN"! 
// -----------------------------------------
// Check, if you clicked "Submit" button
if($_POST['update_bank']){

// Get parameters from form when submitted...this is most likely wrong 
since it does not take care of all the rows that was 
displayed earlier, this only takes a single one and does not say which one even..
$bank_id = $_POST['bank_id'];
$bank_sum = $_POST['bank_sum'];

// Do UPDATE statement into database.
mysql_query("UPDATE bmf_bank SET bank_id='$bank_id', bank_sum='$bank_sum' WHERE bank_id='$bank_id'");

// Print out a text displaying the success
echo "<center><font color='#FF0000'><b>CHANGE IS MADE!</b></font></center>";
}
?>

<?
// The actual form from which I display the "bank_sum" in 
an input field next to the bank_id and bank. This works great though! 
?>

<form action="<?php echo $PHP_SELF; ?>#bank" method="post" 
enctype="multipart/form-data" name="bankupdate" id="bankupdate" >

<?
// --------------------------------------
// Get results from the "bmf_bank" table and order them by ascending bank_id!
	$result = mysql_query("SELECT * FROM bmf_bank ORDER BY bank_id ASC") 
	or die(mysql_error()); 

	while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
	
		echo "<br><b>";
		echo $row['bank_id'];
		echo ". ";
		echo $row['bank'];
		echo "</b> ";
		echo "<input type='text' name=".$row['bank_id']."' class='form_field' value='".$row['bank_sum']."'>";
		echo "kr<br>";
		echo "<span class='grey_text'>";
		echo $row['bank_note'];
		echo "</span><br>";
	}
?>

	<center>
	<input name="update_bank" type="submit" value="UPDATE EKONOMIKOLLEN!" />
	</center>
	</form> 

[benanamen]

Stop what your doing. Your code is obsolete and dangerous and vulnerable to XSS and has been completely removed from Php. You need to use PDO. https://phpdelusions.net/pdo

[Jacques1]

I really wonder where you got this code from. The last time elements like <font> or <center> were acceptable was back in the mid 90s. 20 years ago. If you're learning web programming from an era where people used Netscape Navigator and Internet Explorer 1, I'm a bit worried.

[gizmola]

This might come off as nitpicking, but we have to understand your terminology. A "Form" doesn't do anything other than present data with html standard inputs designed for people to modify or add to that data.

 

When a form is posted to a php script, the php script will then take any form elements and put them into the special $_POST array. If you actually spend the 10 minutes or so it takes to read these pages from the php manual, you should have a good idea of how things work:

 

 

http://php.net/manual/en/tutorial.forms.php

http://php.net/manual/en/language.variables.external.php

http://php.net/manual/en/faq.html.php

http://php.net/manual/en/reserved.variables.post.php

[Psycho]

I was bored. Below is a complete working script that will generate the form and allow you to update the bank sum value for the records. I didn't put in any error handling, so it is not perfect. But, it has all the basics. You can either modify it to meet your specific needs or use it to step through the code to understand how the process can be done and integrate into your existing framework/code. Be sure to edit the variables at the top as needed for your system.

 

Note: in addition to the other problems raised above, your UPDATE query had a 'flaw'.

UPDATE bmf_bank SET bank_id='$bank_id', bank_sum='$bank_sum' WHERE bank_id='$bank_id'

The bank ID is only needed in the WHERE caluse to identify the record you want to update. It makes no sense to include it in the SET statement to update it to the same value. It will "work", but it is a bad idea to write it that way. It may work in *this* scenario, but if you follow that process you will eventually have a problem down the road and be scratching your head trying to figure it out.

 

 

Complete, working script

<?php
 
//Set variables needed for database work
$server = 'localhost';
$database   = 'DATABASE_NAME';
$user = 'root';
$pass = '';
$charset = 'utf8';
$banktable = "bmf_bank";
 
//Connect to database
$dsn = "mysql:host=$server;dbname=$database;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
 
//Create variable to show a message on page (if needed)
$message = '';
 
//If user submitted changes in POST, process them
if(isset($_POST['bank_sum']))
{
    //Create prepared statement to run updates
    $query = "UPDATE {$banktable} SET bank_sum = ? WHERE bank_id = ?";
    $stmt = $pdo->prepare($query);
    //Iterate through each record in POST data and perform update
    foreach($_POST['bank_sum'] as $bank_id => $bank_sum)
    {
        //Execute prepared statement with current record values
        $stmt->execute([$bank_sum, $bank_id]);
    }
    $message = 'CHANGE IS MADE!';
}
 
//Get current data and create form output
$formOutput = '';
$query = "SELECT bank_id, bank, bank_sum, bank_note FROM {$banktable}";
$stmt = $pdo->query($query);
while($row = $stmt->fetch())
{
    $formOutput .= "<tr>\n";
    $formOutput .= "    <td>{$row['bank_id']}</td>\n";
    $formOutput .= "    <td>{$row['bank']}</td>\n";
    $formOutput .= "    <td><input type='text' name='bank_sum[{$row['bank_id']}]' value='{$row['bank_sum']}' /></td>\n";
    $formOutput .= "    <td>{$row['bank_note']}</td>\n";
    $formOutput .= "</tr>\n";
}
 
?>
<html>
<head></head>
<body>
 
<div style="align:center;color:red;font-weight:bold;"><?php echo $message; ?></div>
 
<form action="" method="post">
 
<table border="1">
<tr>
    <th>Bank ID</th>
    <th>Bank</th>
    <th>Bank Sum</th>
    <th>bank Note</th>
</tr>
<?php echo $formOutput; ?>
</table>
 
<button type="submit">UPDATE EKONOMIKOLLEN!</button>
 
</form>
 
</body>
</html>

Edited June 21, 2017 by Psycho