将1个sql表复制到另一个包含一些其他数据的表

Question

I have this code:

<?php
//MYSQL
$dbserver="..."; //adresa MySQL
$dblogin="...";       //jméno uživatele MySQL
$dbheslo="...";     //heslo MySQL
$dbnazev="...";      //název databáze MySQL
$mysqli = new mysqli($dbserver, $dblogin, $dbheslo, $dbnazev); 
if ($mysqli->connect_error) {
    die('Nepodařilo se připojit k MySQL serveru (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
$mysqli->query("SET NAMES 'utf8'"); // nastavíme kódování MySQL



while($row = $mysqli->query("SELECT * FROM `importovat_fyz` WHERE `tempid` IS NOT NULL")->fetch_assoc()){

$tempid = $row['tempid'];
$jmeno = $row['jmeno'];
$prijmeni = $row['prijmeni'];


$email = $row['email'];
$bydliste = $row['bydliste'];

$souhlas = "on";
$aktuality = "on";

$timestamp = time();
$hash = md5("77c0a83besxxxcg1a190ab90d".time().$tempid.rand(10000000, 99999999));

$poznamky = "import19052018";

$vloz ="INSERT into `potvrzenotest` set jmeno='".$jmeno."', prijmeni='".$prijmeni."', bydliste='".$bydliste."', email='".$email."', souhlas='".$souhlas."', aktuality='".$aktuality."', timestamp='".$timestamp."', hash='".$hash."', poznamky='".$poznamky."';";
$result=$mysqli->query($vloz);


$cas = date('H:i');
echo '['.$cas.'] ID '.$tempid.' imported.', PHP_EOL;
}

mysqli_close($mysqli); .
?>

I have one table with some data and I have to copy it to another table with some additional data (like hash etc.).

When I run the code above, I got this:

[17:17] ID 1 imported.
[17:17] ID 1 imported.
[17:17] ID 1 imported.
[17:17] ID 1 imported.
[17:17] ID 1 imported.

So, only 1 row is being copied.

Could you please help me, where is the problem?

Answer 1

while($row = $mysqli->query("SELECT * FROM `importovat_fyz` WHERE `tempid` IS NOT NULL")->fetch_assoc()){ ... }

This loop will never end. And it will fetch the first row again and again.

You should execute the query outside the loop:

$selectResult = $mysqli->query("SELECT * FROM `importovat_fyz` WHERE `tempid` IS NOT NULL");

while ($row = $selectResult->fetch_assoc()) { ... }

As a side note: Consider to use a prepared statement for your INSERT statement in the loop. This will prevent SQL syntax errors if some of the values may contail special characters like '. If you run them in one transaction, you might even improve the performance.