Checking if an item in a database exists

Hi, I am creating a game, and at the beginning, I have this little bit of code:

[lua]

local sqlite3 = require “sqlite3”;

local db = sqlite3.open_memory();

local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]

db:exec( tablesetup );

[/lua]

So far so good. Everything is working fine. Basically, the player gets points. At the end, I tell them what they got that round. 

So I did this:

[lua]

local testvalue = {}

testvalue[1] = prescore;–prescore is the score they got last round

db:exec( tablefill )

[/lua]

Everything has worked fine. 

BUT, at the beginning of the code, I say in the main menu: “Your previous score was (prescore)”

The problem: I basically want to set prescore to 0 IF testvalue[1] does not exist. I want to set it to testvalue[1] IF it does exist.

I tried doing:

[lua]

if( testvalue[1] ~= nil ) then

prescore = 0;

else 

prescore = testvalue[1];

end

[/lua]

But it keeps telling me that testvalue[1] is a nil value. Can someone tell me how to check if an item in a table exists or not?

Thanks

The ~= symbol means “does not equal”. I think you might want to change that bit to the “does equal” sign of ==. Like below:

If testvalue[1] == nil then

Sorry, I should have mentioned I tried both ways. That doesn’t work either. I’m still getting an error.

I’d say that’s because the testvalue table hasn’t been created when that function runs. Would just using testvalue == nil work?

Good idea, but it still doesn’t work. 

Is there even a way to tell if an item exists in a database? I want to assign prescore to 0 if this is their first time playing. If it isn’t their first time playing, I want to assign prescore to testvalue[1], which was their previous score. So I need to be able to asee if testvalue[1] exists, but I can’t do that, because at the time, it is a nil value.

I’m not going to pretend like I’m Conan the Librarian over here so I can’t really give any tips on DB maintenance. I have implemented json logic into my games and projects to check to see if a user has ever started my game/app before, but I don’t know much about sql and how it is similar/different. 

Would a mix of json and sql work? I’m guess not because it’s like having two engines in your car. Why have one if you’re using the other. There must be a way to loop through a sql DB to check for existing tables and values. 

If you’re not married to sql I’d suggest converting to .json as it’s a bit more intuitive. Just my .02.

Hey, that’s cool.  Oh, could you tell me how you used Json to tell if they had played your game already?

Thanks

It’s kind of a roll-your-own type of implementation. I’d suggest checking out Rob’s tutorial on .json. If I could figure it and use it, anyone can:

http://omnigeek.robmiracle.com/2012/02/23/need-to-save-your-game-data-in-corona-sdk-check-out-this-little-bit-of-code/

A wonderful young lady by the name of Peach Pellen used to be a forum super-moderator, but she has since moved onto greener pastures. She created a bunch of nifty libraries for Corona, but they are mostly lost in the ether of the internet. I have a few, and one of the ones I salvaged was her Ego Level selection library. This illustrates how one would handle saving, loading and referencing data. it can apply to high scores, levels, unlockables, whatever. 

Let me know how you get on. Good Luck!

https://www.dropbox.com/s/l3cezaxw6gkf7xn/ego_levels.zip

Well, it doesn’t make sense, really.  Whenever I try to load a Json table at the beginning, it says the table is a nil table. IF you want to keep trying to help, you can, but you don’t have too. Thanks for your help!

I don’t mind helping. I’m no expert by any stretch, but helping others helps everyone in the long run, so I’m happy to lend a hand.

As for you getting another nil error when using json strings, I’m going to assume it’s still not implemented correctly. Here’s basically what I have for json game data:

--main.lua local json = require("json") local pathOne = system.pathForFile( "SavedGameOneSettings.json", system.DocumentsDirectory ) local oneFile = io.open( pathOne ) local pathGame = system.pathForFile( "GameSettings.json", system.DocumentsDirectory ) local gameFile = io.open( pathGame ) function saveTableOne(t, filename) local path = system.pathForFile( "SavedGameOneSettings.json", system.DocumentsDirectory) local file = io.open(path, "w") if file then local contents = json.encode(t) file:write( contents ) io.close( file ) print("File Encoded!") return true else return false end end function saveTableGame(t, filename) local path = system.pathForFile( "GameSettings.json", system.DocumentsDirectory) local file = io.open(path, "w") if file then local contents = json.encode(t) file:write( contents ) io.close( file ) print("File Encoded!") return true else return false end end function loadTableGame(filename) local path = system.pathForFile( "GameSettings.json", system.DocumentsDirectory) local contents = "" local myTable = {} local file = io.open( path, "r" ) if file then local contents = file:read( "\*a" ) myTable = json.decode(contents); io.close( file ) return myTable end return nil end function loadTableOne(filename) local path = system.pathForFile( "SavedGameOneSettings.json", system.DocumentsDirectory) local contents = "" local myTable = {} local file = io.open( path, "r" ) if file then local contents = file:read( "\*a" ) myTable = json.decode(contents); io.close( file ) return myTable end return nil end --[[Create your GameSettings and SavedGameOneSettings variables here]] if oneFile then print( "File exists" ) oneFile.close(oneFile) else saveTableOne(SavedGameOneSettings, "SavedGameOneSettings.json") print( "SavedGameOneSettings didn't exist, but now IT DOES!" ) end if gameFile then print( "File exists" ) gameFile.close(gameFile) else saveTableGame(GameSettings, "GameSettings.json") print( "GameSettings didn't exist, but now IT DOES!" ) end

 free to copy it at will. I think I just adapted what Rob described in his tutorial. Put this in your main.lua and it will tell you pretty clearly in your terminal if the table exists or not. If you wanted to do something like testvalue[1] and adapt it to this snippet, you could do something like this:

--[[Create your GameSettings and SavedGameOneSettings variables here]] SavedGameOneSettings.testvalue= { 0,0,0,0,0}

The above creates tables somewhat similar to what you were doing. The first code snippet will create the second code snippet and will check to see if it’s created. Now, you can have a function that does this:

local function checkDataTest1() if SavedGameOneSettings.testvalue[1] == 0 then print("JSON STRING SAVE AND LOAD SUCCESS!") end end checkDataTest1()

Try that out and let me know how it works; you should be able to convert the important parts to your needs.

Wow! Works like a charm. The “table exists” works perfectly! I am probably getting a little annoying. How do you save and load stuff using Json? I really don’t want to use mySql lite.

How do you save and load stuff using Json?

It’s as easy as 1-2-3 with the code above. If you take a look at this piece:

saveTableOne(SavedGameOneSettings, "SavedGameOneSettings.json")

That will save your SavedGameOneSettings variables whenever you change them. So, let’s say we do this:

 local function increaseScore() SavedGameOneSettings.testvalue[1] = SavedGameOneSettings.testvalue[1]+10 SavedGameOneSettings.testvalue[2] = SavedGameOneSettings.testvalue[2]+20 saveTableOne(SavedGameOneSettings, "SavedGameOneSettings.json") end

Then, every time you run the increaseScore() function, it increases the score by the given amounts, and saves the entire .json string. To make sure it works for sure, click on File in your Corona Simulator, click on Show Project Sandbox, and click on Documents. This should have all of your json files saved.

Loading files is pretty much the same thing:

SavedGameOneSettings = savemanage.loadTableOne("SavedGameOneSettings.json")

Not as many uses, but still necessary.

The above code I provided isn’t optimized as the functions are running as globals, so you’re going to want to do a bit more reading about external modules (http://www.coronalabs.com/blog/2012/08/28/how-external-modules-work-in-corona/) in order to refine the code. 

Hope this helps! 

Thanks so far! I have one more question and I Think I will be set. How do you save testvalue[1] directly to SavedGameOneSettings?

I tried this:

[lua]

[[

–testvalue stuff

]]

[/lua]

But it didn’t work. I got a syntax error.

 So I just need to know how to save testvalue[1] directly to SavedGameOneSettings.

Those brackets mean that you are commenting something out, which means Corona won’t read what is between them. Step #1 should be to go through the Corona documentation and tutorials and try to build the sample applications they have, to really understand how to use the Lua language.

to answer your specific question, if you take a look at the post from 7:22 AM you’ll see the function there provides code to increase the testvalue amount, and save the table after it’s increase. Take a look at that and see if you can adapt it to your needs.

The ~= symbol means “does not equal”. I think you might want to change that bit to the “does equal” sign of ==. Like below:

If testvalue[1] == nil then

Sorry, I should have mentioned I tried both ways. That doesn’t work either. I’m still getting an error.

I’d say that’s because the testvalue table hasn’t been created when that function runs. Would just using testvalue == nil work?

Good idea, but it still doesn’t work. 

Is there even a way to tell if an item exists in a database? I want to assign prescore to 0 if this is their first time playing. If it isn’t their first time playing, I want to assign prescore to testvalue[1], which was their previous score. So I need to be able to asee if testvalue[1] exists, but I can’t do that, because at the time, it is a nil value.

I’m not going to pretend like I’m Conan the Librarian over here so I can’t really give any tips on DB maintenance. I have implemented json logic into my games and projects to check to see if a user has ever started my game/app before, but I don’t know much about sql and how it is similar/different. 

Would a mix of json and sql work? I’m guess not because it’s like having two engines in your car. Why have one if you’re using the other. There must be a way to loop through a sql DB to check for existing tables and values. 

If you’re not married to sql I’d suggest converting to .json as it’s a bit more intuitive. Just my .02.

Hey, that’s cool.  Oh, could you tell me how you used Json to tell if they had played your game already?

Thanks