Changing Images based on SQL Database entry

Solar2D General Questions/Discussion
#1

Hello everyone,

I have been having a tough time getting an image to show based on a SQL Database entry. I have a tableView and I would like a certain image to show based a value stored in the database. I have the SQL select code, but the images don’t show up properly. Does anyone have any experience with updating an image in a tableView row using SQL? I would appreciate any help or advice.

Thank you.

#2

I am guessing you’re storing the images on a folder and then storing the image name in your sqldb. Is this right? Post some code if you could so we can see what the issue might be. 

#3

@ksan Yes that is what I mean. This isn’t working at all.

  function reloadGreenChecks()             pulledAddedCompleted = {}             completedAddedInt = 1             for row in db:nrows("SELECT completed FROM user\_entries WHERE completed= 'yes'") do                 pulledAddedCompleted[completedAddedInt] = "green check"                 completedAddedInt=completedAddedInt+1             end         end           function reloadBlueChecks()             pulledAddedCompletedNo = {}             completedAddedIntNo = 1             for row in db:nrows("SELECT completed FROM user\_entries WHERE completed= 'no'") do                 pulledAddedCompletedNo[completedAddedIntNo] = "blue check"                 completedAddedIntNo=completedAddedIntNo+1             end         end   function completeTask(event)             if event.phase == "ended" and myData.addingMode == false then                 local buttonId = tonumber(event.target.id)                 print("This is the button that was selected"..buttonId)                 print("Task marked as complete")                 local markComplete = "UPDATE user\_entries SET completed='yes' WHERE id='"..buttonId.."'"                 print(markComplete)                 db:exec(markComplete)                 rowCompleteAddedNo[row.index].isVisible = false                 reloadGreenChecks()                 reloadBlueChecks()                 generateBlueChecks()                 generateGreenChecks()             end             return true         end                  function unCompleteTask(event)             if event.phase == "ended" and myData.addingMode == false then                 local buttonId2 = tonumber(event.target.id)                 print("Task marked as not complete")                 local markunComplete = "UPDATE user\_entries SET completed='no' WHERE id='"..buttonId2.."'"                 print(markunComplete)                 db:exec(markunComplete)                 rowCompleteAdded[row.index].isVisible = false                 reloadGreenChecks()                 reloadBlueChecks()                 generateBlueChecks()                 generateGreenChecks()             end             return true          end   function generateBlueChecks()                 for i=1, #pulledAddedCompletedNo do                 if myData.isEditing == false then                 rowCompleteNoAdded[i] = display.newImageRect(row, "images/"..pulledAddedCompletedNo[row.index]..".png", 50,50)                 rowCompleteNoAdded[i].x = row.x - ( row.contentWidth \* 0.5 ) + ( rowCompleteNoAdded[i].contentWidth \* 0.5 ) + 509.5                 rowCompleteNoAdded[i].y = 50                 rowCompleteNoAdded[i].id = gatheredId                 rowCompleteNoAdded[i]:addEventListener("touch", completeTask)                 end             end         end                  function generateGreenChecks()             for i=1, #pulledAddedCompleted do                 if myData.isEditing == false then                 rowCompleteAdded[i] = display.newImageRect(row, "images/"..pulledAddedCompleted[row.index]..".png", 50,50)                 rowCompleteAdded[i].x = row.x - ( row.contentWidth \* 0.5 ) + ( rowCompleteAdded[i].contentWidth \* 0.5 ) + 509.5                 rowCompleteAdded[i].y = 50                 rowCompleteAdded[i].id = gatheredId                 rowCompleteAdded[i]:addEventListener("touch", unCompleteTask)                 end             end         end                  generateBlueChecks()         generateGreenChecks()  
#4

Is it possible to post it using the code button? Looks like <> . Thanks

#5

Just fixed it…sorry. Is it better now?

#6

Yup. This is definitely more readable. I’m not sure if I understand well what you are doing but what I can see is that you are not using the typical tableView:insertRow() in a for loop traversing through your data and inserting the rows. This action is typically met with an onRowRender listener where you do all the text and image allocations etc. 

Suggest you take a look at the examples in : http://docs.coronalabs.com/api/library/widget/newTableView.html and 

http://docs.coronalabs.com/api/type/TableViewWidget/insertRow.html

Hope this helps.

#7

All of the code I posted is inside the onRowRender function. The insertRow code is further down in the file. All of the text rendered in the onRowRender function works fine. It’s just the images that are causing a problem.

#8

I’m sorry but I can’t get my head around your code. Perhaps you can try something like

local myImageFileName = “images/”…pulledAddedCompleted[row.index]…".png"

print (myImageFileName)

rowCompleteAdded[i] = display.newImageRect(row, myImageFileName, 50,50)

rather than 

rowCompleteAdded[i] = display.newImageRect(row, “images/”…pulledAddedCompleted[row.index]…".png", 50,50)

to see what you’re giving that display.newImageRect.

Hope this helps.

#9

I am guessing you’re storing the images on a folder and then storing the image name in your sqldb. Is this right? Post some code if you could so we can see what the issue might be. 

#10

@ksan Yes that is what I mean. This isn’t working at all.

&nbsp; function reloadGreenChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pulledAddedCompleted = {} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;completedAddedInt = 1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for row in db:nrows("SELECT completed FROM user\_entries WHERE completed= 'yes'") do &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pulledAddedCompleted[completedAddedInt] = "green check" &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;completedAddedInt=completedAddedInt+1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;function reloadBlueChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pulledAddedCompletedNo = {} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;completedAddedIntNo = 1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for row in db:nrows("SELECT completed FROM user\_entries WHERE completed= 'no'") do &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pulledAddedCompletedNo[completedAddedIntNo] = "blue check" &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;completedAddedIntNo=completedAddedIntNo+1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp; function completeTask(event) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if event.phase == "ended" and myData.addingMode == false then &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;local buttonId = tonumber(event.target.id) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;print("This is the button that was selected"..buttonId) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;print("Task marked as complete") &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;local markComplete = "UPDATE user\_entries SET completed='yes' WHERE id='"..buttonId.."'" &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;print(markComplete) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db:exec(markComplete) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAddedNo[row.index].isVisible = false &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;reloadGreenChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;reloadBlueChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;generateBlueChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;generateGreenChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return true &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;function unCompleteTask(event) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if event.phase == "ended" and myData.addingMode == false then &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;local buttonId2 = tonumber(event.target.id) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;print("Task marked as not complete") &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;local markunComplete = "UPDATE user\_entries SET completed='no' WHERE id='"..buttonId2.."'" &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;print(markunComplete) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db:exec(markunComplete) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAdded[row.index].isVisible = false &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;reloadGreenChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;reloadBlueChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;generateBlueChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;generateGreenChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return true&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp; function generateBlueChecks()&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for i=1, #pulledAddedCompletedNo do &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if myData.isEditing == false then &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteNoAdded[i] = display.newImageRect(row, "images/"..pulledAddedCompletedNo[row.index]..".png", 50,50) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteNoAdded[i].x = row.x - ( row.contentWidth \* 0.5 ) + ( rowCompleteNoAdded[i].contentWidth \* 0.5 ) + 509.5 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteNoAdded[i].y = 50 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteNoAdded[i].id = gatheredId &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteNoAdded[i]:addEventListener("touch", completeTask) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;function generateGreenChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for i=1, #pulledAddedCompleted do &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if myData.isEditing == false then &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAdded[i] = display.newImageRect(row, "images/"..pulledAddedCompleted[row.index]..".png", 50,50) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAdded[i].x = row.x - ( row.contentWidth \* 0.5 ) + ( rowCompleteAdded[i].contentWidth \* 0.5 ) + 509.5 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAdded[i].y = 50 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAdded[i].id = gatheredId &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rowCompleteAdded[i]:addEventListener("touch", unCompleteTask) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;generateBlueChecks() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;generateGreenChecks() &nbsp;
#11

Is it possible to post it using the code button? Looks like <> . Thanks

#12

Just fixed it…sorry. Is it better now?

#13

Yup. This is definitely more readable. I’m not sure if I understand well what you are doing but what I can see is that you are not using the typical tableView:insertRow() in a for loop traversing through your data and inserting the rows. This action is typically met with an onRowRender listener where you do all the text and image allocations etc. 

Suggest you take a look at the examples in : http://docs.coronalabs.com/api/library/widget/newTableView.html and 

http://docs.coronalabs.com/api/type/TableViewWidget/insertRow.html

Hope this helps.

#14

All of the code I posted is inside the onRowRender function. The insertRow code is further down in the file. All of the text rendered in the onRowRender function works fine. It’s just the images that are causing a problem.

#15

I’m sorry but I can’t get my head around your code. Perhaps you can try something like

local myImageFileName = “images/”…pulledAddedCompleted[row.index]…".png"

print (myImageFileName)

rowCompleteAdded[i] = display.newImageRect(row, myImageFileName, 50,50)

rather than 

rowCompleteAdded[i] = display.newImageRect(row, “images/”…pulledAddedCompleted[row.index]…".png", 50,50)

to see what you’re giving that display.newImageRect.

Hope this helps.