getting mysql to do multiple updates

Question

I Have a table that look like this

.I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

My current code right now it will always update type B..

echo "<form id='set1' role='form' action=" . $_SERVER['PHP_SELF'] . " method=\"post\">";
echo "<table class='table table-hover table-striped table-bordered'><thead><tr>";

echo "<th class='text-center'>Category</th>";
for($m=1; $m<=12; ++$m){
    $monthName=date('M', mktime(0, 0, 0, $m, 1)).'<br>';
    echo "<th class='text-center'>$monthName</th>";
}

echo "</tr></thead>";


$result= $DB->query("SELECT * FROM ".$DB->prefix("statistic")." WHERE year='$year' AND statistic_id='$statistic_id'") or die(mysql_error()); 
while($row = $DB->fetchArray($result)) {
    $id=$row['id'];
    $year=$row['year'];
    $statistic_id=$row['statistic_id'];
    $categoryname=$row['categoryname'];
    $january=$row['january'];
    $february=$row['february'];
    $march=$row['march'];
    $april=$row['april'];
    $may=$row['may'];
    $june=$row['june'];
    $july=$row['july'];
    $august=$row['august'];
    $september=$row['september'];
    $october=$row['october'];
    $november=$row['november'];
    $december=$row['december'];

    echo "<tr>";    
    echo "<td class='text-center'>$categoryname</td>";

    for($m=1; $m<=12; ++$m){
        $monthName=strtolower(date('F', mktime(0, 0, 0, $m, 1)));
        //echo "<td class='text-center'>".$$monthName." </td>";
        echo "<td class='text-center'> <input style='text-align:center' class='form-control ".$monthName."' type='text' size='60' id='".$monthName."' name='".$monthName."' value='".$$monthName."'></td>";

    }
    echo "</tr>";
}

echo "</table>";
$total=$january+$february+$march+$april+$may+$june+$july+$august+$september+$october+$november+$december;

echo "<input type='hidden' name='id' value='$id'>";
echo "<input type='hidden' name='year' value='$year'>";
echo "<input type='hidden' name='categoryname' value='$categoryname'>";
echo "<button type='submit' name='update-stats' class='btn btn-default'>Save</button>";
echo "</form>";



//check if posted using the update link
if (isset($_POST['update-stats']))  {

    //run the query to update the data

    $DB->query("UPDATE ".$DB->prefix("mystatistik_bulanan")." SET 
    january='" . mysql_escape_string(trim($_POST['january'])) . "',
    february='" . mysql_escape_string(trim($_POST['february'])) . "',
    march='" . mysql_escape_string(trim($_POST['march'])) . "',
    april='" . mysql_escape_string(trim($_POST['april'])) . "',
    may='" . mysql_escape_string(trim($_POST['may'])) . "',
    june='" . mysql_escape_string(trim($_POST['june'])) . "',
    july='" . mysql_escape_string(trim($_POST['july'])) . "',
    august='" . mysql_escape_string(trim($_POST['august'])) . "',
    september='" . mysql_escape_string(trim($_POST['september'])) . "',
    october='" . mysql_escape_string(trim($_POST['october'])) . "',
    november='" . mysql_escape_string(trim($_POST['november'])) . "',
    december='" . mysql_escape_string(trim($_POST['december'])) . "'

    where year='".$_POST['year']."' AND categoryname='".$_POST['categoryname']."' AND id=" . $_POST['id']); 

}

it will only execute once. you have to go with foreachloop and you have to send data as array not a single value. like `name="id"` so it should be like `name="id[]"` — Nirav Joshi, Jun 28 '17 at 08:47
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jun 28 '17 at 08:49

score 1 · Accepted Answer · answered Jun 28 '17 at 08:54

Here is the example to update multiple values

form.html

<html>
<body>
   <form method="post" action="update.php">
     <input type="hidden" name="id[]" value="1" />
     <input type="hidden" name="id[]" value="2" />
     <input type="text" name="value[]" value="test" />
     <input type="text" name="value[]" value="test1" />
     <input type="submit" name="submit" value="submit" />
   </form>
</body
</html>

update.php

<?php
if(isset($_POST['submit'])):
   foreach($_POST['id'] as $key => $value):
      $sql = "update tablename set name='".$_POST['value'][$key]."' where id = '".$_POST['id'][$key]."'";
      //execute your query
   endforeach;
endif;

Hope this will helps you.

thank you @Nirav Joshi..indeed this helped me..i used your code — blackrx, Jun 28 '17 at 14:20

getting mysql to do multiple updates

1 Answers1