my Traveller site, with some updates mentioned

JimJoe · 2022-01-14 00:38:05

Thanks, I’ll look that over. Not much in php is obvious to me. I did find a pdf of learning php, but I really haven’t had time to look at it.

edit: The only problem I see is my older sql files have article numbers in the links, and I chose to not use article numbers this time.

I’ll check into the programs you suggested.

Thanks Jakob !

Last edited by JimJoe (2022-01-14 00:43:01)

JimJoe · 2022-01-14 00:51:30

Ah, I should mention its been around 30 years since I used diff in any form. I’ll look at the docs and figure it out.

JimJoe · 2022-01-14 20:27:11

The two I used, Meld and WinMerge, insisted two files, just the articles in each, were identical. They were about 500 kilobytes different.

So I used your method of just inserting the additional articles at the bend, and uploading that sql file. Backup first.

Apparently the articles are not in the same order… in the two files, so I didn’t get all of them back. But I did get about 1800 more articles added in. When I searched for ‘Planet: ‘, which should have been most of them, there were only about 200 more.

Anyway, less editing. But I’ll still check each one. Another site I used an earlier sql file on, I wound up with a few duplicates. I do have an Open Office Calc file that lists all planets and where I placed them on the maps.

Thanks for your help !

edit: I decided to check the file sizes on my hard drive, because the export backup is staying at 8 megabytes.

The file I tried to import shows on my hard drive as a bit over 10 megs.

I did get an error that says ‘#1050 – Table ‘drivein_textpattern’ already exists’.

So… I think I’ll create a new database, import the 10 meg file into that one, and switch over. See if then I get more than just a few articles back.

Last edited by JimJoe (2022-01-14 20:38:04)

JimJoe · 2022-01-14 22:54:41

I deleted and redid the site. About 3 more pages, 96 per page, of articles did import. 14 pages to 17 pages, the last one not a full 96.

But the only categories are the 3 that came with installation. And only the Publisher account I used to redo it, is there. There are others in the sql, but they didn’t import. I’ll worry about it on Saturday.

Last edited by JimJoe (2022-01-14 22:55:17)

JimJoe · 2022-01-15 02:00:46

My saved sql file is now 2.4 megabytes. There were two versions, different prefixes, in the sql file… so that might be where the rest of the data is located.

No usernames/accounts uploaded except for what the install created. No categories. Some articles still are missing.

I think I’ll do the rest by hand.

Thanks.

jakob · 2022-01-15 09:33:05

JimJoe wrote #332427:

Backup first.

Great, that way you can never be worse off than before you started!

Apparently the articles are not in the same order… in the two files …

Hmm, I’d assumed that unless you explicitly choose to export in another order, that each table is ordered by its primary key, which is the ID field for the textpattern table. But maybe not if you’re seeing other output. It is possible that the entire tables come out in different orders but you’re only interested in the ‘textpattern’ table, i.e. the articles.

I did get an error that says ‘#1050 – Table ‘drivein_textpattern’ already exists’.

and …

But the only categories are the 3 that came with installation. And only the Publisher account I used to redo it, is there. There are others in the sql, but they didn’t import.

It sounds like you’re trying to import whole database backups. You don’t want any of the other tables or any DROP or CREATE statements in there. Otherwise you’re going to overwrite or lose what you already had – which is why having a backup of the starting point, and maybe of intermediary successful stages, is so important!.

I really did mean just the INSERT statement for just the textpattern table, i.e.

INSERT INTO `textpattern` 
    ( … ), 
    ( … ), 
    … 
);

That way none of the other tables – users, categories etc. – are affected, not even the textpattern table itself, only the new rows within it.

You could do a new INSERT statement one by one for each database starting with the next new ID number, but I originally meant copying the additional missing rows from the successive documents into one larger SQL statement and then importing that.

BTW: If the textpattern table in your target database where you’re collecting all your articles has a prefix, you’re going to need to do INSERT INTO `drivein_textpattern`… or similar.

The order in which you have your IDs in the INSERT statement should not theoretically matter, but just makes it difficult to visually keep track of possible duplicate IDs. If you have duplicate ID numbers, the import will likely fail with a message telling you that you have a duplicate primary key. You can then go back and weed out the duplicate entry(entries).

The only problem I see is my older sql files have article numbers in the links, and I chose to not use article numbers this time.

That is potentially a problem. Some ideas on this front:

If you were using messy urls, e.g. index.php?id=123 then you’re in luck. They will still work without you having to change anything.
Rewrite mysite.com/section/123/article-url-title to mysite.com/section/article-url-title in your .htaccess file. For that you need a RewriteRule statement with a corresponding regex pattern. Regex101 is helpful in testing your pattern and htaccess.madewithlove.com can help in testing your pattern. With this variant, none of the original articles need to be changed.
It is also possible to write a tiny plugin that checks the incoming url for a part with just a number. It then either directs textpattern to that article id, or it removes that from the incoming url and passes the rest on for textpattern to handle as usual. Here too none of the original articles need changing.
If your previous article links were mysite.com/section/123/article-url-title and now they are mysite.com/section/article-url-title without the ID, one could also do a regex search and replace in the sql file to remove the /123 from those links. For that you should first do your article merging above until you have as much as you can bring together. Then export just the “textpattern” table with DROP TABLE and CREATE TABLE parts. You can choose to do that in phpMyAdmin. Then develop a regex pattern that works for all the url patterns you have (e.g. using regex101 above) and do a regex search and replace with your text editor (you usually have to flip a setting to switch to that method). That needs doing with care but once you have your regex pattern working correctly, you can search and replace all links at once. Once you’re sure it’s worked. Save the sql file again – Make sure you have a backup of the whole database again – and then reimport. This basically corrects all legacy urls in all old articles.

I can assist if needed but unfortunately not for a couple of weeks.

JimJoe · 2022-01-15 17:48:33

I did do the copy and paste additional articles into a copy of the backup. The entire sql filesize went from 8 megs to 10 megs.

However, when I export it from my domain to have a backup, its only 8 megs again.

I’ll look at the ‘INSERT INTO drivein’ you mentioned above. That is very likely part of the rest of it.

Some ID numbers are not there… Is there a way to reorder the ID numbers such that any ID numbers not being used are filled in ?

My current ID numbers skip all over the place. There is a 1, but nothing until about ID# 200. I would like to get them in a continuous sequence.

As for exporting, I just use the phpadmin in cpanel in my domain. The Import asks for filename, and what version of sql I am using. Click on button, and wait.

JimJoe · 2022-01-15 23:35:30

There must have been some junk files in the sql… after I killed the site and restarted it from scratch I have 17 pages of 96 articles which gives me 288 more articles than I had before. There were 14 pages of 96 articles, but page 14 of the articles list may not have been 96 entries.

The Export went from 8 megs down to 2.2 megabytes.

jakob · 2022-01-16 11:03:50

JimJoe wrote #332436:

There must have been some junk files in the sql… The Export went from 8 megs down to 2.2 megabytes.

In many cases, the culprit is a huge txp_log table containing many years of access logs.

JimJoe wrote #332435:

Some ID numbers are not there… Is there a way to reorder the ID numbers such that any ID numbers not being used are filled in ? My current ID numbers skip all over the place. There is a 1, but nothing until about ID# 200. I would like to get them in a continuous sequence.

It’s not a problem if there are gaps in the numbering. That happens normally, for example, when one deletes articles.

If you really want to re-order them, one way would be – at the end once you’ve finally got all your articles together – to export just the textpattern table to a .csv file, then renumber the ID column in a spreadsheet program, save and re-import. Note, however, that if you do have ID-based links in your articles’ text, the links may break. Also, exporting/importing CSV can lead to new problems with newlines and proper field delimitation. To be honest, I wouldn’t bother with renumbering the articles sequentially.

EDIT: you can probably renumber the ID column sequentially using a manual SQL statement in phpMyAdmin, but as the ID column is the table’s primary key, you may have to create a temporary extra column, number than, delete the primary key column, rename the temporary column and re-assign the primary key to it, then finally update the auto-increment to the most recent free id. It’s a lot of workaround to achieve neatness that only you will see in the ID# number list in the admin area.

JimJoe · 2022-01-16 15:36:45

I can live with it the way it is.

Yesterday I just searched for missing planet names. Add those back in, might save me some time.

JimJoe · 2022-01-18 22:11:55

Thanks Jakob !

I used the insert with drivein_ in there. I just did all of the articles… there was text i had typed in, and I wanted that in there to.

Planet numbers went from 1283 to 1829. Of course, I’ll still go through and double check…

JimJoe · 2022-01-19 17:00:41

I am finding duplicates, but that is okay. I just number them draft01 etc. I’ll use them later for new articles. Getting happy readers was my first goal.

Textpattern CMS

Textpattern CMS support forum

#61 2022-01-14 00:38:05

Re: my Traveller site, with some updates mentioned

#62 2022-01-14 00:51:30

Re: my Traveller site, with some updates mentioned

#63 2022-01-14 20:27:11

Re: my Traveller site, with some updates mentioned

#64 2022-01-14 22:54:41

Re: my Traveller site, with some updates mentioned

#65 2022-01-15 02:00:46

Re: my Traveller site, with some updates mentioned

#66 2022-01-15 09:33:05

Re: my Traveller site, with some updates mentioned

JimJoe wrote #332427:

#67 2022-01-15 17:48:33

Re: my Traveller site, with some updates mentioned

#68 2022-01-15 23:35:30

Re: my Traveller site, with some updates mentioned

#69 2022-01-16 11:03:50

Re: my Traveller site, with some updates mentioned

JimJoe wrote #332436:

JimJoe wrote #332435:

#70 2022-01-16 15:36:45

Re: my Traveller site, with some updates mentioned

#71 2022-01-18 22:11:55

Re: my Traveller site, with some updates mentioned

#72 2022-01-19 17:00:41

Re: my Traveller site, with some updates mentioned

Board footer