Scriptaculous and Perl

typomaniac · 03-13-2012, 12:57 AM

Hopefully I'm in the right forum here but not sure.
I'm building a photo gallery and using perl/mysql. I was looking for a way to reorder image listings and happened across the scriptaculous scripts and they had exactly what I'm looking for--that is where re-ordering is a matter of dragging a list entry to another spot.
Getting the values out of the database for building the list is no problem but don't have a clue how to put the new order back in. I know it has to be a set statement, no problem there. I was going to build the db columns in the way of an auto incremented column for matching table cell or div id's, a column with the name of the image and a column with the order numbers in it for making the pic show up in the right location on the page.
I was able to get the scriptaculous script running but don't know how to get the new order in the db set.
At this point the only thing I have to go on is:

Code:

#!/usr/bin/perl
use strict;
use CGI qw(:standard);
use DBI;
print"Content-type:text/html\n\n";
use CGI::Carp qw(fatalsToBrowser);


# PERL MYSQL CONNECT
#$dbh = DBI->connect('dbi:mysql:gallery:localhost','root','*****') or die('Couldnt connect');

#$sth=$dbh->prepare("select * from test");
#$sth->execute();
#while($ref=$sth->fetchrow_hashref()){
#print"            <option value=\"$ref->{'office_id'}\">$ref->{'office_name'}</option>\n";
#            }

print qq~
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <title>script.aculo.us sortable functional test file</title>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <script src="http://127.0.0.1/jscript/lib/prototype.js" type="text/javascript"></script>
  <script src="http://127.0.0.1/jscript/src/scriptaculous.js" type="text/javascript"></script>
  <script src="http://127.0.0.1/jscript/src/unittest.js" type="text/javascript"></script>
  <link rel="stylesheet" href="http://127.0.0.1/css/test.css" type="text/css" />
  <style type="text/css" media="screen">
  ul.sortablelist {
    list-style-image:none;
    list-style-type:none;
    margin-top:5px;
    margin:0px;
    padding:0px;
  }

  ul.sortabledemo li {
    padding:0px;
    margin:0px;
  }

  span.handle {
    background-color: #E8A400;
    color:white;
    cursor: move;
  }

  li.green {
    background-color: #ECF3E1;
    border:1px solid #C5DEA1;
    cursor: move;
  }

  li.orange {
    border:1px solid #E8A400;
    background-color: #FFF4D8;
  }
  p.inv{
  display:none;
  }
  </style>
</head>
<body>
<h1>script.aculo.us: Two floating sortables with containment and dropOnEmpty</h1>

<div style="height:200px;">
<div style="float:left;">
<h3>This is the first list</h3>
 <ul class="sortablelist" id="firstlist" style="height:150px;width:200px;">
~;
my $dbh = DBI->connect('dbi:mysql:cwk:localhost','root','') or die('Couldnt connect');
my $sth=$dbh->prepare("select * from officers");
my $ref;
$sth->execute();
while($ref=$sth->fetchrow_hashref()){
print qq~
            <li class="green" id="$ref->{'office_id'}">$ref->{'nick'}</li>
~;            

#foreach($_GET['item'] as $key=>$value) {
#mysql_query("UPDATE my_items" SET position = '" . $key . "' WHERE id ='" . $value . "'");
#}
}
            
print qq~            
 </ul>
</div>
 <div style="float:left;">
 <h3>And now the second list</h3>
 <ul class="sortabledemo" id="secondlist" style="height:150px;width:200px;">
   <li class="orange" id="secondlist_secondlist1">
     <span class="handle">DRAG HERE</span> Item 1 from second list.
   </li>
   <li class="orange" id="secondlist_secondlist2">
     <span class="handle">DRAG HERE</span> Item 2 from second list.
   </li>
   <li class="orange" id="secondlist_secondlist3">
     <span class="handle">DRAG HERE</span> Item 3 from second list.
   </li>
 </ul>
</div>
</div>

<hr style="clear:both" />
<p class="inv">hhh
<pre id="firstlist_debug"></pre>
<pre id="secondlist_debug"></pre>
</p>
~;
print" <script type=\"text/javascript\">\n";
print" // <![CDATA[\n";
print"   Sortable.create(\"firstlist\",\n";
print"     {dropOnEmpty:true,containment:[\"firstlist\",\"secondlist\"],constraint:false,\n";
print"      onChange:function(){\$.innerHTML = Sortable.serialize(\'firstlist\') }})\;\n";
print"   Sortable.create(\"secondlist\",\n";
print"     {dropOnEmpty:true,handle:\'handle\',containment:[\"firstlist\",\"secondlist\"],constraint:false,\n";
print"     onChange:function(){\$(\'secondlist_debug\').innerHTML = Sortable.serialize(\'secondlist\') }})\;\n";
print" // ]]>\n";
print" </script>\n";
print qq~ 
 </body>
 </html>
~;

In the example it lists office names and id's but in the script it would be referring to images. Any ideas on this one?

felgall · 03-13-2012, 02:07 AM

Databases don't store things in order - they rely on the ORDER BY on the select statement to specify the order.

So to change the order on retrieval to a specific on set earlier you'd need to UPDATE each and every record to replace the value of the field that you are using to define the order.

FishMonger · 03-13-2012, 02:22 AM

Cross posted on devshed