Multiple INSERT/UPDATE with SQL and PHP

Question

can you suggest me the best solution to make multiple INSERT and UPDATE at the same time, please?

I use a SELECT to retrieve records from a table and I let the user to update the content of each record if he checks the checkbox "insert". At the moment, I use a form button for each set of records.

Since there can be lots of records, I would like to use only a single "form" and a single "form button" for all the data and to update or insert only the set of records when the user checks the checkbox. Is it possible? If yes, how? Can you give me any suggestions, please?

$sql = "select * from table1 where nome_area like '%$nome_area%' AND nome_voce like '%$nome_voce%'";

    $rs = mysql_query( $sql ) or die('Database Error: ' . mysql_error());
    $num = mysql_num_rows( $rs );

    if($num >= 1 ){         

        echo "<table align=\"center\" cellspacing=0 cellpadding=100>";

        echo "<tbody><tr><td width=\"20%\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td>";

        while($row = mysql_fetch_array( $rs )){

            echo "<form name=\"form\" id=\"form\" action=\"update.php\" method=\"post\">";
        echo "<table data-role=\"table\" class=\"ui-responsive\"  cellspacing=\"20\" ";
        echo "<tbody>";

            echo "<tr>";
            echo "<td>";
            echo "<font text color=\"red\">Insert</font> &nbsp;&nbsp;&nbsp;</td><td><input type=\"checkbox\"  name=\"insert\" value=\"1\" id=\"insert\"></td></tr>";
            echo "<tr><td><b>Tipo Area</b>:</td><td>".$row['nome_area']. " </td></tr><tr><td><b>Nome della voce</b>: </td><td> " .$row['nome_voce'] . "</td></tr>";
echo "<tr><td>";
            echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"id_voce\"  maxlength=\"50\" value=\"" . $row['ID'] . "\">";
            echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"nome_azienda\"  maxlength=\"50\" value=\"" .$name. "\">"; // azienda
            echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"nome_area\"  maxlength=\"50\" value=\"" .$row['nome_area']. "\">";
            echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"nome_voce\"  maxlength=\"50\" value=\"" .$row['nome_voce']. "\">";
            echo "</td></tr>";
            echo "<tr><td colspan=\"2\"> <input type=\"submit\" value=\"Associa la voce di contratto all'azienda\" id=\"search-btn\" style=\"height:50px; \"; /></td></tr>";
            echo "</tbody></table>";
                echo "</form>";



}

echo "</td></tr></tbody></table>";

    }else{
        // if no records found
        echo "<br><br><b>Nessun risultato trovato!</b></div>";
    }

In my update.php page, I just read the form values and INSERT them into my table:

.....
mysql_query("INSERT INTO table2 (id_voce,associazione, nome_azienda, nome_area, nome_voce, created_date) VALUES('$id_voce','$associa','$nome_azienda','$nome_area','$nome_voce','$time')");

David White · Accepted Answer · 2016-03-30 19:06:59Z

1

One way would be to generate a sequential row id # for each row. Let's assume this is your collection of rows from the database:

$rs = array(
    array('ID'=>'111', 'nome_area'=>'first nome area',  'nome_voce'=>'first nome voce'),
    array('ID'=>'222', 'nome_area'=>'second nome area', 'nome_voce'=>'second nome voce'),
    array('ID'=>'333', 'nome_area'=>'third nome area',  'nome_voce'=>'third nome voce'),
);

Now create the table with a checkbox that has a sequential ID number:

$rowcounter = 0;
foreach ($rs as $row) {
    $rowcounter++;
    echo <<<HERE
<tr>
  <td><input type="checkbox" name="row_{$rowcounter}_checkbox" value="1" /></td>
  <td>
    ID: {$row['ID']}
    <input type="hidden" name="row_{$rowcounter}_ID" value="{$row['ID']}" />
  </td>
  <td>
    Tipo Area: {$row['nome_area']}
    <input type="hidden" name="row_{$rowcounter}_nome_area" value="{$row['nome_area']}" />
  </td>
  <td>
    Nome della voce: {$row['nome_voce']} 
    <input type="hidden" name="row_{$rowcounter}_nome_voce" value="{$row['nome_voce']}" />
  </td>
</tr>

HERE;
}

When the form is submitted, you can find all checkboxes that are checked, then process the other form values related to that unique row ID:

foreach ($_POST as $key=>$val) {
    if (preg_match("/^row_(\d+)_checkbox$/",$key,$matches) && $val == "1") {

        $rowid = $matches[1];

        $ID = $_POST["row_{$rowid}_ID"];
        $nome_area = $_POST["row_{$rowid}_nome_area"];
        $nome_voce = $_POST["row_{$rowid}_nome_voce"];

        print "The checkbox was checked for the row with this data:\n";
        print "ID: $ID\n";
        print "nome_area: $nome_area\n";
        print "nome_voce: $nome_voce\n";
    }
}

EDIT For your own HTML code, it would be something like this:

$rowcounter = 0;
echo "<form name=\"form\" id=\"form\" action=\"update.php\" method=\"post\">";
echo "<table data-role=\"table\" class=\"ui-responsive\"  cellspacing=\"20\" ";
echo "<tbody>";
while($row = mysql_fetch_array( $rs )){
    $rowcounter++;
    echo "<tr>";
    echo "<td>";
    echo "<font text color=\"red\">Insert</font> &nbsp;&nbsp;&nbsp;</td><td><input type=\"checkbox\"  name=\"row_{$rowcounter}_insert\" value=\"1\" id=\"insert\"></td></tr>";
    echo "<tr><td><b>Tipo Area</b>:</td><td>".$row['nome_area']. " </td></tr><tr><td><b>Nome della voce</b>: </td><td> " .$row['nome_voce'] . "</td></tr>";
    echo "<tr><td>";
    echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"row_{$rowcounter}_id_voce\"  maxlength=\"50\" value=\"" . $row['ID'] . "\">";
    echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"row_{$rowcounter}_nome_azienda\"  maxlength=\"50\" value=\"" .$name. "\">"; // azienda
    echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"row_{$rowcounter}_nome_area\"  maxlength=\"50\" value=\"" .$row['nome_area']. "\">";
    echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"row_{$rowcounter}_nome_voce\"  maxlength=\"50\" value=\"" .$row['nome_voce']. "\">";
    echo "</td></tr>";
}
echo "<tr><td colspan=\"2\"> <input type=\"submit\" value=\"Associa la voce di contratto all'azienda\" id=\"search-btn\" style=\"height:50px; \"; /></td></tr>";
echo "</tbody></table>";
echo "</form>";

and a form processor something like this:

foreach ($_POST as $key=>$val) {
    if (preg_match("/^row_(\d+)_insert$/",$key,$matches)) {
        $rowid = $matches[1];
        $nome_id_voce = $_POST["row_{$rowid}_nome_id_voce"];
        $nome_area    = $_POST["row_{$rowid}_nome_area"];
        $nome_voce    = $_POST["row_{$rowid}_nome_voce"];
    }
}

edited Mar 30, 2016 at 19:06

answered Mar 30, 2016 at 18:45

David White

1,8933 gold badges18 silver badges28 bronze badges

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

5 Comments

Marcus Barnet Over a year ago

thank you for ur answer! but how can I fill the array after the SELECT? I'm studying your solution.. it seems to be a little bit complicated for me! :(

David White Over a year ago

I have updated my answer to show how you can write a loop around your HTML table-generating code (using a $rowcounter to ensure the fields in each row have unique names), then process them once the form is submitted.

Marcus Barnet Over a year ago

Thanks a lot, David! It works very well! I just can't read $_POST["row_{$rowid}_nome_id_voce"] since the variable $nome_id_voce is always NULL. I'm trying to understand the reason..

Marcus Barnet Over a year ago

I solved it!!! Everything works well!!! Now i just have to insert the SQL query in the loop!!!

David White Over a year ago

Glad to hear it's working. If you found it helpful I would be obliged if you would "accept" my answer (clicking the check mark to the left of my answer).

raidenace · Accepted Answer · 2016-03-30 18:23:33Z

0

Use ON DUPLICATE KEY UPDATE syntax for performing insertion of new record or updation of existing ones (based on PK)

answered Mar 30, 2016 at 18:23

raidenace

12.8k1 gold badge34 silver badges35 bronze badges

Comments

mkysoft · Accepted Answer · 2016-03-30 18:24:33Z

0

You need adding record id to element name. Example:

echo "<input type=\"hidden\" style=\"width:0; height:0; border:0; background-color:inherit; overflow:hidden;\" name=\"nome_voce_". $row['ID'] ."\"  maxlength=\"50\" value=\"" .$row['nome_voce']. "\">";

You can get all variables in update.php and prepare multiple insert command. Also there are js based table editor, you can use them. Easy one: http://codepen.io/ashblue/pen/mCtuA

answered Mar 30, 2016 at 18:24

mkysoft

5,7881 gold badge26 silver badges33 bronze badges

1 Comment

Marcus Barnet Over a year ago

The problem is that I dont know how to get undefined form values in update.php file in order to update it..

Collectives™ on Stack Overflow

Multiple INSERT/UPDATE with SQL and PHP

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related