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']',
if (!$result1) {
echo "NO SE PUDO ACTUALIZAR LA BASE DE DATOS";
exit();
}else{
Please guys any advaice??
Thank you.
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']',
if (!$result1) {
echo "NO SE PUDO ACTUALIZAR LA BASE DE DATOS";
exit();
}else{
Please guys any advaice??
Thank you.
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
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
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.
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.
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:
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):
<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.
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;
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 :)
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 :)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial