Kieran Crown Kieran Crown - 5 months ago 7
SQL Question

How to remove 5 characters from the end of every column in a table MySQLi

I'm trying to loop through every row in a table, and remove 5 characters from the end of one column. I have written the following code but it seems to remove 2 characters for some reason.

<?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

Dont do this in php, you can do it with a single sql query:

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