LiveCode Forums.

Hello all! Any help with this would be greatly appreciated.

I created a database in my stack script, using DB Browser for SQLite, and I need the table to be read by the code in the substack where I have the google Maps API. The first column of the table is "Location name", the next 2 columns are the longitude & latitude , and the final column is the zIndex. I have the global variables "gDatabaseID" and "gLocationList". I query the database and put the result into gLocationList.

function databaseGetLocations
put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into gLocationList
return gLocationList
end databaseGetLocations


In my substack for the google Maps API, I use the command "updateCard" to put gLocationList into sLocations. The code in the google maps api substack is below:

function getMarkerCode pName
if sMarkers[pName] is empty then
return "error: no such marker."
else
local tMarkerCode, tPlaceJson, tPlaceArray

--put "for (var i = 0; i < locations.length; i++) {"
--put "var myLatLng = new google.maps.LatLng(sLocations[1], sLocations[2]),"; ## when I use this code, I don't get any results
put "var " & pName & " = new google.maps.Marker({" & return into tMarkerCode
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode ## want zIndex # to show in Marker

put "position: new google.maps.LatLng(" & sMarkers[pName]["pos"] & ")," & return after tMarkerCode
--put "position: myLatLng," & return after tMarkerCode ## would like for the position to be read from "myLatLng" instead of from position code on live above
put "map: map," & return after tMarkerCode
--put "title: sLocation[0]," & "'" & return after tMarkerCode ## would like this to come from database table
put "title:'" & sMarkers[pName]["title"] & "'," & return after tMarkerCode
--put "zIndex: sLocations[3]," & return after tMarkerCode ## would like this to come from database table
put "});"& return after tMarkerCode

put getPlaceByLatLong(sMarkers[pName]["pos"]) into tPlaceJson
put jsonToArray(tPlaceJson) into tPlaceArray
##put tPlaceArray["results"][1]["formatted_address"]
put "(function (marker) {"&return after tMarkerCode
put "google.maps.event.addListener(marker, 'click', function (e) {"&return after tMarkerCode
put "infobox.setContent('<div id=" & quote & "infobox" & quote & " >'+'<p>'+" & quote & tPlaceArray["results"][1]["formatted_address"] &quote & "+'</p>'+'</div>');" & return after tMarkerCode
put "infobox.open(map, marker);"&return after tMarkerCode
put "markCentered(marker.position);"&return after tMarkerCode
put "});" & return after tMarkerCode
put "})(" & pName & ");"&return after tMarkerCode

return tMarkerCode
end if
end getMarkerCode

The goal is to only have to update the database tables so that the results flow automatically into tMarkerCode, and for the markers to have distinct labels (e.g. 1, 2, 3, etc.). Also want the titles and positions to come from whats in the database tables.

Your query to the DB should look something like this. Then you would parse the items in tData.

Thanks for the response quailcreek!

Do you mean something like this?:

function databaseGetLocations

put "SELECT Locations.Location,Locations.lat,Locations.lng,Locations.zIndex FROM Locations" into tCreateListSQL
put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into tData

set the itemDel to tab
put item 1 of tData into tLocation
put item 2 of tData into tlat
put item 3 of tData into tlng
put item 4 of tData into tzIndex

set the itemDel to comma

end databaseGetLocations

If so, in my substack for the google maps API, how do I get the values of tLocation, tlat, tlng, and tzIndex into the proper place, since it's my understanding that "t=temporary" and is only being used in the stack script?

You really shouldn't have a . in the header name. It will cause you endless misery.
The t is for temp but it can be used anywhere. It's a way of not only identifying a temp variable but also for making sure you don't use a special word like "location" as a variable name. tLocation is OK as a variable container, but the word location is used by LC.
Your going to need this link.
http://www.w3schools.com/sql/sql_quickref.asp

Hopefully this is my last question (smile).

Per quailcreek's suggestions, I changed the header name to the following to get rid of the periods:

function databaseGetLocations
put "SELECT Address, Latitude, Longitude, zIndex FROM Sites;" into tSQLStatement
put revDataFromQuery(tab,return,gDatabaseID,tSQLStatement) into tData

set the itemDel to tab
put item 1 of tData into tAddress
put item 2 of tData into tLatitude
put item 3 of tData into tLongitude
put item 4 of tData into tzIndex

set the itemDel to comma
end databaseGetLocations

But the part that continues to confuse me is how to get the tData items into my code? I still cannot seem to get the code to read the values from the database. Below is the code that I've been using and also code that I would like to use (i.e. the lines that start with "--" is what I would like to use, but the values from the database are not being read.

function getMarkerCode pName
if sMarkers[pName] is empty then
return "error: no such marker."
else
local tMarkerCode, tPlaceJson, tPlaceArray, tAddress, tLatitude, tLongitude, tzIndex

--put "var myLatLng = new google.maps.LatLng(tLatitude, tLongitude),";
put "var " & pName & " = new google.maps.Marker({" & return into tMarkerCode
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode

put "position: new google.maps.LatLng(" & sMarkers[pName]["pos"] & ")," & return after tMarkerCode
--put "position: myLatLng," & return after tMarkerCode
put "map: map," & return after tMarkerCode
--put "title: tAddress," & "'" & return after tMarkerCode
put "title:'" & sMarkers[pName]["title"] & "'," & return after tMarkerCode
--put "tzIndex," & return after tMarkerCode
put "});"& return after tMarkerCode

put getPlaceByLatLong(sMarkers[pName]["pos"]) into tPlaceJson
put jsonToArray(tPlaceJson) into tPlaceArray
##put tPlaceArray["results"][1]["formatted_address"]
put "(function (marker) {"&return after tMarkerCode
put "google.maps.event.addListener(marker, 'click', function (e) {"&return after tMarkerCode
put "infobox.setContent('<div id=" & quote & "infobox" & quote & " >'+'<p>'+" & quote & tPlaceArray["results"][1]["formatted_address"] &quote & "+'</p>'+'</div>');" & return after tMarkerCode
put "infobox.open(map, marker);"&return after tMarkerCode
put "markCentered(marker.position);"&return after tMarkerCode
put "});" & return after tMarkerCode
put "})(" & pName & ");"&return after tMarkerCode

answer tMarkerCode

return tMarkerCode

end if
end getMarkerCode

Hi Bellballer1,

just fetch the data form the db when you need it!

1. Use your first version of your function: You should add some error checking in your database calls however!

2. The get the data when needed, no need to use local or globakl variables: Sounds logical when you see it, right?


Best

Klaus

Thanks Klaus!

That is very logical! Whenever I find the right solution in LiveCode, it always seems easier than I made it out to be.

I did throw in some error checks, and I now know that the correct data and values are being carried over properly (e.g. the proper values in tAddress, tLatitude, etc.). Now I just have to write the script properly so that the variables show up correctly. For instance:

put "title: tAddress" & return after tMarkerCode ## nothing happens when I use this code.

I will continue to play around with the script so that it works properly, especially since I know the right values are in tAddress, etc. I feel I must be making another simple mistake (once again).

Hi Bellballer1,

Bellballer1 wrote:...
put "title: tAddress" & return after tMarkerCode
## nothing happens when I use this code.
...

not surprising, you are passing the STRING tAddress and not the value of that variable!

Do like this:
...
put "title:" & tAddress & CR after tMarkerCode
## Save some time and simply use CR instad of return
...

Best

Klaus

Hi Klaus!
Thank you for everything! Your tips have worked, no surprises there! Now just 2 minor questions (smile).

I have 2 lines of data in my database table, but right now only the first line is being read. Do I set the itemDel to "repeat", so that the next line from the table is read? Again, here is what I have so for (where only the 1st line of the table is being read):

put databaseGetLocations() into tData
set the itemDel to tab
put item 1 of tData into tAddress
put item 2 of tData into tLatitude
put item 3 of tData into tLongitude
put item 4 of tData into tzIndex

I need it to loop through and read the next line for the next location (i.e. the next address, lat/long and zIndex).

Question 2 involves the zIndex. The zIndex is a number (numbers 1 and 2, and maybe more numbers later), that I want to appear inside my location marker on the map. I am using this code:

put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode

However, I'm not quite sure how to get the zIndex number into the marker. I've searched stock overflow for examples, which I have found, but its not working for me. Any tips would be greatly appreciated. Thanks again! Hope I'm not being too much of a pain (smiling again).

Hi Bellballer1,

Bellballer1 wrote:I have 2 lines of data in my database table, but right now only the first line is being read..

you mean you have 2 RECORDS in your database, right? Speaking the same language is really helpful
Hm, your SQL: will actually fetch ALL records from that table, so you should do something like this.
I use an array, here, easier to read and maintain, since we do not know how many "tAddress" etc. variables we might need! Now you can access the different Adresses, Latitudes etc like:
...
put tLocArray[1]["Address"] into tAddress1
...
or use it directly:
...
put "title:" & tLocArray[1]["Address"] & CR after tMarkerCode
...
You get the picture.

Bellballer1 wrote:Question 2 involves the zIndex. The zIndex is a number (numbers 1 and 2, and maybe more numbers later), that I want to appear inside my location marker on the map. I am using this code:
...
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode
...

I thought I already explained this?
I think the number you are talking about in this example is -> FE6256|000000
Then you can:
...
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|" & tLocArray[1]["zindex"] & "|'," & return after tMarkerCode
...

Best

Klaus

Klaus you are beautiful!!

All of your tips have worked! The RECORDS in my database table are now being read properly (and it helps to speak the same "language"...sorry about that). Like you said in an earlier post, it all sounds logical when I see it.

The 2nd part of my question from yesterday actually involved the number (in this case, the"zIndex") that goes inside the marker. It now looks like this:

put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld= " & tLocArray[1]["zIndex"] & "|FE6256|000000|'," & CR after tMarkerCode

The only trouble I am having with this is it's using the zIndex from the last record in the table for each marker (i.e. all of my markers appear on the map with the same number (zIndex)). The zIndex from the 1st record in my database is not being saved in the marker. I suspect it has something to do with where I place the "add 1 to counter" and "end repeat" command. I will figure it out though!

Thanks tremendously for your help!!

Hi Bellballer1,

Bellballer1 wrote:Klaus you are beautiful!!

thank you for your kind words, but I already have a steady girlfriend!


Best

Klaus