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.
--
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
