converting an sql query into an array

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: converting an sql query into an array

Post by Klaus » Fri Mar 20, 2020 3:08 pm

Hi Paul,

probably wrong use of the IN operator, IN exspects a comma separated list! 8)

Try this (and please use the CODE tags after pasting your script here):

Code: Select all

command select_push_keys_for_selected_group_members
   put tune_group_id & "_members" into table_name

   ## !!!
   put "nancy,ben" into selected_group_members
   ## !!!
   
   put "SELECT push_device_key FROM " & table_name & " WHERE member_name IN (:1)" into tSQL
   put revDataFromQuery(tab, cr, tDatabaseID_tuune, tSQL, "selected_group_members") into push_keys_for_seleceted_members
   if item 1 of other_members = "revdberr" then
      answer error "There was a problem querying the database:" & cr & push_keys_for_seleceted_members
   else
      put "search was successful" && push_keys_for_seleceted_members
   end if
end select_push_keys_for_selected_group_members
I always refresh my poor knowledge of SQL here when it comes to working with databases:
https://www.w3schools.com/sql/default.asp

Best

Klaus

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: converting an sql query into an array

Post by FourthWorld » Fri Mar 20, 2020 3:18 pm

paulalsmith1000 wrote:
Fri Mar 20, 2020 2:06 pm
...I realized what I was doing was a bit convoluted, as in doubling up unnecessarily on what was already on my db.
...
Any help would be greatly appreciated, as this tiny bit of the app is doing my head in!
It would help me understand how well I grasped (or didn't) the initial description of what you're looking to do if you could tell me: did you look at the example stack I posted?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: converting an sql query into an array

Post by paulalsmith1000 » Sat Mar 28, 2020 10:29 am

Thanks v much for all the answers everyone.

I went round the houses trying the various suggestions above, but in the end I used the stack from Fourthworld, because I still couldn't get the SQL syntax to work (I've now bought 'SQL Queries for mere mortals' to try and swot up).

So, for any NOOBs like me out there -

-A simple and neat way to achieve what I asked is to put the data in a list, so each line has the 2 items separated by a comma.

-When you display the list in the App, if you change the tab settings in the property inspector you can move the 2nd item outside of the border of the list (so the user cant see it)

-Then when the user selects a line in the list, you can use the itemdelimiter to copy the 2nd item (set itemdelimiter to "," / put the item 2 of the selectedtext of field "yourlist" into wherever you want.

-For multiple lines you can do this

put the selectedtext of field "yourlist" into your_variable
set the itemdel to tab
repeat for each line L in your_variable
put item 2 of L & comma & space after where_you_want_the_info_saved
end repeat
delete the last character of where_you_want_the_info_saved (this deletes the last space)
delete the last character of where_you_want_the_info_saved (this deletes the last comma)

I'm sure the last 2 lines above could be done more efficiently, but I'm not sure how.

Post Reply

Return to “Databases”