converting an sql query into an array

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

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

converting an sql query into an array

Post by paulalsmith1000 » Wed Mar 18, 2020 12:34 am

Evening everyone

Could one of you clever people answer the following:-

I'm making a very simple database query which returns the info from 2 columns for a number of entries

e.g the result is

john smith eastbourne
ben brown brighton
etc, etc..

What I'd like to do is convert the result into an array (or something)

This would be so that I can display the 1st item of the each entry in the array in a list field, and then when someone selects the item(s) it will create a variable with each the 2nd items, separated by commas.

As ever, any help would be much appreciated.

Kind regards

Paul

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

Re: converting an sql query into an array

Post by FourthWorld » Wed Mar 18, 2020 12:56 am

Is the first item of each entry guaranteed to be unique?
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

bogs
Posts: 4692
Joined: Sat Feb 25, 2017 10:45 pm

Re: converting an sql query into an array

Post by bogs » Wed Mar 18, 2020 6:06 am

FourthWorld wrote:
Wed Mar 18, 2020 12:56 am
Is the first item of each entry guaranteed to be unique?
Well, if he uses the record number or key, it will be :)
This would be so that I can display the 1st item of the each entry in the array in a list field, and then when someone selects the item(s) it will create a variable with each the 2nd items, separated by commas.
If you use the record number/key as the array key, you can format the value in any way you wish, comma (NOT preferred if any of the value has commas in it), tab, character of your own choosing, etc.

You can then set your value separation marker to split the value into the various cells, fields, whatever.
Image

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

Re: converting an sql query into an array

Post by FourthWorld » Wed Mar 18, 2020 6:53 am

paulalsmith1000 wrote:
Wed Mar 18, 2020 12:34 am
What I'd like to do is convert the result into an array (or something)
Here's an "or something" (sometimes a simple delimited list in a list field is just fine):
Attachments
List Example.zip
(837 Bytes) Downloaded 39 times
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

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

Re: converting an sql query into an array

Post by Klaus » Wed Mar 18, 2020 1:46 pm

This has come up herer a couple of times before...
Check this one: https://forums.livecode.com/viewtopic.p ... rd#p163184

AxWald
Posts: 426
Joined: Thu Mar 06, 2014 2:57 pm

Re: converting an sql query into an array

Post by AxWald » Wed Mar 18, 2020 2:51 pm

Hi,

at first, when you fetch partial data from a DB it's usually just a matter of time until you want to retrieve some of the corresponding data. So you'll want to have always a unique designator for each record.
If you haven't slept in DB school, you'll have a primary key for each of your tables, usually something like (in mySQL):

Code: Select all

`ID` INT(11) NOT NULL AUTO_INCREMENT,
... ,
PRIMARY KEY (`ID`)
This one you'll fetch with all of your queries - you may not need it now, but it doesn't hurt & may come handy later.
It helps with a quirk of LC, too - LC can behave unpredictable when you have table-like data & empty fields at the end of a record. You really don't want empty fields at the end of your records - they'll come back & bite you! So you query:

Code: Select all

SELECT name, city ID FROM t_table
and get:

Code: Select all

john smith [tab] eastbourne [tab] 1 [CR]
ben brown [tab] brighton [tab] 2 [CR]
...
Now you have your data, and know that ID is unique - thus we'll make it the key for our array. Assume the data is in "myData":

Code: Select all

   set itemdel to tab
   repeat for each line L in myData
      put item 1 of L into myArr[item 3 of L][name]
      put item 2 of L into myArr[item 3 of L][city]
   end repeat
Result:

Code: Select all

1
   city        eastbourne 
   name        john smith
2
   city        brighton 
   name        ben brown
Your array is done.
----------------------------------
Now you may want to save it, and display it. There's a lot of ways to do it, I'll show a tricky one - but one that can be used in a lot of ways. To do so, we have to save the array at first:

Code: Select all

   set the cData of fld "names_fld" to myArr
It's now saved in a custom property of the field - and the field itself can care for the display! Have your field's "listbehaviour" set, and put a high number (>= the width of the field) into "Table/ tabStops" - be sure nothing else is marked there! Then put this code into your field:

Code: Select all

setProp cData theArr
   repeat for each key K in theArr
      put theArr[K][name] & tab & K & CR after myVar
   end repeat
   delete char -1 of myvar         --  the trailing CR
   put myvar into me               --  show text
   pass cData                      --  don't forget this, else theArr isn't stored!
end cData	
You see, the field handles the display of the data you throw to it, all by itself - each time you change the cData of it :)
Result:

Code: Select all

john smith	1
ben brown	2
But you don't see the ID 'cause it's outside the field (due to the high value for "tabStops").
Now you want to display the city of the selected name in another field. Add this code to fld "names_fld":

Code: Select all

on selectionchanged
   set itemdel to tab              --  'cause we work with tab now
   put item -1 of line (the hilitedline of me) of me into myID  --  ;-))
   put the cData of me into myArr  --  retrieve the array
   put myArr[myID][city] into fld "city_fld"   --  and get the matching city
end selectionchanged
Voila!

This may look a little dazzling at first, but checking & understanding it will teach some important thingies:
  • The use of an ID - maybe you'll want to retrieve the fone number later?

    Code: Select all

    put "SELECT foneNr FROM t_table where ID = " & myID into StrSQL
  • Using multidimensional, named arrays (not only the dull flat key-value pairs they're meant as)
  • Using custom properties to store data where they belong, and process 'em from there
Hope this helps, have fun!
Livecode programming until the cat hits the fan ...

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

Re: converting an sql query into an array

Post by FourthWorld » Wed Mar 18, 2020 4:37 pm

Unless there is a requirement not described, it doesn't seem an array is needed at all. Indeed, keeping the delimited data delimited allows the described UI to be made with very little code; the example I posted yesterday has just one statement in it.

Arrays are great. And so are chunk expressions. There's a good place for each. This exercise seems to lend itself to simple chunks.
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

AxWald
Posts: 426
Joined: Thu Mar 06, 2014 2:57 pm

Re: converting an sql query into an array

Post by AxWald » Wed Mar 18, 2020 7:19 pm

Well,
the OP asked for arrays ...

In my work, I'm using table-like data all day long. And since I've finally understood arrays (took me quite some time ...) I use them quite often, too. But not exclusively!

In my humble opinion, arrays are good when:
  • You want data saved in a stack (or a file) that cannot be read easily in any text editor.
  • You want to access parts of the data by field name - working with "item 23 (or was it 24?) of L" can be tedious & becomes a pain when you have to change the field order/ count.
  • You have really, really large data.
Arrays have disadvantages, too:
  • For small data sets, they are actually slower then simple tab-CR lists.
  • You have to sort often ...
  • They're less easy to debug.
So, as always: Choose your tools wisely ;-)
Have fun!
Livecode programming until the cat hits the fan ...

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

Re: converting an sql query into an array

Post by FourthWorld » Wed Mar 18, 2020 7:35 pm

AxWald wrote:
Wed Mar 18, 2020 7:19 pm
Well,
the OP asked for arrays ...
"...or something"

Unless there are requirements not yet described, arrays can work but "or something" appears simpler and more efficient in this case, esp. when the "or something" is simple delimited text, since here delimited text is the input, and it is the output, and it lends itself very well to low-code solutions for the UI in between.

You raised some very relevant points about the strengths and weaknesses of arrays. Well done, and useful to keep in mind on any project.

There is no one-size-fits-all data type. As you say, "Choose your tools wisely"
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

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

Re: converting an sql query into an array

Post by paulalsmith1000 » Thu Mar 19, 2020 12:40 am

wow thanks for all the answers


I went with the array in the end, which worked fine as far as displaying the data, but retrieving what I actually need is still eluding me:-

I have the array as suggested-

key -id name info i want
1 paul xxxxxxxx(push key)

I can select all the names and put them in a list field - ok good

but, the app will be used to share something with a number of people, so the user will select say 4 names from the list field.

what i then need to do is use those names to search the array and return the push-keys for each person (as a string, comma separated.

I tried to use-

'repeat for each element in array' but I cant work out the syntax.

any (more) help would be great

Kind regards

Paul

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

Re: converting an sql query into an array

Post by FourthWorld » Thu Mar 19, 2020 12:54 am

What drove your decision to use arrays for this particular need?
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 760
Joined: Wed Nov 22, 2006 3:42 pm
Location: France
Contact:

Re: converting an sql query into an array

Post by Thierry » Thu Mar 19, 2020 9:45 am

paulalsmith wrote:
what i then need to do is use those names to search the array and return the push-keys
for each person (as a string, comma separated.

I tried to use-

'repeat for each element in array' but I cant work out the syntax.

any (more) help would be great
Hi Paul,

Feel free to send me a sample stack with some of your datas (or fake ones)
and I might give you some hints...
as I'm using arrays more often than lists for complex datas.

Regards,

Thierry
Regex LiveCode sunnYrex
https://sunny-tdz.com

bogs
Posts: 4692
Joined: Sat Feb 25, 2017 10:45 pm

Re: converting an sql query into an array

Post by bogs » Thu Mar 19, 2020 10:16 am

Thierry wrote:
Thu Mar 19, 2020 9:45 am
Feel free to send me a sample stack with some of your datas (or fake ones)
Now there is an offer you can't beat with a stick! I'd jump all over that, paulalsmith1000.
Image

AxWald
Posts: 426
Joined: Thu Mar 06, 2014 2:57 pm

Re: converting an sql query into an array

Post by AxWald » Thu Mar 19, 2020 2:07 pm

Hi,

assuming you have the Array as I showed above (Mainkey = ID, Sub-Entries are named), and the selected IDs are in a comma-del list "mySelectedList ", and your itemdel is still "comma", try this:

Code: Select all

   repeat for each key K in myArr
      if K is among the items of mySelectedList then
         put myArr[K]["pushNum"] & comma after myVar
      end if
   end repeat
   delete char -1 of myVar
   put myVar
Have fun!
Livecode programming until the cat hits the fan ...

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

Re: converting an sql query into an array

Post by paulalsmith1000 » Fri Mar 20, 2020 2:06 pm

Again, thank you for all the replies and offers of help to check my stack


However, I have changed tack a bit as I realized what I was doing was a bit convoluted, as in doubling up unnecessarily on what was already on my db.

All I need to do is populate a field in my sql db with the push keys of whatever people have been selected, so I adapted the command below, which I know works to see if this is possible. I need to change it to UPDATE obviously, but I can do that bit:-

command select_push_keys_for_selected_group_members
put tune_group_id & "_members" into table_name
put "nancy" 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

This works fine and displays the push key as expected, but If I replace the 2nd line with:-

put "nancy OR ben" into selected_group_members

no keys are returned, when I was expecting 2?

(The 2nd line is just to test the query it actually comes from a variable)

Any help would be greatly appreciated, as this tiny bit of the app is doing my head in!

Post Reply

Return to “Databases”