I have a work table, one of the fields is a flg, what I want is to number from number 1 to where it is needed in the corr field (it is initially empty). I have a class called numerador()
. With this I want to number each record by incrementing the variable $num. But I don't know what to put in the while
.
$bd = new ConectaDB();
$sql = "UPDATE temporal SET corr = :num WHERE flg = 'X'";
$num = 0;
while( ) {
$num++;
$stmt = $bd->prepare($sql);
$stmt->bindParam(':num', $num, PDO::PARAM_INT);
$stmt->execute();
}
UPDATE: This is the code I'm testing, but it writes the same value to all records that meet the condition or not. It puts the number of records that meet the condition.
$bd = new ConexionDB();
$sql1 = "SELECT * FROM temporal WHERE flg = 'X'";
$stmt1 = $bd->prepare($sql1);
$stmt1->execute();
$sql2 = "UPDATE temporal SET corr = :num";
$stmt2 = $bd->prepare($sql2);
$num = 0;
while($row = $stmt1->fetch(PDO::FETCH_NUM)) {
$num++;
$stmt2->bindValue(':num', $num, PDO::PARAM_INT);
$stmt2->execute();
}
And this is the structure of my table temporal
.
CREATE TABLE `temporal` (
`id` int(10) UNSIGNED NOT NULL,
`cod` int(10) UNSIGNED NOT NULL,
`nom` varchar(35) DEFAULT NULL,
`flg` varchar(1) DEFAULT NULL,
`corr` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
First of all, you need to get all the rows using a
SELECT
that meets the required criteria: the flag, and thatcorr
isNULL
. In that we willSELECT
obtain theid
one that we will use later as a criterion for eachUPDATE
.Then, you read the rows and update each one with the counter of
$num
. You don't need to use two connections to do this, nor do you need to prepare the query each time within a loop. Precisely the meaning of prepared queries is to trace a path only once, notN
times, so preparing the same query over and over again would be counterintuitive.Also, since it
bindParam()
binds by reference 1 , you can do the bindings outside the loop only once and the values that will be set when the occursexecute()
will be the ones that each variable has at that moment.You may need a
ORDER BY
in your querySELECT
if the order in which the data should be updated matters with respect to any column. I have not put anything because you do not give any details in that sense in the statement of the question.This is how it should work:
Postscript
If this code is to further normalize your data, it may be necessary to rethink your data model, and/or resolve this at the database level, not in PHP. For example, if in time you need to continue updating, you will have to look for the last value of the counter to continue incrementing from there. If this is so, you would be throwing at PHP a problem that is from the database, or simply solving by programming an error that is at another level (in the design of the data model). This is like solving a bug in your program, which can lead to serious problems on several levels.
Grades:
The correct option, given that you are altering record by record, is to condition it to the fact that there is a record to modify. Example:
Assuming that your method
execute()
returns the associative result of the SELECT (and, if not, replacesexecute()
with the correct method):In each iteration, if it
$registro
is a NOT empty array (if a record is found withoutcorr
), the while will continue to execute. Sooner or later the while will stop, because your records are not infinite and they will all have a value assigned to the corr column.GRADES:
The query must be done in each iteration because any of the records obtained prior to the while could no longer meet the condition while you are carrying out the updates. Especially if the table is manipulated by several users at the same time. You can assume that there are few records to modify, and that the total records of the table are few, and the table is manipulated only by you, and any other condition that seeks to guarantee that your list of records that meet the condition will be the same in two runs consecutive... But it's not right. The
<CONDICION>
at the end can be anything, as well as the number of rows and the data type of the columns.Limiting it to 1 is not strictly necessary, but this way $query occupies less memory on each execution. You will always be taking the first record.
I am also assuming that it
id
is the column you use as the index of your table. If it is not, then replace with the correct name. If you don't have an index, then your example will become much more complex. You'll end up wanting to do the whole process in MySQL instead of PHP.