First off I’d suggest to change the database structure in the same go. You are repeating a lot of data and would have a much easier time fixing this if artist was a foreign key to a separate artists table.
[code]artist
id, name, etc
1, “The Breeders, The”
2, “Commitments, The”
3, “Confederate Railroad, The”
record
id, artist_id, title
2323, 1, Cannonball
4251, 2, Chain Of Fools
4252, 2, Gimme Some Lovin’
4253, 2, I Can’t Stand The Rain
4254, 2, In The Midnight Hour[/code]
Having it structured in a normalized way means you can go edit the artist name in one place and fix all other entries where that artist is used.
[hr]
30 000 records isn’t much. I’d just create a conversion script and run it.
[php]<?php
$host = ‘127.0.0.1’;
$db = ‘test’;
$user = ‘root’;
$pass = ‘’;
$charset = ‘utf8’;
$increment = 200; // how many records to fetch on each page load, adjust if the server times out
$dsn = “mysql:host=$host;dbname=$db;charset=$charset”;
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
$start = isset($_GET[‘start’]) ? $_GET[‘start’] : 0;
$stmt = $pdo->query(“SELECT id, artist FROM table LIMIT $start,$increment”);
$records = $stmt->fetchAll();
if (count($records) == 0)
{
die(‘done’);
}
$pdo->prepare(“UPDATE table SET artist = ? WHERE id = ?”);
foreach ($records as $record)
{
if ($record[‘artist’] == ‘blah’) // needs logic to find what you would consider a wrong artist name
{
$artist= ‘foo’; // needs logic to fix the name in $record[‘artist’]
$pdo->execute([$artist, $record[‘id’]]);
}
}
$start = $start+$increment;
header(“Location: $_SERVER[‘PHP_SELF’] ?start=$start”);
die();[/php]
This file will fetch the first 200 records, look through them for the error(s) you’ve added, and if needed update the entries. Then reload the page to do the operation on the next 200 records.
For 30.000 records this shouldn’t take long.
