Adding new categories in phpMyAdmin

aswihart · 2010-07-29 20:08:11

This is more of a MySQL question, but still relevant to Textpattern. I am first of all a complete newbie to MySQL so please forgive my ignorance. I have a ton of categories I need to enter into Textpattern, and to do it quickly, I was thinking I would directly enter them (with names, types, parents, and titles) into the txp_category table using phpMyAdmin. That table has the following structure, for reference:

	id	name	type	parent	lft	rgt	title

So, I tried this…

insert into txp_category values (NULL, 'test-one', 'article', 'some-existing-category', NULL, NULL, 'Test one');

And I got the following error message: #1048 - Column 'lft' cannot be null

I’m not sure that there is a way to figure out what the lft and rgt values are supposed to be, and I thought they might get automatically populated if I specified the category parent (I read that somewhere on the webs).

Any tips on getting this to work, or is there another method for rapidly entering categories?

Last edited by aswihart (2010-07-29 20:10:59)

aswihart · 2010-07-29 20:33:16

Quick update, I was able to succesfully enter a new category using the alternative syntax:

insert into txp_category (name, type, parent, title) values ('test-one', 'article', 'some-existing-category', 'Test one');

Then the lft and rgt columns automatically got values of 0. The new category did not show up in Textpattern until I manually entered what seemed like the next available numbers for lft and rgt, in this case, 20 and 21. Then the category showed up, but it wasn’t showing as a child of the category I had assigned as its parent. Clicking it to edit, I saw that it had the correct parent assigned to it, so I didn’t change anything and just hit save, and after I did that, the category showed up under its assigned parent, and the lft and rgt values had gotten automatically updated.

I think I’m almost there, but is there a better way / some tweaks to automate this whole process so that I can enter a ton of rows into the txp_category table, assigning parents to create a hierachy 4 or more levels deep, using phpMyAdmin?

aswihart · 2010-07-29 20:47:26

OK, I think I have it sorted out, I just need to specify sequential lft and rgt values for all the new categories I’m entering, then (at least one of) the new categories will get displayed inside Textpattern, and at that point I just have to click it, make no changes, and save it, and the lft and rgt values get magically updated to correct values based on the assigned parents. Awesome!

geoff777 · 2010-07-29 21:13:56

Hi Aswihart,

I noticed that Bloke (Stef) has very recently made a plugin that lets you create Categories on the fly from the Admin write tab.

I haven’t used it yet, but I had a quick look at its features, and it should help you avoid all that scary looking MySql …

smd_write_cats

Geoff

ruud · 2010-07-29 22:05:02

By editing/saving the category from within TXP, you triggered the rebuild_tree_full(‘article’) function, which updates the lft/rgt values for all categories. Normally TXP does that each time you add/remove/change a category. Doing it just once after first adding all categories through PHPmyAdmin is probably a lot more efficient, but by taking that route you are circumventing the checks that TXP normally performs when you add a category. If you add incorrect data in the txp_category table, you might end up with a corrupted category tree.

aswihart · 2010-07-29 22:10:52

Geoff – Thank you for your message. As incredibly useful as smd_write_cats is, I am dealing with a few hundred categories that need to be in a certain hierarchy, so I believe batch adding with SQL inserts is the way to go, especially now that I think I’ve got the method figured out!

*Ruud*— Thanks for the insight into what is happening here. Could you please define “incorrect data”. I am going to be careful to enter the “parent” and “name” with lower-case and dashes, is their some other formatting issue to be concerned with? And if I do trash the table, couldn’t I just delete all the added categories and bring the auto-increment value back to where it was and be back to where I started?

Last edited by aswihart (2010-07-29 22:11:34)

ruud · 2010-07-30 10:54:44

Each category should have a valid parent (‘root’ or some other category’) and the ‘name’ of a category should be properly formatted. If you consistently use alphabetic (lower case) with dashes. You can’t go wrong, really. Your table recovery plan sounds good.

If you created the category in TXP, the following things would be checked before adding the category:

the name is first lower-cased and sanitizeForUrl() is used on it.
the name must not be an empty value after sanitizeForUrl
the name can’t already exist (no duplicate category ‘name’ is allowed)
the above checks also apply to the parent you specify.

Textpattern CMS

Textpattern CMS support forum

#1 2010-07-29 20:08:11

Adding new categories in phpMyAdmin

#2 2010-07-29 20:33:16

Re: Adding new categories in phpMyAdmin

#3 2010-07-29 20:47:26

Re: Adding new categories in phpMyAdmin

#4 2010-07-29 21:13:56

Re: Adding new categories in phpMyAdmin

#5 2010-07-29 22:05:02

Re: Adding new categories in phpMyAdmin

#6 2010-07-29 22:10:52

Re: Adding new categories in phpMyAdmin

#7 2010-07-30 10:54:44

Re: Adding new categories in phpMyAdmin

Board footer