-1

I'm trying to select commenter from a table called comments then select username from a table called users using a where clauses from the details from the first table. After that update comments set username to the usernames obtained from table 2.

This is my code:

<?php 
include("connection.php") ;
$sql=mysqli_query($link, "SELECT * FROM comment ORDER BY id ASC" ) ;
while($row=mysqli_fetch_assoc($sql)){
    $commenter=$row['commenter'] ;

    $sqli=mysqli_query($link, "SELECT * FROM users WHERE fullname='$commenter' ORDER BY id ASC" ) ;
    while($rows=mysqli_fetch_assoc($sqli)){
        $username=$rows['username'] ;
        $sql_u=mysqli_query($link, "UPDATE comment SET username='$username' WHERE commenter='$commenter' " ) ;
    }
}
?>

The code above only updates one row

Efoskhi
  • 61
  • 5
  • 1
    Is there a reason you don't do this all in one query, by using a `JOIN` in the `UPDATE` query? – Barmar Mar 26 '21 at 21:32
  • I can't see any reason why this would only update one row. Add some `echo` statements so you what it's doing. – Barmar Mar 26 '21 at 21:36
  • you code is **vulnerable** to **sql injection** so use only **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Mar 26 '21 at 21:54

1 Answers1

0

Here is a small correction of Barmar answer

UPDATE comment c
JOIN users u ON u.fullname = c.commenter
SET c.username = u.username

u.fullname = c.commenter instead of comment

Titi
  • 121
  • 5