php - 如何从表 MySQLi 中每列的末尾删除 5 个字符

Question

我试图循环遍历表中的每一行，并从一列末尾删除 5 个字符。我编写了以下代码，但由于某种原因似乎删除了 2 个字符。

<?php
$connection = new mysqli('localhost', 'nawd_test', 'password', 'nawd_test');

if ($connection->connect_errno > 0) {
    die ('Unable to connect to database [' . $connection->connect_error . ']');
}

$sql = "SELECT *
        FROM test";  

if (!$result = $connection->query($sql)) {
    die ('There was an error running query[' . $connection->error . ']');
}

//Create an array to hold the values of id's already changed
$ids = [];
$newVals = [];

echo '<p>Fetching rows...</p>';
while ($row = $result->fetch_assoc()) {
    //Check / Add the id
    if (in_array($row['id'], $ids)) {
        echo '<p style="red"><strong>Error: Script has repeated itself!</strong></p>';
        break;
    }
    $ids[] = $row['id'];

    $rowName = $row['name'];
    $newName = substr($rowName, -1);
    $newName = rtrim($rowName,$newName);
    $newVals[] = $newName;
}

//Now loop and update
$newValID = 0;
foreach ($ids as &$id) {
    echo '<p>Updating row with ID ' . $id . '</p>';
    mysqli_query($connection, "UPDATE test SET name ='" . $newVals[$newValID] . "' WHERE id=" . $id);
    echo '<p>Column successfully changed "<em>' . $newVals[$newValID] . '</em>"</p>';
    $newValID++;
}

echo '<p style="color: green;"><strong>Script complete!</strong></p>';
?>

Answer 1

不要在 php 中执行此操作，您可以使用单个 sql 查询来完成:

UPDATE test SET name = LEFT(name, LENGTH(name) - 5)