Categories disappear in phpld

So you’ve been busy adding a new category in phpld directory software and other categories have disappeared – what’s gone wrong? Are you at the category limit in phpld? Well the simple answer is ‘no’ – you haven’t reached the category limit in phpld (I’m not sure that there is one).

What’s happened is probably due to one of three things – either you have previously bulk imported a list of categories, uploaded a database from an older version without updating the appropriate tables, or you have at some stage upgraded from a previous version and it hasn’t quite worked. The latter is usually due to the directory owner taking a few shortcuts.

Either way, you have a problem – some categories (or a category) disappears. You may even have realised it is something to do with PLD_CATEGORY_SEQ.

Any webmaster worth his salt would of course, Google for a fix, and that’s where you might run into some problems. You may have come across this:

CREATE TABLE IF NOT EXISTS `PLD_CATEGORY_SEQ` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

update PLD_CATEGORY_SEQ
set id = (select count(id)+1 from PLD_CATEGORY);

Y’see, that wont work properly. In theory it looks correct and making the table ‘auto-increment’ while perhaps a good idea in some respects, is incorrect in so far as it isn’t how phpld sets up the table and also, even if you fiddled around with it and got the query working, it will input the wrong value! The logic is sound but it doesn’t address the actual problem of disappearing categories because it only works if the categories have been entered correctly in the first place (and therefore the category numbers match the row numbers) and since you have categories disappearing, it isn’t going to do you any good because the category numbers wont match with the count!

What’s required is this:

CREATE TABLE IF NOT EXISTS `PLD_CATEGORY_SEQ` (
`id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO PLD_CATEGORY_SEQ
set id = (SELECT id
FROM PLD_CATEGORY
ORDER BY ID DESC
LIMIT 1)+1;

What this mysql query does is create the table if it isn’t there already (note that it doesn’t auto-increment and doesn’t have a primary key – this is how it is set up in phpld v3.3 and phpld v3.4); although as you have disappearing categories the table should already exist; then it finds the highest category number, adds one to it (not strictly necessary but it makes certain the next category number is at least one more than the existing highest category number), and updates the category count table. You’re good to go! No more disappearing categories in phpld!

It’s easiest to run this query from phpmyadmin. Simply go to the top level of your phpld database – it should say something like this at the top: Server: localhost Database: username_phpld.

Select ‘SQL’ from this range of options: Structure SQL Search Query Export Import Operations, copy and paste the above query and hit ‘Go’