How to update multiple mysql rows and columns using php?

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);
            exit();
      }else{
            $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";
                  exit();
            }else{

Please guys any advaice??

Thank you.
elm9999Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
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.

http://www.mysqltutorial.org/mysql-transaction.aspx
Ray PaseurCommented:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

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.
Chris StanyonWebDevCommented:
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]" />
etc.

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;
	$stmt->execute($values);
endforeach;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elm9999Author Commented:
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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.