We help IT Professionals succeed at work.

How to update multiple mysql rows and columns using php?

elm9999 asked
Hi guys,

I have a table that I need to update, this table has 20 columns and 31 rows, it's something like this:

Day   h0400_0415  h0415_0430  h0430_0445  h0445_0500  h0500_0515  h0515_0530  h0530_0545  h0545_0600
01     10                     12                   10                   12                   10                    12                  10                    12                
02     20                     30                   20                   30                   20                    30                  20                    30                  
03     10                     12                   10                   12                   10                    12                  10                    12                  
04     20                     30                   20                   30                   20                    30                  20                    30                  
05     10                     12                   10                   12                   10                    12                  10                    12                  
06     20                     30                   20                   30                   20                    30                  20                    30                  

I know there are a lot of fields, but I need to update all at once.
all the values comes from 620 text boxes, so there are 20 textboxes per day and the name of the text boxes are:

for day 1 =====> h01_0400_0415, h01_0415_0430, h01_0430_0500 ......... until h01_0845_0900
for day 2 =====> h02_0400_0415, h02_0415_0430, h02_0430_0500 ......... until h02_0845_0900
for day 3 =====> h03_0400_0415, h03_0415_0430, h03_0430_0500 ......... until h03_0845_0900

And this is my query I'm trying to build:

      $mysqli1 = new mysqli($DBhost, $DBuser, $DBpass, $DBname);
      if ($mysqli1->connect_errno) {
            printf("No fue posible conectarse a la base de datos: %s\n", $conn->connect_error);
            $result1 = "UPDATE control_citas SET h0400_0415 = '$_POST['01_h0400_0415']', h0415_0430 = '$_POST['01_h0415_0430']'  WHERE ano = '2015' && mes = '$mes'";
            if (!$result1) {
                  echo "NO SE PUDO ACTUALIZAR LA BASE DE DATOS";

Please guys any advaice??

Thank you.
Watch Question

You cannot do it in a single statement.  Therefore you cannot do it literally "all at the same time".

You can do it within a transaction so that the DB considers the multiple update statements a single unit of work.

Most Valuable Expert 2011
Top Expert 2016
You might want to take a step back from this task and learn how MySQLi works.  The code as written above creates a query string in the $result variable, but never actually runs the query!  This article shows how to use the PHP MySQL extensions.

My recommendation: set up a simple test case with a small database - perhaps three columns and a simple HTML example with three matching checkboxes.  With that simple example (1) you don't have to create a lot of data and (2) you can do a simple proof of concept.  We call this the SSCCE.  Once you have that working, start scaling up to the larger test case.
Most Valuable Expert 2018
Distinguished Expert 2019
Here's a tip to make your life a LOT easier. When you create your HTML form, name the <input> elements with an array format, using the day and column names as keys:

<input type="text" name="day[01][h0400_0415]" />
<input type="text" name="day[01][h0415_0430]" />
<input type="text" name="day[01][h0430_0445]" />
<input type="text" name="day[02][h0400_0415]" />
<input type="text" name="day[02][h0415_0430]" />
<input type="text" name="day[02][h0430_0445]" />

Open in new window

When your <form> gets POSTed, you will then be able to loop through the values much easier, and UPDATE your table row by row.

If you use a prepared statement (and you should), then you could do the whole update using something along these lines (it's using PDO and presumes you already have a database connection):

$stmt = $dbh->prepare("UPDATE yourTable SET h0400_0415 = ?, h0415_0430 = ?, h0430_0445 = ?, h0445_0500 = ? WHERE day = ? AND ano='2015'");
foreach ($_POST['day'] as $day => $time):
	$values = array_values($time);
	$values[] = $day;

Open in new window


Thank you guys,

Actually, this app is to be used for a short period of time, the user page is already done and this one is to administer how many appointments we need per day, but to tell the truth, is much more easy to change values directly on the database because during this period is it possible that the values remain the same until the end, and there is another job that I had to stop, As soon as I have time I will learn some more about.

Thank you again :)