I have a large table in my MySQL database of different businesses. Included in this table is the path on my site that displays this information (such as example.com/directory/[path]/), and a unique id (such as 5468).
While a path may change, such as if a business changes names, it's unique id will not.
My problem is if the path changes then users going to the old path will get a 404 error.
To combat this I am trying to create a second table in my database which is an archive of paths and ids. I will be able to use this information to later create a 301 redirect between old path to the new path.
I'm creating the following script in PHP to run periodically to output a list of instances where a path exists on the main table, and then one by one copy this information into the archive.
$result = mysql_query("SELECT places.id, places.path FROM places LEFT JOIN paths ON places.path = paths.path WHERE paths.path IS NULL");
while($row = mysql_fetch_array($result)) mysql_query("INSERT INTO paths (path, id) VALUES ('".$row["path"]."', '".$row["id"]."')");
The code seems to work but I was hoping somebody would be able to confirm that there wasn't any faulty logic to it. Also, it seems that there might be a better, more direct, way of copying the data from the first table to the second table. I've used mysql queries in the past that easily copied humongous databases in one query and not have to do it line by line.
Of note, on the paths table, the path is unique.