database design - storing rows order in mysql -


i need give ability change order of displaying rows script admin page.
there default order newly added rows (the go end of list) , admin should able change position of specific row.
i'm going act rows doubly linked list able re-position rows.

  • is ok use linked list method saving display position of mysql rows?
  • is there better method?
  • should use separate table store orders or ok add 2 next & prev columns original table?
  • is possibe use mysql order statement method?

edit: thought of using spaced order codes (e.g. 0, 100, 200, ...) has limit may reached

i think you'll better off storing ordering position in dedicated field, instead of trying implement linked list.

the issue linked list is requires sort of list traversal "reconstruct" order before can display user. normally, you'd employ recursive query that, unfortunately mysql doesn't support recursive queries, you'll either need fiddle stored procedures, or end-up making database round-trip each , every list node.

all in all, updating order field of several rows time time (when need reorder) cheaper traversing list every time (when need display it), if move rows small distancees. , if introduce gaps (as mentioned), number of rows you'll need update fall dramatically, @ price of increased complexity.

you may able piggy-back order field onto clustering mechanism offered innodb.

ymmv, of course, i'd advise benchmarking simple order field approach on representative amounts of data before attempting implement more sophisticated...


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more