Variable inside a foreach loop with PHP and PDO

Question

I'm trying to add some UUID's to my users table. I'm having a foreach loop which should generate a unique UUID for every user in my db. The problem is that every user is getting the same UUID in the db, but when I add a "echo" i see that it generates a separate UUID for every user, but that is not reflected in the db. Is PDO doing some sort of caching, as it doesn't care about that the $uuid variable is changing?

function getAllIds($db) {
$stmt = $db->query("SELECT id from users;");
$array = $stmt->fetchAll();
return $array;
}

foreach (getAllIds($db) as $ids) {
$uuid = uuid();
print "$uuid\n";
$stmt = $db->prepare("update users set user_uuid = ?");
$stmt->execute(array($uuid));
}

Kevin · Accepted Answer · 2014-10-12 09:40:05Z

1

Try adding a user id on the where clause:

function getAllIds($db) {
    $stmt = $db->query("SELECT id from users;");
    $array = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $array;
}

$stmt = $db->prepare("UPDATE users SET user_uuid = :uuid WHERE id = :id");
$ids = getAllIds($db);
foreach ($ids as $k => $id) {
    $uuid = uuid();
    $stmt->execute(array(':uuid' => $uuid, ':id' => $id['id']));
}

edited Oct 12, 2014 at 9:40

answered Oct 12, 2014 at 8:56

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barmar Over a year ago

Why $ids[$k] instead of $id? Isn't that the point of using foreach rather than for?

TheG Over a year ago

@ghost I have tried your solution, but I am getting a "Array to string conversion..." error

Kevin Over a year ago

@TheG oh i forgot, this is a multi dimensional, check my revision

arkascha · Accepted Answer · 2014-10-12 10:12:38Z

1

The problem is that you are always updating the id of all entries inside your foreach loop. So when the fooreach loop has finished, all entries in the database will have the last chosen uuid as id.

Instead you must make sure to only modify a single database entry with each run of the loop. You can do that by specifying the entries in inside a where clause to the update statement.

$query = $db->prepare("UPDATE users SET user_uuid=:uuid WHERE id=:id");
foreach (getAllIds($db) as $entry)
  $query->execute(array(':uuid'=>uuid(), ':id'=>$entry['id']));

A side aspect: you should prepare that update statement only once: before the foreach loop. No need to repeat that step with every run.

edited Oct 12, 2014 at 10:12

answered Oct 12, 2014 at 8:55

arkascha

43.2k8 gold badges61 silver badges97 bronze badges

1 Comment

TheG Over a year ago

You are absolutely right about preparing the statement before the loop.

Collectives™ on Stack Overflow

Variable inside a foreach loop with PHP and PDO

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related