> -----Message d'origine----- > De : Richard Lynch [mailto:ceo@xxxxxxxxx] > Envoyé : jeudi 1 février 2007 02:35 > À : Tim > Cc : php-general@xxxxxxxxxxxxx > Objet : Re: Manipulating "categories" in a mysql database > > On Tue, January 30, 2007 12:36 pm, Tim wrote: > > This is more of a conceptional question rather then a technical one. > > > > I am developping an online catalog, the catalog is a standard > > categorie/subcat/subsubcat system using the following logic in my > > database: > > > > cat_id cat_name > > > > 10 DVD > > 1010 DVD/Action > > 1020 DVD/Adventure > > 20 CD > > 2010 CD/Reggae > > 2020 CD/Electro > > > > Up to now I enter the category id's manually from an administration > > console. > > > > I would like to be able to have my console automagically generate a > > cat_id > > when my users add categories (so they don't have to bother with that > > aspect > > of adding a category) and I would also like to be able to move a > > category up > > or down in the way it displays on the page, as of now I am sorting by > > cat_id > > Rule of thumb for SQL: > Never ever ever sort by *_id, because it will almost alway send up > biting you in the butt sooner or later. > > Add a 'rank' column and maintain that in your web application. > > > I have been for a while trying to develop a logical scheme to be able > > to > > generate the id's and change their cat_id to reflect a "new" position > > in how > > it displays (displaying DVD/Action after DVD/Adventure for example I > > would > > need to change the cat_id from 1010 to something above 1020 say 1021). > > This > > is fine as I can test the previous elements cat_id and add or subtract > > 1 but > > I get stuck when I have a cat_id say 1010 that I would want to put > > between > > 1011 and 1012. I'm having trouble developing the algorithm that would > > allow > > this.. > > Once you separate the cat_id and the 'rank' you will have no problems. > > Your cat_id can just be auto_increment and never displayed to the user. > > Your 'rank' can be a simple integer, and to re-order you'll just do like: > > <?php > //Assume cat_id # 7 needs to move to the 42nd place in the list: > $cat_id = 7; > $new_rank = 42; > $query = "update catalog set rank = rank + 1 where rank >= $new_rank"; > mysql_query($query) or error_log(mysql_error() . " $query"); > $query = "update catalog set rank = $new_rank where cat_id = $cat_id"; > mysql_query($query) or error_log(mysql_error() . " $query"); > ?> > > This may create a "hole" in the rank listing, if $cat_id 7 used to be > LOWER than 42. > > A little bit more effort can solve this, however. > AFTER the above queries, tack on: > if ($old_rank < $new_rank){ > $query = "update catalog set rank = rank - 1 where rank > $old_rank"; > mysql_query($query) or error_log(mysql_error() . " $query"); > } > > You could go even further and change the minimum number of ranks > needed in any given change, to avoid thrashing the index on the rank. > This would be crucial if your table was very large. Thanks a lot, that put me in the right direction, I am studying this option as I write this, I have drawn it up in a diagram and it looks quite simple now. I have also discovered a flaw in my database programming that made me realize how badly I use the MySQL engine by doing the sorting at the application-layer level rather then using the MySQL engine to sort the info prior to sending back to the application.. Have now discovered, indexing, foreign keys, and joins :D Great list, always opening new doors :) Thank you much for all your suggestions! Regards, Tim > -- > Some people have a "gift" link here. > Know what I want? > I want you to buy a CD from some starving artist. > http://cdbaby.com/browse/from/lynch > Yeah, I get a buck. So? > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
