daithi Posted July 10, 2003 Share Posted July 10, 2003 Hello, I am using the following SQL to create a dropdown menu of my categories with the current category of a product selected - the only problem is that the first category does not show in the dropdown, so if I update a product in the first category it goes into the second! - any ideas on why the first category is not showing? this is urgent as it is happening on a live site where the user can update products...any help appreciated. $sql3 = mysql_query("SELECT categories_id, categories_name FROM categories_description ORDER BY categories_name"); $categorize = mysql_fetch_array($sql3); <?php if(mysql_num_rows($sql3)) { while($row = mysql_fetch_row($sql3)) { if ( $row[0] == "$locoo" ) { print("<option selected value="$row[0]">$row[1]</option>"); } else { print("<option value="$row[0]">$row[1]</option>"); } } } ?> Link to comment Share on other sites More sharing options...
Guest Posted July 10, 2003 Share Posted July 10, 2003 Try a query along the lines of: $parent_category_id = 0; // Update this to reflect current category before querying the DB $sql3 = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent_category_id . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); I based this query on include/boxes/categories.php. Hope this helps, Rob Link to comment Share on other sites More sharing options...
daithi Posted July 10, 2003 Author Share Posted July 10, 2003 thanks, that worked a treat! Link to comment Share on other sites More sharing options...
daithi Posted July 12, 2003 Author Share Posted July 12, 2003 $parent_category_id = 0; // Update this to reflect current category before querying the DB $sql3 = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent_category_id . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); ok, so now i am using the above code and it works great, but does anyone have an idea how to have my sub categorys listed in the dropdown under their main category? I currently have the dropdown sorted by name, but if i add a new subcategory it fits into the dropdown menu alphabetically, what I want is for the list to be sorted alphabetically but have the sub categories displayed under their main category - something like: Computers --Hardware --Software Electrical --any item and so on, any ideas? Link to comment Share on other sites More sharing options...
daithi Posted July 12, 2003 Author Share Posted July 12, 2003 I just noticed that the code i need is in admin/categories.php when moving an item - but not sure how to use it - any ideas please? $contents[] = array('text' => '<br>' . sprintf(TEXT_MOVE, $cInfo->categories_name) . '<br>' . tep_draw_pull_down_menu('move_to_category_id', tep_get_category_tree('0', '', $cInfo->categories_id), $current_category_id)); Link to comment Share on other sites More sharing options...
daithi Posted July 12, 2003 Author Share Posted July 12, 2003 i have given up on the query from admin/categories.php so back to the original: $parent_category_id = 0; // Update this to reflect current category before querying the DB$sql3 = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent_category_id . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); This query only gives me a menu of the top level categories - removing the c.parent_id = '" . (int)$parent_category_id . "' - gives me a list of all categories in alphabetical order, but i still need the sub cats to show under their parent cat - should be simple but its recking my head... Link to comment Share on other sites More sharing options...
Guest Posted July 12, 2003 Share Posted July 12, 2003 you could group by parent_id in the sql statement or you could sort the array results by parent_id after the query has been run Link to comment Share on other sites More sharing options...
daithi Posted July 12, 2003 Author Share Posted July 12, 2003 you could group by parent_id in the sql statement or you could sort the array results by parent_id after the query has been run Hi, I have tried lots of different combinations but still can't get it right. Can you show a statement that you think would work? Link to comment Share on other sites More sharing options...
Guest Posted July 12, 2003 Share Posted July 12, 2003 i assume you have tried the built in function i.e. tep_draw_pull_down_menu('cPath', tep_get_categories(array(array('id' => '', 'text' => PULL_DOWN_DEFAULT))), $cPath, 'onchange="this.form.submit();"') Link to comment Share on other sites More sharing options...
daithi Posted July 12, 2003 Author Share Posted July 12, 2003 i assume you have tried the built in function i.e. tep_draw_pull_down_menu('cPath', tep_get_categories(array(array('id' => '', 'text' => PULL_DOWN_DEFAULT))), $cPath, 'onchange="this.form.submit();"') Yeah, I tried that, but it just comes up blank! Link to comment Share on other sites More sharing options...
daithi Posted July 14, 2003 Author Share Posted July 14, 2003 sorry posted in the wrong topic earlier, so here goes again... has anyone any ideas on this? the site has now gone live but i need to fix the drop down menu for when a user wants to submit an item. Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2003 Share Posted July 14, 2003 If you can afford the performance hit of a few extra hits on your db, you could just hit the database again for each category found after updating the $parent_category_id value. If this is a problem, you could look into using retreving all categories regardless of parent_id and use a custom usort() to sort your list. BTW: I saw someone mentioned a standard routine in osC to do this, that would probably be the preferred method, but sadly I don't know what functionality is referred to. (See sig for reason why) Hope this helps, Rob Link to comment Share on other sites More sharing options...
daithi Posted July 14, 2003 Author Share Posted July 14, 2003 not a problem if i knew how to do it and create the menu with the results... Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2003 Share Posted July 14, 2003 not a problem if i knew how to do it and create the menu with the results... What would be not a problem? The first or second strategy? Link to comment Share on other sites More sharing options...
daithi Posted July 14, 2003 Author Share Posted July 14, 2003 either way, i just need to get it done... Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2003 Share Posted July 14, 2003 The second way is the easiest: Again untested: function print_categories( $parent_category_id = 0, $languages_id ) { $sql = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent_category_id . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); if ( mysql_num_rows($sql) ) { while($row = mysql_fetch_row($sql)) { if ( $row[0] == "$locoo" ) { print("<option selected value="$row[0]">$row[1]</option>"); } else { print("<option value="$row[0]">$row[1]</option>"); } print_categories( $row[2], $languages_id); } } } $parent_category_id = 0; // Update this to reflect current category before querying the DB print_categories(); If this code works straight away, you're gonna have to buy me a beer if I ever get in your neighbourhood. But don't worry the chances of both are probably very remote. This is an ugly hack, but I don't really have that much time atm. It hits your database twice for each category, so this probably won't scale well. HTH, Rob Link to comment Share on other sites More sharing options...
daithi Posted July 14, 2003 Author Share Posted July 14, 2003 Cryp2Nite, thanks for all your help with this, but again no joy. using the code you posted as it i get: Missing argument 2 for print_categories() this dissapears if I remove $languages_id from function print_categories( $parent_category_id = 0, $languages_id ) but the menu still doesn't show.... Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2003 Share Posted July 14, 2003 Ok, I ddin't expect it to work straight away, as I am sloppy and in a hurry ;-) Try this: function print_categories( $lang_id, $parent = 0 ) { $sql = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$lang_id ."' order by sort_order, cd.categories_name"); if ( mysql_num_rows($sql) ) { while($row = mysql_fetch_row($sql)) { if ( $row[0] == "$locoo" ) { print("<option selected value="$row[0]">$row[1]</option>"); } else { print("<option value="$row[0]">$row[1]</option>"); } print_categories( $row[2], $languages_id); } } } $parent_category_id = 0; // Update this to reflect current category before querying the DB print_categories( $languages_id ); Link to comment Share on other sites More sharing options...
daithi Posted July 14, 2003 Author Share Posted July 14, 2003 still no joy, no sql errors this time and the drop down does show - but only shows the parent categories.... Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2003 Share Posted July 14, 2003 Can you send me the complete file, I'll have a look at it later Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2003 Share Posted July 14, 2003 still no joy, no sql errors this time and the drop down does show - but only shows the parent categories.... Sorry, my bad,... again. I forgot to change: print_categories( $row[2], $languages_id); to: print_categories( $lang_id, $row[2] ); Link to comment Share on other sites More sharing options...
daithi Posted July 14, 2003 Author Share Posted July 14, 2003 sorry this is going on so long and thanks again for all your help. after making the last change, the function now looks like this. <?php function print_categories( $lang_id, $parent = 0 ) { $sql = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$lang_id ."' order by sort_order, cd.categories_name"); if ( mysql_num_rows($sql) ) { while($row = mysql_fetch_row($sql)) { if ( $row[0] == "$locoo" ) { print("<option selected value="$row[0]">$row[1]</option>"); } else { print("<option value="$row[0]">$row[1]</option>"); } print_categories( $lang_id, $row[2] ); } } } $parent_category_id = 0; // Update this to reflect current category before querying the DB print_categories( $languages_id ); ?> This creates a long, long drop down with nothing but the first category in it... Link to comment Share on other sites More sharing options...
Guest Posted July 15, 2003 Share Posted July 15, 2003 Here you go, I even tested it first this time! <?php include('includes/application_top.php'); ?> <select name="categoies_id_form"> <?php $languages_id = 1; // Be sure to set languages id to something sensible function print_categories( $lang_id = 1, $parent = 0, $prepend_char = '-' ) { $sql = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$parent . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$lang_id ."' order by sort_order, cd.categories_name"); if ( mysql_num_rows($sql) ) { while($row = mysql_fetch_row($sql)) { // Display category and call myself for sub categories if ( $row[0] == "$locoo" ) { // Note: I don't know what locoo is or where it comes from print("<option selected value="$row[0]">$row[1]</option>"); } else { print("<option value="$row[0]">$row[1]</option>"); } print_categories( $lang_id, $row[0]); } } } print_categories( $languages_id ); ?> </select> Have fun with it, I'm done for the day. Good luck, Rob Link to comment Share on other sites More sharing options...
daithi Posted July 15, 2003 Author Share Posted July 15, 2003 You are a daring, thank you! If you are ever in Ireland, that pint and many others are waiting... one last question - do you know how I would indent the sub categories? Link to comment Share on other sites More sharing options...
Guest Posted July 15, 2003 Share Posted July 15, 2003 You are a daring, thank you!If you are ever in Ireland, that pint and many others are waiting... I don't know if that's an invitation or a threat, as I have some rather unpleasant memories of a morning after a night of Irish booze. ;-) one last question - do you know how I would indent the sub categories? I knew this one was coming, but I was too lazy to be bothered last night. If you only wan't to indent all sub cats the same amunt I guess you could do while($row = mysql_fetch_row($sql)) { var $indent_chars = ""; if ( row[2] != 0 ) { $indent_chars = "--"; } if ( $row[0] == "$locoo" ) { // Note: I don't know what locoo is or where it comes from print("<option selected value="$row[0]">$row[1]</option>"); } else { print("<option value="$row[0]">$indent_chars $row[1]</option>"); } print_categories( $lang_id, $row[0]); } If you have more than one level of subsategories and want more than one level of indentation something a little more clever is required. Link to comment Share on other sites More sharing options...
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.