i am try to update a mysql table with a PHP instance query. but I do not know how to put the query correctly or whether there is a logical part it works specified side mysql or if i can do with php.
i get the data from a web form with 2 field the ID(It is the autoincrementable ID in MySQL) and a input with the new order.
Update Case 1: Change Order Update Data ID=3 Imput=5
Original table 1
+--------+---------+
| ID | Order |
+--------+---------+
| 1 | 1 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 10 |this have a Hole from last registre order need preserve
| 8 | 11 |
+--------+---------+
Table
+--------+---------+
| ID | Order |
+--------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 5 |Updated
| 4 | 6 |Updated
| 5 | 7 |Updated
| 6 | 8 |Updated
| 7 | 12 |Update, preserve and continue the hole
| 8 | 13 |Update, and Continue if more record
+--------+---------+
Update Case 2: Inserting a new record and modify the order.
ID=2 Imput=4
Original table 2
+--------+---------+
| ID | Order |
+--------+---------+
| 1 | 1 |
| 7 | 10 |this have a Hole from last registre order need preserve
| 8 | 11 |
+--------+---------+
Table
+--------+---------+
| ID | Order |
+--------+---------+
| 1 | 1 |
| 2 | 4 |record Inserted
| 7 | 10 |preserve no need update
| 8 | 11 |
+--------+---------+
I need some cycles, but do not know what conditions apply.
basics sorry for my example but I am not very expert
Update 1 Legancy
<?php
#Get Values from input Dinamical:
# $i_txt_1 = ID
# $i_txt_3 = New Order
# Attention: this is not the correct syntax for PHP, they are guidelines on what to do at every step, and that must be taken into account for the creation of the string of update.
foreach ($_POST as $key => $value){
${$key} = trim(addslashes(strip_tags($value)));
}
#collector output message
$psmg = '';
#statement prepared for the query updater
$stmtpreUP ="";
#save this variable the current date using some specific function.
$DateUD;
#We keep the variable that is the form that represents the ID
$ID = $i_txt_1;
#first condition
1. search the database if the ID exists we receive the form.
result 0{
throw new Exception You can not modify a nonexistent parameter. Search before Change
}
#second condition
2. if order is the same that the current order display MSG
{
$psmg.='<br>Update was not carried out in the Order';
}
#third condition
3. if check if it exists, any record or ID, with the order comes from the form.
result 0{
update: Create a direct update using the new order and id.
}else{
#Important Step : detecting whether an increase or decrease in the order
4. $GViD = $i_txt_3 - order;
if ($GViD < 0){
#in case is decreasing the order
$stmtpreUP .="UPDATE Table SET Order= $i_txt_3, DateUD= DateUD WHERE ID = $i_txt_1"; #String update for the ID target
#Generate the string updater for the following rows, contemplating that, if a decrease in these rows ID target should be avoided.
5.
GET "SELECT ID, Order FROM Table WHERE Order >= ".$i_txt_3." ORDER BY Order ASC";
$count = $i_txt_3; #need a counter
#Cicle to generate Update String
6.
while ($datos = mysqli_fetch_array($Get)){
#condition to ignore the target ID and update only up to the target ID range, avoid overuse of resources
if($datos['ID']!==$ID AND $datos['ID']<$ID ){
$idUD = $datos['ID'];
$count = ++$count;
$neworder = $count;
#concatenation to the Update String
$stmtpreUP .= "UPDATE table SET Order = ".$neworder.", DateUD ='".$DateUD."' WHERE ID ='{$idUD}';";
}
}
}else{
#in case is Increase the order
$stmtpreUP .="UPDATE Table SET Order= $i_txt_3, DateUD= DateUD WHERE ID = $i_txt_1"; #String update for the ID target
#Generate the string updater for the following rows, contemplating that, if a decrease in these rows ID target should be avoided.
7.
GET "SELECT ID, Order FROM Table WHERE Order >= ".$i_txt_3." ORDER BY Order ASC";
$count = $i_txt_3; #need a counter
#Cicle to generate Update String
8.
while ($datos = mysqli_fetch_array($Get)){
#condition to ignore the target ID and update all the next Order for all the table to preserver spaces into order
if($datos['ID']!==$ID){
$idUD = $datos['ID'];
$count = ++$count;
$neworder = $count;
#concatenation to the Update String
$stmtpreUP .= "UPDATE table SET Order = ".$neworder.", DateUD ='".$DateUD."' WHERE ID ='{$idUD}';";
}
}
}
}
#Run the update of all the statement
9. #function to run mutiple statement updates.
BDupdateM($stmtpreUP);
$psmg.='Datos Actualizado Correctamente';
10. output all MSG
echo $psmg;
?>
