Page 2 of 2
Re: converting an sql query into an array
Posted: Fri Mar 20, 2020 3:08 pm
by Klaus
Hi Paul,
probably wrong use of the IN operator, IN exspects a comma separated list!
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
Re: converting an sql query into an array
Posted: Fri Mar 20, 2020 3:18 pm
by FourthWorld
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?
Re: converting an sql query into an array
Posted: Sat Mar 28, 2020 10:29 am
by paulalsmith1000
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.