November 16, 2022

Re-establish file download links from eBooks to WordPress site

By  DataBases, eBooks, Web, Wordpress  0 Comments

Background to issue

Major stuffup, I’d got spam comments on website and I deleted about 2/3 of my post table rows. This lost me all my download links to code files for eBooks I’d published.

I was foolish in that I didn’t have a backup of the website (I do now) so I had to fix the problem from scratch.

I quickly unpublished all the relevant eBooks and then had to recreate the links to the correct location on the post table in the websites database.

The process I took to do that is show below.

As I had to find all the links in the existing books I ended with the text but had to find the links.
I was using a spreadsheet to itemise all the links and where they are in the books so having a method to extract the actual URL was quite handy.

Some actions on database

Delete rows in MySQL table

This below buggered my Website as I couldn't retrieve the data after it was blown away, best to backup whole website and database first, or copy table first before using

---DO NOT USE UNDER ANY CIRCUMSTANCES UNLESS YOU HAVE COPIED TABLE FIRST---
DELETE FROM Students WHERE ID=2;

I’ve a script for this person posting in my Revit Shortcuts site where I can strip them out of the table with 

DELETE FROM wordpress332.wp_posts
WHERE post_title like '%Crytorhype%'

So can use that in future without blowing away the database table with all my elements in it.

Stop auto increment on a table and restart it

How to drop auto_increment from a mysql table – Stack Overflow

SET FOREIGN_KEY_CHECKS=0;
alter table added_object modify column id_interaction_object int(11) not null;
SET FOREIGN_KEY_CHECKS=1;

Insert into a Row

I want to be able to put a non-null entry value to the table at an ID just before I need to put in a Download link, so that, when I go back yo auto increment the next one will be the original download link.
I’ll need to do them consecutively, so will need to go through all the books and have all the downloads ready to install in sequence.
Need to change first Value for the row/ID in script below 

SET FOREIGN_KEY_CHECKS=0;
INSERT INTO wordpress332.wp_postsold2(ID, post_title, post_content,post_excerpt,to_ping,pinged,post_content_filtered) VALUES (1024, 'dummy','1','1','1','1','1') ;
SET FOREIGN_KEY_CHECKS=1;

c# – Add a row to a sql database which has a identity primary key – Stack Overflow

Delete a Table

In SQL use DROP for DELETE for TABLES

DROP TABLE wordpress332.wp_postsold1;

Copy a table

First line creates table with same structure as the LIKE one then following 2 lines copies everything from original to new table

CREATE TABLE IF NOT EXISTS wordpress332.wp_postsOld2 LIKE wordpress332.wp_postsOld;

INSERT wordpress332.wp_postsOld2
SELECT * FROM wordpress332.wp_postsOld;

View what’s in a table

SELECT * FROM wordpress332.wp_posts;

Reset Increments-Not realy, it just stops autoincrement, puts the row in and then puts auto-increment back on, so if auto increment set at 3551 then the next auto increent row will be 3552. -SEE BELOW

If over time there are holes in Database you may want to get rid of the gaps. The risk is that with items moved from one row to a lesser row number, if there is a another table using the ID Key to point to that row, it won’t point there anymore.
Don’t use the below code unless you 1/ Backup website & database first, OR 2/ make a copy of the table before doing it, then testing it afterwards.
Otherwise it could be a disaster as things will not point to where they should and so linkages in DB will be broken

SET @count = 0;
UPDATE wordpress332.wp_postsOld2 SET wordpress332.wp_postsOld2.id = @count:= @count + 1;

ALTER TABLE wordpress332.wp_postsOld2 AUTO_INCREMENT = 1;

Reset Auto Increments

How to change auto increment number in MySQL?
So this below tells auto increment where to start from. 

alter table wordpress332.wp_posts auto_increment=1020;

Rename a table

First I rename the existing wp_posts table to wp_postsOrig
Then I rename the modified table wp_postsold1 to wp_posts which has the row ID incrementing from Row ID=1000 onwards. This allows me to look to put original download links into exactly the same location 

ALTER TABLE wordpress332.wp_posts RENAME wordpress332.wp_postsOrig;
ALTER TABLE wordpress332.wp_postsold1 RENAME wordpress332.wp_posts;

Writing data from one table to another (appending)

See commentary below

INSERT wordpress332.wp_postsOld1
SELECT * FROM wordpress332.wp_postsOld2;

Process to reorganise Table “POST” so that it will be compacted to before my Book download links.

1/ Because I’d originally deleted from Table “POSTS” everything after Row 1000, it started incrementing from after the original table row (pre-Delete) so next row is 3551 , so rows go 999,1000, 3551,3552 etc.
I added some more links to site and so all of those increment after 3551.

  • So I copied the “Post” Table twice, “TPost1” & “TPost2”
  • in TPost1 I just deleted everything after Row ID=1000
  • The reason for this is that there are gaps in the rows between Row ID=1 to Row ID=1000 and I don’t want to mess up any of the existing pointers there.
  • in TPost 2 I deleted everything before Row ID =1001, so these rows start at Row ID=3551 and upwards
  • I then Reset Increments with SET @count = 1001; so all new fields instead of starting at Row ID = 3551 now start at Row ID =1001, and say there were 50 rows then the last row is 1051
  • Although I’ve made rows TO Row ID =1051 it still auto increments from Row ID = 3551
  • So I have to use the Reset auto-increment from- Row ID =1051
  • So the next item that is added goes to Row ID =1052 which is what I want to happen.

Process to reorganise Table “POST” so that it will accept new Download links from correct Row ID

  • Now that I have a Post Table that goes up to Row ID =1052, as long as my links are after ID =1052 the n I can:
  • Go to MySQL on Remote Desktop connection and in correct database:
  • Set Auto-Increment to the row number that the next download link should be from, lets say it its ID = 1070, so I set
    – 
    alter table wordpress332.wp_posts auto_increment=1070;
  • Then I can go to the Download Monitor Plugin and create a new Download Link and it should show that Row ID= 1070.
  • If it doesn’t, and it shows something like Row ID= 1071. then you need to go back to MySQL Workbench and delete a couple of rows and try again.
  • I can get a couple of spam comments come in and mess up the sequence so had to delete those Rows, Re-set the auto increment to the row I wanted, delete the last Download Monitor Plugin Download Link and create a new one.
  • You have established the Download Link to the correct row, so can come back later to add file that will be added at a much later Row ID, but it’ll have the association with that Download link at the Row ID= 1070 so it works fine

One thing to note, you can just give the new Download Link a Title/Name and PUBLISH, and come back later and add the download link file that you want.

You have established the Download Link to the correct row, so can come back later to add file that will be added at a much later Row ID, but it’ll have the association with that Download link at the Row ID= 1070 so it works fine

Its actually faster to set these up like this, then come back and add files later, especially if you have large gaps between book Row Download Links


### Process to reorganise Table "POST" so that it will accept new PAGE LINKS for Email Before Download Forms 
- I got caught just creating the download monitor links, then a couple didn't work, these generally were for Last chapter items, WHERE I gave the whole script of the book, so if they wanted it all in one file then they could give me their email. 
- For this I used the Email Before Download Plugin. This takes a FORM Row ID & a Download Link Row ID and combine them in a shortcode on a POST or PAGE. So the link is not to a Download Link but to a specific page or post on the website (I just used pages for this). Therefore the PAGE has to be created at a specific row number in the 'POST" Table. 
- As I'd already created Download Links, which were ERRONEOUS I had to go back and mess with the Database. 
#### Steps to split & rejoin database to be able to insert a NEW item in in the middle of the table 
- This is a bit messy but code below, I finally figured out how to do comments for SQL
- I'll step through process after code block
```SQL
/* */
-- DROP TABLE wordpress332.wp_posts_bac ; 
/*
-- This creates Backup table of posts - in case anything goes wrong
CREATE TABLE IF NOT EXISTS wordpress332.wp_posts_bac LIKE wordpress332.wp_posts;

INSERT wordpress332.wp_posts_bac
SELECT * FROM wordpress332.wp_posts;

-- check that posts_bac table is same as orig posts table
SELECT * FROM wordpress332.wp_posts_bac ;
 */

/* 
-- Create new table for low end - Will add new download later
CREATE TABLE IF NOT EXISTS wordpress332.wp_postsx1 LIKE wordpress332.wp_posts;

INSERT wordpress332.wp_postsx1
SELECT * FROM wordpress332.wp_posts;
-- This deletes all rows above 1940
-- DELETE FROM wordpress332.wp_postsx1 WHERE ID>1940;


-- Create new table for low end - Will add new download later
CREATE TABLE IF NOT EXISTS wordpress332.wp_postsx2 LIKE wordpress332.wp_posts;

INSERT wordpress332.wp_postsx2
SELECT * FROM wordpress332.wp_posts;
*/

-- DELETE FROM wordpress332.wp_postsx2 WHERE ID<1953;

--  SELECT * FROM wordpress332.wp_postsx1 ;
--  SELECT * FROM wordpress332.wp_postsx2 ;
--  SELECT * FROM wordpress332.wp_postsx2 ;

-- Rename posts to a 2nd backup
-- ALTER TABLE wordpress332.wp_posts RENAME wordpress332.wp_posts_bac2;

-- Rename postsx1 to post table
-- ALTER TABLE wordpress332.wp_postsx1 RENAME wordpress332.wp_posts;

-- Check the new posts table to make sure its ready 
-- SELECT * FROM wordpress332.wp_posts ORDER BY ID DESC LIMIT 10; 

--  Set Auto increment to download point 
-- alter table wordpress332.wp_posts auto_increment= 1949 ;

-- GO DO DOWNLOAD and put in New !!!!
--  Add dowwnload as well on next line 

/* 
-- This should copy the higher numbers across on top of the other 
INSERT wordpress332.wp_posts
SELECT * FROM wordpress332.wp_postsx2;
*/
  • POST table has Row ID =2027 as highest row
  • You want to insert a new Page in at Row ID = 1703,
  • there is a gap before this of 2 numbers, sp preceding row number is Row ID = 1701
  • After Row ID = 1703, Row ID = 1704 is active so we do not want to mess with that.
  • Make a copy of POST table first, so you have a backup in case anything is mogadored
  • Make 2 further exact copies of the POST table, x1 & x2.
  • Table x1 you then want to delete all rows above Row ID = 1701, so this is the Lower table, we will set it up so we can insert the new page in at Row ID = 1703
  • Table x2 you then want to delete all rows below Row ID = 1704, so this is the Higher table
  • We then need to Rename the existing POST table
  • We then rename Table x1 to be POST table
  • We then set AUTO-INCREMENT = 1703
  • We then go to Dashboard on website and ADD a NEW PAGE.
  • This page should have Row ID = 1703
  • TEST TO CHECK THIS IS SO. by putting URL to that page into the browser.
  • We can then use:
    INSERT wordpress332.wp_posts
    SELECT * FROM wordpress332.wp_postsx2;
  • This adds all of the items in Table x2 and adds them onto the POST table.
  • We can now create a new FORM for getting name & email address and set that up, it will have a row ID, say Row ID =2028
  • We then create a new Download link to the file that we want to upload, this will have a download link to a row, say Row ID =2029
  • We then go back to the new Page we have created and create a short-code block where we will insert the Email Before Download Plugin short-code to put the Form on the Page & When filled out and submitted will return the Download link to the specific file.
    A long process , but I got there in the end.
    Next time I need to be more careful in seeing if IT’S A download link or a page that the link is pointing to as splitting the Table to add in a Row then merging the table is time consuming, better to have just added them in in an incremental fashion.

End Comment

It took 3 days from realising what had happened to:

  • finding all the download links
  • working out a methodology that would work
  • finding all the files (actually available on the website in sub-directory of uploads folder)
  • working through managing the database and getting the proper SQL commands to do what was required
  • Doing the actual re-linking to original link ID
  • Finding that there were pages for “Email before upload” for some files, so
  • Re-splitting Table to insert new page links instead of Download links
  • Re-publishing books on Amazon & Draft 2 Digital
  • Writing this article for reference for the future.

That will teach me to make sure I have backups. Still, I did learn something along the way.

Another way to look at it is , 1. I really really stuffed up, BUT 2. I was able to fix the issue.

So I’m quite pleased with being able to resolve the issue. So if it happens another time I can fix it. But I won’t need to, as I’ll have a backup!!!!

Post Views: 27

Related Posts