SQL Query Lessons: LKeys this time

Building a new module for D6 (then we'll go into D7 with it, but for now D6) and looking at adding into the DB a few things.  Okay, 7 New Tables, 2 Admin Menu Items, 1 G1Params & 96 friggin' Language Keys. 

I know for this I need a new Localization Category for the strings, then I need to drop the LocalizationKeys & finally LocalizationStrings to get all of this go smoothly.  Thanks to Deano in a previous post I know that I can do the following as an example of how to input in the AdminMenu:

SET @iOrder = (SELECT MAX(`order`) FROM `AdminMenu` WHERE `Categ` = '5');
INSERT INTO `AdminMenu` (`ID`, `Title`, `Url`, `Desc`, `Check`, `Order`, `Categ`, `Icon`) VALUES(NULL, 'Mod Name Settings', 'global_settings.php?cat=24', 'mod name feature management: notification emails, the thumbs size, etc.', '', @iOrder, 5, 'modname_settings.gif');

What we don't want is for the:

'global_settings.php?cat=24'

To start with.  That is the cat for Groups, and while this is similiar we need a whole new one for this module that will allow it to be administered correctly from the Admin Panels Settings Page.  So, do I first need to copy cat=24 from AdminMenuCategories and set it into the DB with the new settings and if so how do I get it's cat=# to go to the string that is above this? 

2nd question of the day, this should be fun also.  Since we're adding in 96 LKeys, I know the traditional way of doing this is to create the Language Categorization (which raises the question from above) of gaining the CAT# and then using that in the LKey & Strings.

So, how do we get all of this run to smoothly in an SQL file?  I know that I can just do it by hand in the DB (gawd would that take forever) or I can go in and create the necessary categories by hand and then input them real quick into a long SQL for the Keys & Strings.  But I'm looking for the super easy way that makes the whole thing automated.  This is for a really large free module (don't see a ton wanting it, but many may want it) and it is a freebie like all the stuff for those who can use it.

So, how about a little hand here please.

Insert new Catagory.

INSERT INTO `AdminMenuCateg` (`Title`, `Order`, `Icon`, `Icon_thumb`, `User`) VALUES ('CatTitle', 0, 'CatIcon.png', 'CatIconThumb.png', 'admin');

Grab the last insert ID which will be your catagory ID.

SET @iCategId = (SELECT LAST_INSERT_ID());

Now just use that in the query to insert the Menu item

SET @iOrder = (SELECT MAX(`order`) FROM `AdminMenu` WHERE `Categ` = @iCategId);

EDIT: Actually that above line is pointless. If your using a new catagory then obviously nothing matching will be found so your order should start at 1.

INSERT INTO `AdminMenu` (`ID`, `Title`, `Url`, `Desc`, `Check`, `Order`, `Categ`, `Icon`) VALUES(NULL, 'Mod Name Settings', CONCAT('global_settings.php?cat=',+@iCategId), 'mod name feature management: notification emails, the thumbs size, etc.', '', @iOrder, @iCategId, 'modname_settings.gif');




As for adding the language keys. That is normally done from a php script using the function addStringToLanguage($langKey, $langString, $langID, $categoryID)

The more and more I look at this to apply it over to other parts the worse it gets on my end.  The crazy part is I can do this easily (I know, that's cheating) in PHPMyAdmin. 

Okay, so to get the global_setings.php?cat=24; to appear in the correct place in the AdminMenu that we just did, we first need to set the G1ParamsKateg & then insert the correct injection into the G1Params.

So, to create the G1ParamsKateg we need:

SET @iOrder+1 WHERE (I'm pretty sure I want the menu_order here)

INSERT INTO ``.`GlParamsKateg` (`ID`, `name`, `menu_order`) VALUES (NULL, 'Mod Name', @iOrder);

SET @iMenu_Order = (SELECT LAST_INSERT_ID());
INSERT INTO `GlParams` (`Name`, `VALUE`, `kateg`, `desc`, `Type`, `check`, `err_text`, `order_in_kateg`) VALUES ('Mod Name', '', '', 'Manage your Mod Name Here', 'digit', '', '', @iMenu_Order);

I know that's wrong, and I've got 3 or 4 to put into g1params.  I'm really trying here, not used to this and just tryin' to get my mind wrapped around all of it.  I do appreciate the help.  Do I need the first SET @iOrder+1 and what in the world am I setting at iOrder+1.  This is part of where I'm getting lost if I do need it.  I don't know the ID or the menu_order on it yet.  And the 2nd is just plain old butchered.

i'll look into this some more later. I need to get by D6 test site fixed so i can play around a bit. See what i can figure out.

How about if you play on one of my D6 test sites.  This is a pretty large item overall, as you've guessed I'm sure.  Love the input on this thing.  Off to do another thread on the next issue we're having with it.  Guess I'll have to share what we're doing overall with this thing there.

What if I do it like this?

<?

require_once( 'inc/header.inc.php' );
require_once( BX_DIRECTORY_PATH_INC . 'design.inc.php' );
require_once( BX_DIRECTORY_PATH_INC . 'admin_design.inc.php' );
require_once( BX_DIRECTORY_PATH_INC . 'languages.inc.php' );
require_once( BX_DIRECTORY_PATH_INC . 'utils.inc.php' );

echo "START .....<br /><br />";

echo "UPDATING DATABASE .....<br /><br />";
 

 
$maxOrder = db_value("SELECT max(`menu_order`) FROM `GlParamsKateg`");
$maxOrder += 1;

db_res("INSERT INTO `GlParamsKateg` (`name`, `menu_order`) VALUES ( 'Class Rooms', $maxOrder );");
$iParamsCategId = mysql_insert_id( );
 
db_res("
INSERT INTO `GlParams` VALUES ('clroom_img_width', '600', $iParamsCagegId, 'Gallery max image width', 'digit' '', '', 1);
('clroom_img_height', '600', $iParamsCategId, 'Gallery max image height', 'digit', '', '', 2),
('clroom_img_tmb_width', '100', $iParamsCategId, 'Gallery max image thumb width', 'digit, '', '', 3),
('clroom_img_tmb_height', '100', $iParamsCategId, 'Gallery max image thumb height', 'digit', '', '', 4),
('clroom_invitation_text', '<b>{sender}</b> has invited you to join <b>{clroom}</b>.<br />\r\nClass Rooms allow users to communicate on the forums on interesting topics, share pictures, ideas, problem solving skills and so on.<br />\r\nYou may accept or refect this invitation below:<br />\r\n{accept}&nbsp; &nbsp; &nbsp; {refect}</b>', $iParamsCategId, 'Class Room invitation text', 'text', '', '',5)
");

$iCategId = mysql_insert_id( );
$maxOrder = db_value("SELECT max(`order`) FROM AdminMenu");
$maxOrder += 1;
 
db_res("
INSERT INTO `AdminMenu` (`Title`, `Url`, `Desc`, `Check`, `Order`, `Categ`, `Icon`) VALUES('Class Rooms', 'clrooms.php', 'Here you are able to manage class rooms and its categories', '', $maxOrder, $iCategId, 'tags_settings.gif'),
('Class Room Settings', concat('global_settings.php?cat=', $iParamsCategId), 'Class Room feature management: notification emails, the thumbs size, etc.', '', $maxOrder, $iParamsCategId, 'clrooms_settings.gif')
");
 
echo "SUCCESSFULLY UPDATED DATABASE ..... <br /><br />";

echo "FINISHED <br />";

?>

Now, before I fire this off and screw up the DB, I'm takin' a moment to ask if this is right or if it's got some errors in it.  Goin' a little further with this than I usually go.  But from what I can see this makes sense to me as the way to do it.  Also sure I have an error here somewhere, but this is the best I see so far.