Getting correct result from database using counter

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 5:25 am

Hello all!

The problem I am having is that the database I am using is being queried correctly, but the correct result is not being returned because of a counter. For example, for the 1st line of the record, I get returned 2 results..."var mrk_" which correctly contains item 4 of the 1st line of the 1st record, but THEN also get "var mrk_" which contains item 4 of the 2nd line of the 1st record, and is the result that shows up (incorrectly for me) in the marker. Similarly, when the counter moves to the 2nd line of the record, I get "var mrk_1" and item 4 of the 1st line (which is wrong), THEN I also get returned, "var mrk_1" which is item 4 of the 2nd line/record, and the correct result that shows up inside the marker on the map.

The code is below:

function getMarkerCode pName
if sMarkers[pName] is empty then
return "error: no such marker."
else

local tMarkerCode, tPlaceJson, tPlaceArray, tAddress

put databaseGetLocations() into tData
put empty into tLocArray
put 1 into tCounter
set the itemDel to tab

repeat for each line tLine in tData

put item 4 of tLine into tLocArray[tCounter]["zIndex"]

put "var " & pName & " = new google.maps.Marker({" & CR into tMarkerCode
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld= " & tLocArray[1]["zIndex"] & "|FE6256|000000|'," & CR after tMarkerCode ##ARRAY HERE
put "position: new google.maps.LatLng(" & sMarkers[pName]["pos"] & ")," & CR after tMarkerCode
put "map: map," & CR after tMarkerCode
put "title:'" & sMarkers[pName]["title"] & "'," & CR after tMarkerCode
put "});"& CR after tMarkerCode

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

function getMarkers
return the keys of sMarkers ## "keys of sMarkers = "var mrk _", "var mrk _1", etc.
end getMarkers

Because the results are not being returned in the right sequence, I am getting the same "zIndex" # inside all the markers. Again, I think the problem comes from the way the counter is making the "tMarkerCode" result produce too many results "var mrk_", "var mrk_" and then "var mrk_1" and "var mrk_1". Not sure how I can correct this. I just need the formula in the "icon" (i.e. "tLocArray[1]["zIndex"]) to be able to correctly put the correct "zIndex" numerical value inside the marker. Any help/thoughts? Thanks!

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

Re: Getting correct result from database using counter

Post by Klaus » Fri Mar 18, 2016 12:04 pm

Hi Bellballer1,

not sure, but shouldn't this:
...
## put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld= " & tLocArray[1]["zIndex"] & "|FE6256|000000|'," & CR after tMarkerCode
...
read:
...
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld= " & tLocArray[tCounter]["zIndex"] & "|FE6256|000000|'," & CR after tMarkerCode
...
?


Best

Klaus

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 4:05 pm

Hi Klaus,

I made the change you suggested, but I get the same result. I need to get a unique value, the "zIndex", for each of the records in my database...I'm currently getting 4 values (returned as "var mrk_", "var mrk_", "var mrk_1" , and "var mrk_1" because the value stored in tLocArray[2]["zIndex"] (i.e. line 2 of a 2-line db) is the result given when the counter is used.

so currently I'm getting these results in this order:

1) "var mrk_" ## the zIndex is tLocArray[1]["zIndex"] -- which is correct and the result I want inside the marker (but do not get)
2) "var mrk_" ## the zIndex is tLocArray[2]["zIndex"] -- which is incorrect and shows inside the marker
3) "var mrk_1" ## the zIndex is tLocArray[1]["zIndex"] -- which is incorrect
4) "var mrk_1" ## the zIndex is tLocArray[2]["zIndex"] -- which is correct and the result I want inside the marker (and get)

However, this is all I need:

1) "var mrk_" ## the zIndex is tLocArray[1]["zIndex"]
2) "var mrk_1" ## the zIndex is tLocArray[2]["zIndex"]

Hopefully this helps further explain. Any tips?

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

Re: Getting correct result from database using counter

Post by Klaus » Fri Mar 18, 2016 4:08 pm

Hm, are you sure the records in your database are correct?
Does not sound like it, or i still do not understand your problem...

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 4:39 pm

The records in the database are correct. The problem is that I'm getting 4 results returned, when I only want 2. I'm getting 2 results for each marker because I get 1 result for each line in my database. So this is how the results are being returned (function getMarkerCode):

1) "var mrk_" ## the zIndex is tLocArray[1]["zIndex"] -- which is correct and the result I want inside the marker (but do not get)
2) "var mrk_" ## the zIndex is tLocArray[2]["zIndex"] -- which is incorrect and shows inside the marker
3) "var mrk_1" ## the zIndex is tLocArray[1]["zIndex"] -- which is incorrect
4) "var mrk_1" ## the zIndex is tLocArray[2]["zIndex"] -- which is correct and the result I want inside the marker (and get)

However, this is all I need:

1) "var mrk_" ## the zIndex is tLocArray[1]["zIndex"]
2) "var mrk_1" ## the zIndex is tLocArray[2]["zIndex"]

So on my map, I should have 2 markers, one with the numerical value of "tLocArray[1]["zIndex"]" and the other with "tLocArray[2]["zIndex"]" inside of the marker. Right now I am getting the numerical value of "tLocArray[2]["zIndex"]" inside both of the markers.

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

Re: Getting correct result from database using counter

Post by Klaus » Fri Mar 18, 2016 4:46 pm

Can't you modify your "databaseGetLocations()" function to only return the zindexes you need?
Sorry, I have to guess since I do not know what is in your database and how you actually fetch it.

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 5:12 pm

I can give modifying the "databaseGetLocations()" function. My database consists of 2 records, each line with a value for the "address, latitude, longitude, and zIndex."

put databaseGetLocations() into tData
put empty into tLocArray
put 1 into tCounter
set the itemDel to tab

repeat for each line tLine in tData
put item 1 of tLine into tLocArray[tCounter]["Address"]
put item 2 of tLine into tLocArray[tCounter]["Latitude"]
put item 3 of tLine into tLocArray[tCounter]["Longitude"]
put item 4 of tLine into tLocArray[tCounter]["zIndex"]

Do you suggest adding another column to my database (i.e. item 5), placing the proper value there? I guess it could work but doesn't seem natural.

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

Re: Getting correct result from database using counter

Post by Klaus » Fri Mar 18, 2016 5:18 pm

1) "var mrk_" ## the zIndex is tLocArray[1]["zIndex"] -- which is correct and the result I want inside the marker (but do not get)
2) "var mrk_" ## the zIndex is tLocArray[2]["zIndex"] -- which is incorrect and shows inside the marker
3) "var mrk_1" ## the zIndex is tLocArray[1]["zIndex"] -- which is incorrect
4) "var mrk_1" ## the zIndex is tLocArray[2]["zIndex"] -- which is correct and the result I want inside the marker (and get)
Sorry, what I don't really understand is the fact that you have 2 records in your database but you get 4 results for zindex?
That's why I was asking if the data in your db are correct!

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 5:26 pm

Right, that's the part I don't understand either (getting the 4 results). I think it has to do with the counter and the repeat. Its giving me the result after the repeat, which in this case, is always going to be "tLocArray[2]["zIndex"]" since it is the last result when the counter ends.

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

Re: Getting correct result from database using counter

Post by Klaus » Fri Mar 18, 2016 5:36 pm

Hm, did you check tData right after using:
...
put databaseGetLocations() into tData
## Like this
answer the num of lines of tData & CR & tData
...
?

Actually your problem sounds impossible if there are really only 2 records in your database
and hence there must only be 2 lines in tData.

Molto mysterioso! :D

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 6:11 pm

tData is right...I am getting the correct # of lines. I just added another record (now there are 3 records), and I got the same result...all my makers now have the zIndex from the 3rd line. I want:

"var mrk_" = tLocArray[1]["zIndex"]
"var mrk_1 = tLocArray[2]["zIndex"]
"var mrk_2 = tLocArray[3]["zIndex"]

instead now with the 3 records and lines, I get:

"var mrk_ = tLocArray[1]["zIndex"]
"var mrk_ = tLocArray[2]["zIndex"]
"var mrk_ = tLocArray[3]["zIndex"] ## result that shows in marker

"var mrk_1 = tLocArray[1]["zIndex"]
"var mrk_1 = tLocArray[2]["zIndex"]
"var mrk_1 = tLocArray[3]["zIndex"] ## result that shows in marker

"var mrk_2 = tLocArray[1]["zIndex"]
"var mrk_2 = tLocArray[2]["zIndex"]
"var mrk_2 = tLocArray[3]["zIndex"] ## result that shows in marker

Maybe there is a problem somewhere else in my code, which I have examined, but all roads seem to keep leading back to the way the counter is being executed, which gives off all these results.

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Getting correct result from database using counter

Post by phaworth » Fri Mar 18, 2016 8:31 pm

You need to supply more information to help debug this, specifically your database schema and the SELECT statement you are using to get the data. Hard to tell what the problem is without that.
Pete

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Fri Mar 18, 2016 9:20 pm

Here is the database schema:

function databaseGetLocations

put "SELECT Address, Latitude, Longitude, zIndex FROM Sites;" into tCreateListSQL
put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into gLocationList
return gLocationList
end databaseGetLocations

And here is the code that I use in the card that contains the search_field and calls the database.

on loadSelectedResult

put databaseGetLocations() into tData
put empty into tLocArray
put 1 into tCounter
set the itemDel to tab

repeat for each line tLine in tData
put item 1 of tLine into tLocArray[tCounter]["Address"]
put item 2 of tLine into tLocArray[tCounter]["Latitude"]
put item 3 of tLine into tLocArray[tCounter]["Longitude"]
put item 4 of tLine into tLocArray[tCounter]["zIndex"]

## get the lat & long for the selected result and create map HTML
--put placeSearch(the cSelectedResult of me) into tResultsArray
put placeSearch(tLocArray[tCounter]["Address"]) into tResultsArray
put tResultsArray["results"][1]["geometry"]["location"]["lat"] & "," & tResultsArray["results"][1]["geometry"]["location"]["lng"] into tLatLong
--get addMarker(tLatLong, "search_result")
get addMarker(tLatLong, tLocArray[tCounter]["Address"])
setMapLatLong tLatLong
setZoom 13
put getMapHTML() into URL("binfile:" & stackPath() & "/gen_mp_html.html")

set the text of me to the cSelectedResult of me

browserStart
add 1 to counter
end repeat

end loadSelectedResult

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Getting correct result from database using counter

Post by phaworth » Sun Mar 20, 2016 1:06 am

OK, the SQL looks fine. If you set a breakpoint right after the line "put item 4 of tLine..." and look at the tLocArray variable, what do you see? From your description, there should be two main elements (1 and 2) each containing Address, Latitude,Longitude, and zIndex keys with whatever values they should have. If that's what you see, the problem is somewhere else.

ALso, what are "var_mrk_" etc? Livecode variables? I don't see anything in the code you've posted that sets them.

Pete

Bellballer1
Posts: 40
Joined: Sat Oct 24, 2015 5:42 pm

Re: Getting correct result from database using counter

Post by Bellballer1 » Mon Mar 21, 2016 2:34 am

Hi Pete!

Thanks for the response...I really appreciate it. When I set the breakpoint, my tLocArray variables appear as they should. However, part of the problem is that its not updating (i.e. not reading line2, line3, etc.)...it is staying on line 1, so the only thing that appears in the markers is the value from the zIndex from line 1.

The code that sets the "var mrk_" is below:

function addMarker pPosition, pTitle

if pPosition is empty then
return "error: no position specified"
end if
local pname
put ("mrk_" & sMarkerCount) into pname

if sMarkers[pName] is empty then
add 1 to sMarkerCount
put pPosition into sMarkers[pName]["pos"]
put pTitle into sMarkers[pName]["title"]
return "OK"
else
return "error: marker with this name already exists."
end if
end addMarker

Any tips or ideas you can think of that gets my code ("function getMarkerCode") to look at each line of the table instead of going through the full repeat, would be greatly appreciated!

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”