I have no idea why, but sometimes the categories and boards in the board index would not display as intended. Specifically, I had the most recently added category display after all the other categories, regardless of the specified sort order in the forum software. This wasn't working.
I solved the problem by changing a line in Sources/BoardIndex.php
AND b.childLevel <= 1" : ''), __FILE__, __LINE__);
to
AND b.childLevel <= 1" : '') . " ORDER BY c.catOrder,b.boardOrder ASC", __FILE__, __LINE__);
I hope this can be added to the next bug-fix release of 1.1. Thanks!
SMF does this when you edit boards by doing this query:
ALTER TABLE smf_boards
ORDER BY boardOrder
If it didn't happen then either mysql is disallowing it or the script isn't getting that far due to a modification preventing it from getting this far.
Quote from: SleePy on December 24, 2007, 01:02:16 PM
SMF does this when you edit boards by doing this query:
ALTER TABLE smf_boards
ORDER BY boardOrder
If it didn't happen then either mysql is disallowing it or the script isn't getting that far due to a modification preventing it from getting this far.
What would cause the categories to be out of order then? Even if I manually run
ALTER TABLE `smf_categories` ORDER BY `catOrder`
ALTER TABLE `smf_boards` ORDER BY `boardOrder`in MySQL, I still end up with categories/boards showing in the wrong order. I have no modifications installed.
However, the results of
SELECT * FROM `smf_boards`and
SELECT * FROM `smf_boards` ORDER BY `smf_boards`.`boardOrder` ASCare in a different order.
I'm using InnoDB tables, MySQL 5.0.24a-standard-log, with utf8_unicode_ci collation throughout.
So perhaps this is a MySQL bug? In any event, the sort takes next to no time for MySQL to run, so I don't see the harm in having an ORDER BY clause.
I wonder if it is a mysql bug. What version of mysql are you using?
What happens if you convert the smf_boards and smf_categories back to myisam?
Quote from: SleePy on December 24, 2007, 03:54:58 PM
I wonder if it is a mysql bug. What version of mysql are you using?
MySQL 5.0.24a as stated above.
QuoteWhat happens if you convert the smf_boards and smf_categories back to myisam?
Converting back, then running ALTER TABLE .. ORDER BY .. fixes the problem. It appears that InnoDB is not honouring ALTER TABLE .. ORDER BY.
I converted my smf_boards and smf_categories on a test install of SMF. It did restore its order. But I was able to edit it back to the way it should be.
I did report this as it seems this could be an issue that would make people using Big Boards and converting over to InnoDB for what ever. For team Reference the bug is 1165
InnoDB does not support ALTER TABLE ORDER BY.
It was done that way to make the query that little bit more efficient, although as people are finding out, it causes issues when the table is InnoDB.
It also causes a sorting issue when you're moving posts, too.
Also, the sort order appears wrong in the Move Topic function when using InnoDB tables. This can be fixed changing line 95 in MoveTopic.php from
AND $user_info[query_see_board]" /* . (!in_array(0, $boards) ? "
to
AND $user_info[query_see_board] ORDER BY c.catOrder,b.boardOrder ASC" /* . (!in_array(0, $boards) ? "
This should really be fixed for the next release of 1.1 and 2.
Also $context['jump_to'] seems to be affected. My proposed fix:
Load.php
replace
WHERE $user_info[query_see_board]", __FILE__, __LINE__);
with
WHERE $user_info[query_see_board] ORDER BY c.catOrder,b.boardOrder ASC", __FILE__, __LINE__);
in function loadJumpTo()