Update rows after mysql select foreach

Question

I have a select where I have 3 results:

$stmt = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = '$usr_id'");
$stmt->execute();

After this select I have 3 results. Now I want in another table update or insert a new row for each result

This is my complete code

I don't have any update or new insert in table. Can anybody please help me?

  $stmt = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = '$usr_id'");
  $stmt->execute();
  while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $rows[]=$row;
    foreach($rows as $row){
    $site_id = $row[id];    

            $stmt = $handler->prepare("SELECT id FROM session WHERE site_id = '$site_id' AND usr_id = '$usr_id'");
            $stmt->execute();
            $no=$stmt->rowCount(); 

            if ($no > 0)
            {
                $stmt = $handler->prepare("UPDATE session SET comments = '$comments' , likes = '$likes' , views = '$views'  WHERE usr_id = $usr_id AND site_id = $site_id");
                $stmt->execute();

            }
            else
            {
                $stmt = $handler->prepare("INSERT INTO session(user_id,site_id,comments,likes,views)VALUES('$user_id','$site_id','$comments','$likes','$views')");
                $stmt->execute();

            }
        }
  }

Why `foreach($rows as $row){` in the `while` loop; use `fetchAll`? You also are using prepared statements unsafely, parameterize them. — chris85, Jan 20 '16 at 22:26
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 20 '16 at 22:26
You also could use `insert on duplicate update`. http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html — chris85, Jan 20 '16 at 22:28
Sorry for my BAD Englisch Ok here again I have a table with Infos About a Website SITE id|usr_id|comments|views|likes In my first Select i need the site_id from the table where usr_id In my table i have after the select 3 results Now i want to insert in another table 3 new rows with infos from the SITE Table SESSION id|usr_id|site_id|comments|views|likes — Andi Wyder, Jan 20 '16 at 22:38
@AndiWyder but in your first select , you dont select `site_id` — meda, Jan 20 '16 at 22:39
why even have a loop? Why not have a single statement IODKU with an embedded select statement? Takes PHP out of the picture — Drew, Jan 20 '16 at 22:48

score 1 · Answer 1 · answered Jan 20 '16 at 22:45

First issue, you weren't taking advantage of prepared statements at all. Use parameters (the ? in the query) and then fill them with values in the execute() call.

Also, prepare your query outside a loop, and execute it inside. This is one of the key advantages of preparing statements in advance, there is less overhead when they are only prepared once.

Finally, there's no need for checking the database before your query and then executing one of two queries. Just let MySQL check if the value exists already with INSERT...ON DUPLICATE KEY UPDATE syntax. This relies on the database being set up properly, so there should be a UNIQUE index on (session.usr_id, session.site_id).

This is untested, but should get you going:

$stmt1 = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = ?");
$stmt2 = $handler->prepare("INSERT INTO session SET comments = ?, likes = ?, views = ?, usr_id = ?, site_id = ? ON DUPLICATE KEY UPDATE comments = VALUES(comments), likes = VALUES(likes), views = VALUES(views)");

$stmt1->execute(array($usr_id));
while($row = $stmt1->fetch(PDO::FETCH_ASSOC)) {
    $site_id = $row["id"];
    $stmt2->execute(array($comments, $likes, $views, $usr_id, $site_id));
}

thanks for the code. Dont work. When I make an echo on $site_id =$row["id"]; I dont see a result. In phpmyadmin when I make the query i have 3 results — Andi Wyder, Jan 20 '16 at 23:01
This is basic debugging. Make sure `$usr_id` has a value, check to make sure `$stmt1` and `$stmt2` are valid, check the return of `execute()`, etc... — miken32, Jan 20 '16 at 23:09

score 0 · Accepted Answer · answered Jan 21 '16 at 16:30

@Miken32's answer would be the ideal way.

A direct fix to your code would be this way:

$stmt1 = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = :usr_id");
$stmt1->bindValue(':usr_id', $usr_id);
$stmt1->execute();
while ($row = $stmt1->fetch(PDO::FETCH_ASSOC)) {
    $stmt2 = $handler->prepare("SELECT id FROM session WHERE site_id = :site_id AND usr_id = :usr_id");
    $stmt2->bindValue(':usr_id', $usr_id);
    $stmt2->bindValue(':site_id', $row['id']);
    $stmt2->execute();

    if ($stmt2->rowCount() > 0) {
        $stmt3 = $handler->prepare("UPDATE session SET comments = :comments , likes = :likes , views = :views  WHERE usr_id = :usr_id AND site_id = :site_id");
    } else {
        $stmt3 = $handler->prepare("INSERT INTO session(user_id,site_id,comments,likes,views)VALUES(:usr_id,:site_id,:comments,:likes,:views)");
    }
    $stmt3->bindValue(':comments', $row['comments']);
    $stmt3->bindValue(':likes', $row['likes']);
    $stmt3->bindValue(':views', $row['views']);
    $stmt3->bindValue(':usr_id', $usr_id);
    $stmt3->bindValue(':site_id', $row['id']);
    $stmt3->execute();
}

But this is not the best way to go about it. INSERT ...UPDATE ON DUPLICATE KEY would be better.

ok thanks for this solution... @meda Now i have an INSERT in the Table session The Problem is... when i make an echo after while ($row = $stmt1->fetch(PDO::FETCH_ASSOC)) { then the result is 53,54,55 but in the Table session i have only one entry for the id 53. Anybody have a solution about this — Andi Wyder, Jan 24 '16 at 20:23

Update rows after mysql select foreach

2 Answers2