Fundamentals of rules for the UPDATE statement for a relational table MySql: for vs while

Question

I am taking the first steps with mysql and php, so I have doubts about the fundamental rules for optimizing the right code.

I have a case where my statement UPDATEmust execute in a certain number of rows, because it must execute in a relational table, so is this correct for cicle?

<?
// connection already created
$data[] = array ("id" => 54, "enabled" => 1);
$data[] = array ("id" => 33, "enabled" => 0);
$data[] = array ("id" => 12, "enabled" => 0);
$data[] = array ("id" => 58, "enabled" => 0);
$data[] = array ("id" => 21, "enabled" => 1);
$data[] = array ("id" => 10, "enabled" => 1);
$data[] = array ("id" => 18, "enabled" => 0);
$data[] = array ("id" => 32, "enabled" => 1);
$data[] = array ("id" => 84, "enabled" => 0);
$data[] = array ("id" => 80, "enabled" => 1);

for (var $i = 0; $i < count ($data); $i ++) {

    $id = $data[$i]["id"];
    $enabled = $data[$i]["enabled"];

    $sql = "UPDATE users SET user_enabled = '$enabled' WHERE user_id = '$id' LIMIT 1;";
    $res = mysql_query ($sql);
    $num = mysql_num_rows ($res);

}


?>

Should I use a loop whileor for? Is this code valid for multiple UPDATEs, or is there more than specific queries for this kind of action?

Answer 1

A few comments:

• count($data) . count($data), , , @Ignacio, foreach.

• , . mysql PHP. mysqli PDO.

• SQL.

• mysql_num_rows() UPDATE. , mysql_affected_rows().

• , LIMIT , , user_id users, .

:

<?php
// PDO connection already created
$data[] = array ("id" => 54, "enabled" => 1);
...etc...

$sql = "UPDATE users SET user_enabled = :enabled WHERE user_id = :id";
$stmt = $pdo->prepare($sql);

foreach ($data as $parameters) {

    $success = $stmt->execute($parameters);
    $num = $stmt->rowCount();

}


?>

Answer 2

foreach.

Answer 3

you can use RecursiveArrayIterator

<?
// connection already created
$data[] = array ("id" => 54, "enabled" => 1);
$data[] = array ("id" => 33, "enabled" => 0);
$data[] = array ("id" => 12, "enabled" => 0);
$data[] = array ("id" => 58, "enabled" => 0);
$data[] = array ("id" => 21, "enabled" => 1);
$data[] = array ("id" => 10, "enabled" => 1);
$data[] = array ("id" => 18, "enabled" => 0);
$data[] = array ("id" => 32, "enabled" => 1);
$data[] = array ("id" => 84, "enabled" => 0);
$data[] = array ("id" => 80, "enabled" => 1);

$data = new RecursiveArrayIterator($data);

for (var $i = 0; $i < count ($data); $i ++) {

    $id = $data[$i]["id"];
    $enabled = $data[$i]["enabled"];

    $sql = "UPDATE users SET user_enabled = '$enabled' WHERE user_id = '$id' LIMIT 1;";
    $res = mysql_query ($sql);
    $num = mysql_num_rows ($res);

}


?>