php - 使用 PHP similar_text 和 UPDATE 合并表

Question

我想合并 MySQL 中的两个表:

table_1
--------------------------------------------------------
id |     table_1_name           |   table_1_art
--------------------------------------------------------
1  |     Ernest Hemingway       |   Writer
--------------------------------------------------------
2  |     Mark Twain             |   Writer
--------------------------------------------------------
3  |     Jackson Pollock        |   Painter
--------------------------------------------------------
4  |     Miles Davis            |   Musician
--------------------------------------------------------
5  |     Akira Kurosawa         |   Cinema
--------------------------------------------------------


table_2
--------------------------------------------------------
id |     table_2_name             |   table_2_art
--------------------------------------------------------
1  |     Hemingway, ernest        |   Writer
--------------------------------------------------------
2  |     Twain, Mark              |   Writer
--------------------------------------------------------
3  |     P., Jackson              |   Painter
--------------------------------------------------------
4  |     Miles Dewey Davis        |   Musician
--------------------------------------------------------
5  |     Bukowski, Charles        |   Poetry
--------------------------------------------------------

我想用 table_1_name 的数据更新字段 table_2_name，但前提是它们之间存在相似性。我正在使用 similar_text，并选择超过 40% 的相似度:

<?php

// Connection
$server = "****";
$user = "****";
$pass = "****";
$db_name = "****";
$conn = new mysqli($server, $user, $pass, $db_name);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    echo "Connected <br/>";
}

//Begin
$petition = "SELECT * FROM table_1,  table_2";
$result = mysqli_query($conn, $petition);
while ($row = mysqli_fetch_assoc($result)) {
    $table_1_name = $row['table_1_name'];
    $table_1_art = $row['table_1_art'];
    $table_2_name = $row['table_2_name'];
    $table_2_art =$row['table_2_art'];

//Ask for similarities
similar_text($table_1_name, $table_2_name, $percent);

//Show coincidences over 40%
if ($percent > "40%") {
    echo $table_1_name." — ".$table_2_name." — ".$percent."% <br />";

//Update field in table_2_name for this coincidences
$sql = "UPDATE table_2 SET table_2_name = '$table_1_name'";
    if ($conn->query($sql) === true) {
        echo "updated <br/> <br/>";
    } else {
        echo "ERROR: " . $conn->error." <br/> <br/>";
    }
}
    $conn->close();
}

但是我得不到结果。而不是这个:

table_2
--------------------------------------------------------
id |     table_2_name             |   table_2_art
--------------------------------------------------------
1  |     Ernest Hemingway         |   Writer
--------------------------------------------------------
2  |     Mark Twain               |   Writer
--------------------------------------------------------
3  |     Jackson Pollock          |   Painter
--------------------------------------------------------
4  |     Miles Davis              |   Musician
--------------------------------------------------------
5  |     Bukowski, Charles        |   Poetry
--------------------------------------------------------

我明白了:

table_2
--------------------------------------------------------
id |     table_2_name             |   table_2_art
--------------------------------------------------------
1  |     Ernest Hemingway         |   Writer
--------------------------------------------------------
2  |     Ernest Hemingway         |   Writer
--------------------------------------------------------
3  |     Ernest Hemingway         |   Painter
--------------------------------------------------------
4  |     Ernest Hemingway         |   Musician
--------------------------------------------------------
5  |     Ernest Hemingway         |   Poetry
--------------------------------------------------------

我敢肯定这是一个很常见的错误，至少对于初学者来说是这样，但我找不到!

尼基塔

PD:正如@denny 所说，sql 需要一个条件，我忘了说我尝试了一些选项，例如:

$sql = "UPDATE table_2 SET table_2_name = '$table_1_name' WHERE ($percent > '45%')";

现在的问题是:哪个条件以及如何。不敢相信有那么难……

Answer 1

修改sql

$sql = "UPDATE table_2 SET table_2_name = '$table_1_name'";

为此

$sql = "UPDATE table_2 SET table_2_name = '$table_1_name'"where column="您的条件";

语法:-

更新 table_name set column_name = 'value' where column_name ='your_condition'

如果你执行

更新 table_name set column_name = 'value'

它将更新整个表格行