Solved

compair two arrays and take action

Posted on 2014-01-23
5
275 Views
Last Modified: 2014-02-23
Hi Expert,

I'm trying to build an interface in which I can quickly generate web forms. I've got three database tables. One with documents one with items (questions of different types) and a join table to administrate the fields that are used in a document (web form).

In my drag and drop interface I've got two columns one (the left one)  presenting all possible items and the right one presenting the items that populate the chosen document (in this example document "1").

I succeeded building this functionality partly.
With this interface I can now change the order of the items and add new items to a document.
But I'd like to be able to remove items as well.

And this is What I can't get working right.
I've got two arrays to work with.
One holding the values of the created new order coming from interface
And one holding the values that presents the current administrated situation.

Like I said I did make it work for the adding and reordering part but not for deletion.

This is what I've got:

The interface file:

<!--#INCLUDE FILE="config.asp"-->
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>jQuery UI Sortable - Handle empty lists</title>
  <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
  <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
  <script src="http://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css">
  <style>
	#sortable1, #sortable2 { list-style-type: none; margin: 0; padding: 0 0 2.5em; float: left; margin-right: 10px; }
  #sortable1 li, #sortable2 li { margin: 0 5px 5px 5px; padding: 5px; font-size: 1.2em; width: 420px; }
  </style>
  <script>
  $(function() {
    $( "#sortable1, #sortable2" ).sortable({
      connectWith: ".connectedSortable",
        update : function () {
            //var order = $('#sortable2').sortable('toArray').toString();
			var order = $('#sortable2').sortable('serialize'); 
		
            //alert(order);
			$("#info").load("update-order.asp?"+order+"&documentID=1");
				}
    }).disableSelection();
  });
  </script>
</head>
<body>
<div id="sortable"> 
<ul id="sortable1" class="connectedSortable">
<%
SQL = "SELECT top (15) fieldtypeID, label FROM entree_items where fieldtypeID not in (select itemID from entree_ItemPerDocument WHERE documentID='1') ;"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, cs ,3,3
Do While Not RS.EOF
	response.write "<li class=""ui-state-default"" id='item_"&RS("fieldtypeID")&"'>"&RS("label")&"</li>"& vbcrlf 
	RS.MoveNext 
LOOP
%>
</ul>
 
<ul id="sortable2" class="connectedSortable">
<%
SQL = "SELECT top (15) entree_ItemPerDocument.itemID, entree_items.label FROM entree_ItemPerDocument inner join entree_items ON entree_items.fieldtypeID=entree_ItemPerDocument.itemID WHERE documentID='1' ORDER BY sorting"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, cs ,3,3
Do While Not RS.EOF
	response.write "<li class=""ui-state-highlight"" id='item_"&RS("itemID")&"'>"&RS("label")&"</li>" & vbcrlf 

	RS.MoveNext 
LOOP
%>
</ul>
</div> 
<br style="clear:both">
<div id="info"></div> <!--This receives the message from the ASP page -->
</div>
</body>
</html>

Open in new window


And the machine part of the functionality:

<!--#INCLUDE FILE="config.asp"-->
<%
dim sql : sql = "SELECT itemID FROM entree_ItemPerDocument WHERE documentID='1' ORDER BY sorting"
dim rs : set rs = cs.execute(SQL)
dim arr : arr = rs.GetRows()
dim curItemID : curItemID = ubound(arr,2)

getItems = Request.QueryString("item[]")
documentID = Request.QueryString("documentID")
getItems = Split(getItems, ",")

for i = LBound(getItems) TO UBound(getItems) ' get the newly wanted items
	InArray = 0
	dim y
	for y = 0 to curItemID ' get the current items
        if Trim(getItems(i)) = Trim(arr(0,y)) then  'compair them and if they are equal :
			InArray = 1 
		end if
	next
	if InArray = 1 then 
		sql_update = "UPDATE entree_ItemPerDocument SET sorting= " & i & " WHERE documentID ='"&documentID&"' AND itemID='" & Trim(getItems(i)) & "'"
	else
		sql_update = "INSERT INTO entree_ItemPerDocument (documentID,itemID,required,sorting) VALUES('"&documentID&"','"&getItems(i)&"','0','"&i&"')"
	end if
	response.write sql_update & "<br>"
Next
%>

Open in new window


The output from the machine part is redirected in to the interface file and looks like this if I drag and drop one item from the left column to the right one:

UPDATE entree_ItemPerDocument SET sorting= 0 WHERE documentID ='1' AND itemID='7'
UPDATE entree_ItemPerDocument SET sorting= 1 WHERE documentID ='1' AND itemID='8'
UPDATE entree_ItemPerDocument SET sorting= 2 WHERE documentID ='1' AND itemID='9'
UPDATE entree_ItemPerDocument SET sorting= 3 WHERE documentID ='1' AND itemID='17'
INSERT INTO entree_ItemPerDocument (documentID,itemID,required,sorting) VALUES('1',' 23','0','4')
UPDATE entree_ItemPerDocument SET sorting= 5 WHERE documentID ='1' AND itemID='19'
UPDATE entree_ItemPerDocument SET sorting= 6 WHERE documentID ='1' AND itemID='20'
UPDATE entree_ItemPerDocument SET sorting= 7 WHERE documentID ='1' AND itemID='21'
UPDATE entree_ItemPerDocument SET sorting= 8 WHERE documentID ='1' AND itemID='22'
UPDATE entree_ItemPerDocument SET sorting= 9 WHERE documentID ='1' AND itemID='24'
UPDATE entree_ItemPerDocument SET sorting= 10 WHERE documentID ='1' AND itemID='27'
UPDATE entree_ItemPerDocument SET sorting= 11 WHERE documentID ='1' AND itemID='32'
UPDATE entree_ItemPerDocument SET sorting= 12 WHERE documentID ='1' AND itemID='33'
UPDATE entree_ItemPerDocument SET sorting= 13 WHERE documentID ='1' AND itemID='34'
UPDATE entree_ItemPerDocument SET sorting= 14 WHERE documentID ='1' AND itemID='35'
UPDATE entree_ItemPerDocument SET sorting= 15 WHERE documentID ='1' AND itemID='36'

Dragging one item from right to left should result into about the same result but with a delete query instead of  the insert one.

I simply can't think how the compair should be engineered so it presents the removed item.

I hope you understand.
0
Comment
Question by:Steynsk
  • 3
  • 2
5 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Let's make sure you we are not mixing up the client and server side functions.  It might help if you can render the page and and paste the rendered html or create a sample link we can see.

On the back end the delete is going to be

DELETE FROM some_table WHERE table_id=xyz;

On the client side (jquery) it is a matter of detecting what that is.  This is why it is easier to see the rendered code.  But looking at
<li class=""ui-state-highlight"" id='item_"&RS("itemID")&"'>"&RS("label")&"</li>

Open in new window

converts to something like
<li class="ui-state-highlight" id="item_123">This is my item</li>

Open in new window

You can access this li with jquery using
http://jsbin.com/uHasuRiC/1/edit?html,js,output
$('li').each(function(){
  var id=$(this).attr('id');
  alert(id);
  var newID=id.substr(id.length - 3);
  alert(newID);
});

Open in new window

From there an ajax call.
0
 
LVL 1

Author Comment

by:Steynsk
Comment Utility
Hello Scot thanks for the quick response but I don't think you understand the question.

The ajax works for adding from column left to right and the reordering of the right column is detected as well.

With this array compair

FOR i = LBound(getItems) TO UBound(getItems)
	FromArray = 0
	FOR y = LBound(arr,2) TO ubound(arr,2)
        if Trim(getItems(i)) = Trim(arr(0,y)) then 
			InArray = 1 
		end if
	next
	if FromArray = 1 then 
		sql_update = "UPDATE entree_ItemPerDocument SET sorting= " & i & " WHERE documentID ='"&documentID&"' AND itemID='" & Trim(getItems(i)) & "'"
	else
		sql_update = "INSERT INTO entree_ItemPerDocument (documentID,itemID,required,sorting) VALUES('"&documentID&"','"&getItems(i)&"','0','"&i&"')"
	end if
	response.write sql_update & "<br>"
Next

Open in new window


I detect what's new in the right column but now I need to figure out another array compare like the one below to detect if a item is removed from column right to left.

FOR y = LBound(arr,2) TO ubound(arr,2)
	FromArray = 0
	FOR i = LBound(getItems) TO UBound(getItems)
        if Trim(getItems(i))=Trim(arr(0,y)) then 
			FromArray = 1
		end if
	next
	if FromArray = 1 then 
		sql_update = "DELETE FROM entree_ItemPerDocument WHERE documentID ='"&documentID&"' AND itemID='" & Trim(getItems(i)) & "'"
	end if
	response.write sql_update & "<br>"
Next

Open in new window



But it does not work.....
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
I don't really understand what you are doing.  

In your first example http:#a39805908 there is no way FromArray can ever be "1" so you will always be inserting a record.   I think your first if then statement you meant t use FromArray instead of InArray=1

In that 2nd example, I am confused as to how you are detecting if something is removed.

If you have
<li><a>item1</a></li>
<li><a>item2</a></li>
<li><a>item3</a></li>

Open in new window

and I click on item 2, I would either send that to a query string/form post and refresh the page that trips a delete statement and reruns the query.  Or I would use jquery/ajax to remove the item and in the done function refresh the page or the list.

Maybe it would help if you post a link to your page so we can see the rendered html.
0
 
LVL 1

Accepted Solution

by:
Steynsk earned 0 total points
Comment Utility
Thank you for your time but I've found the solution:

FOR y = LBound(arr,2) TO ubound(arr,2) 
	FromArray = 1
		FOR i = LBound(getItems) TO UBound(getItems)
        if Trim(getItems(i)) = Trim(arr(0,y)) then 
			FromArray = 0
		end if
	next
	if FromArray = 1 then
		sql_update = "DELETE FROM entree_ItemPerDocument WHERE documentID ='"&documentID&"' AND itemID='" & Trim(arr(0,y)) & "'"
		response.write sql_update & "<br>"
	end if
Next

Open in new window

0
 
LVL 1

Author Closing Comment

by:Steynsk
Comment Utility
Because I've found my own solution
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now