homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Gold Sponsor 2015!
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Creating an archive of a table subset

5+ Year Member Top Contributors Of The Month

Msg#: 4402890 posted 4:22 am on Jan 2, 2012 (gmt 0)

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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved