Sqlite question

[BTS]

Hi Experts, 

1. I created a sqlite table, and i am able to get/display a random row from the table in the stimulator.   I would like to print the row id of this random row, so that i can carry out the next functions.

My end goal is to use the next button to go to next row in the table and so on… Also I want to have  previous button to get the previous row.   

2.  how can i randomize and display the columns as well? is there any corona code or sqlite code for that?

3. Most of the tutorials about sqlite are  about reading, updating the database. What else can i do with the database? Are there any sample codes or tutorials on that. I am interested in using sqlite for educational apps. like quiz …

Look forward to your help.

Thanks

[rob]

Let me go backwards in your questions.

3.  A database holds records of data.  Therefore, you can create new records, read existing records, update existing records and delete existing records. This is known as CRUD - Create, Read, Update, Delete.  Some databases provide build in functions for helping find the data, compute dates, etc. But for the most part, the database’s job is to keep the records and help you find them, returning them to you so *you* can manipulate them.

2. You would have to read a record into a table and figure out how to write it back to the database.  Depending on what you need, you might not want the database to change, but temporarily randomize the data in memory.

For #1, I think we are going to need to know what your database scheme is.  How are you identifying your records.

[ToeKnee]

It would help a lot to see your code of how you get your data and then display it.

If i understand you correctly this is quite a simple procedure.

For me all of my data is associated with number- i forget the correct term for the sqlite but it’s the lookup data

so 

[lua]for row in gEz.db:nrows(“SELECT * FROM myData WHERE Num=”…choice) do

[/lua]

choice is just a variable containing a number. - and you may in fact be using something similar as your “random” ID 

So you could create a function to increase choice by 1 or reduce choice by 1 then call the sqlite function to get the data for the new choice

As for your other Q’s - and again i’m not sure i understand exactly what you are wanting to do - but try thinking of things separately.

Your data is in sqlite database - you have said that you are able to get the data out and display - that is actually 2 steps

get your data out and i presume into a Variable - “Data” and then you do 2 - display that variable (“text”) - and displaying that variable has nothing to do with sqlite - yes it started it’s journey from sqlite but in reality you could easily change the “text” to “newtext” with one line of code

[lua] display.text = “newtext”[/lua]

and then it will have absolutely nothing to do with sqlite!

either way i’m starting to ramble here - as Rob said we need to see some code and also how you have organised your data in sqlite.

But i will say how you have organised your data in the sqlite db goes a big way towards making the coding simple.

T.

[BTS]

@Rob & @Toeknee  - Appreciate your rescue effort on a weekend. Thanks for your help.

My db schema is - Qid, Question, Opt1, Opt2, Opt3, Opt 4. Opt4 is the correct answer.

Regret not including the code earlier. Here i am putting it out now…

1. Now I am able to get random question & also print the Qid of the same, but am not able to go to the next question.

 @Toeknee - it seems i am not able to pass it (the Qid) off as a variable.

2. Also to randomize the columns (options) - i am still not able to get it done. 

local sql = “SELECT * FROM QuizCorona ORDER BY RANDOM() LIMIT 1”

–n = math.random(#row.Qid)       –  Shows error    

–print (n)

for row in db:nrows(sql) do
    print (“Row=>” …row.Qid)   – Able to print the Qid of the Question on display.
    --c = row.Qid
     – c = row.Qid +1
    --Print©
    local text = row.Question
    local t = display.newText(text, 50, 50, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text1 = row.Opt1
    local t1 = display.newText(text1, 50, 100, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text2 = row.Opt2
    local t2 = display.newText(text2, 200, 100, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text3 = row.Opt3
    local t3 = display.newText(text3, 50, 200 , native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text4 = row.Opt4
    local t4 = display.newText(text4, 200, 200, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
end
 

[ToeKnee]

I am far from an expert so please take my advice with that in mind.

1st - from what i understand when you declare local - they will only be seen where they have been declared.

In this case - you appear to declare within your database for loop - thus it may fuse problems if you want to do things relating to those variables outside of the for loop.

2nd - when you say next question do you mean “Next” as in the one after current (sequentially) - or “another different” question(random)?

3rd - I assume the colours aren’t causing problems (255, 255, 255) instead of (1, 1, 1)

have never used “BY RANDOM() limit 1” before so can’t help there.

4th - You obviously want to randomise the position of the answers b/c if you don’t then player will always be touching t4 located at 200, 200.

Something to consider (but is just how i code) i like actually adding the X & Y lines separately.

t4.x = 200

t4.y = 200

by adding some randomisation to these positioning lines you won’t have to deal with tracking the correct answer touched

it will always be t4 no matter where on the screen it is located

[lua]

local sql = “SELECT * FROM QuizCorona ORDER BY RANDOM() LIMIT 1”

–n = math.random(#row.Qid)       –  Shows error    

–print (n)

for row in db:nrows(sql) do

    print (“Row=>” …row.Qid)   – Able to print the Qid of the Question on display.

    --c = row.Qid

     – c = row.Qid +1

    --Print©

    local text = row.Question

    local t = display.newText(text, 50, 50, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text1 = row.Opt1

    local t1 = display.newText(text1, 50, 100, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text2 = row.Opt2

    local t2 = display.newText(text2, 200, 100, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text3 = row.Opt3

    local t3 = display.newText(text3, 50, 200 , native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text4 = row.Opt4

    local t4 = display.newText(text4, 200, 200, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

end

[/lua]

edit - interesting i was able to post above your post - must be a time zone thing - anyway i posted your code in the lua format to make it easier to read.

[ToeKnee]

line 3 

Unless you called the Db previously there’s no way i can think of that 

–n = math.random(#row.Qid)       –  Shows error

Can know what #row.qid IS.

How many questions in your database try using

n= math.random(min,max)

T.

[ToeKnee]

Random location of options.

a Not pretty solution but simple - only 4 options ABCD, ACDB, ADCB, ACBD, ADBC …… There are at a guess 16 different ways you could present them

Do random number between 1 & 16 

then 

If layout random == 1 then

t1xAdj = 

t1yAdj =

t2xAdj = 

t2yAdj =

t3xAdj = 

t3yAdj =

t4xAdj = 

t4yAdj =

elseIf layout random == 2 then

t1xAdj = 

t1yAdj =

t2xAdj = 

t2yAdj =

t3xAdj = 

t3yAdj =

t4xAdj = 

t4yAdj =

……

end

Do this before the db call and then when using t1.x = …. include t1xAdj in the calculation.

Yes it’s long but the concept is easy to understand and being a learner you can just get it done - then later as you refine your code look for ways to do it better - it provides a good base for the learning process.

T.

[rob]

I’ve never used Order by Random() before.  I don’t now what that behavior is.  I did a trivia game.  I do a query to get all the questions in a category (just the IDs), and store them in a table.  I then run a shuffle table method to randomize them and then if I want 20 questions, I’ll query the questions and keep them in a Corona table.  Now you have a table that you can move forward and backward in.

Rob

[rob]

Let me go backwards in your questions.

3.  A database holds records of data.  Therefore, you can create new records, read existing records, update existing records and delete existing records. This is known as CRUD - Create, Read, Update, Delete.  Some databases provide build in functions for helping find the data, compute dates, etc. But for the most part, the database’s job is to keep the records and help you find them, returning them to you so *you* can manipulate them.

2. You would have to read a record into a table and figure out how to write it back to the database.  Depending on what you need, you might not want the database to change, but temporarily randomize the data in memory.

For #1, I think we are going to need to know what your database scheme is.  How are you identifying your records.

[ToeKnee]

It would help a lot to see your code of how you get your data and then display it.

If i understand you correctly this is quite a simple procedure.

For me all of my data is associated with number- i forget the correct term for the sqlite but it’s the lookup data

so 

[lua]for row in gEz.db:nrows(“SELECT * FROM myData WHERE Num=”…choice) do

[/lua]

choice is just a variable containing a number. - and you may in fact be using something similar as your “random” ID 

So you could create a function to increase choice by 1 or reduce choice by 1 then call the sqlite function to get the data for the new choice

As for your other Q’s - and again i’m not sure i understand exactly what you are wanting to do - but try thinking of things separately.

Your data is in sqlite database - you have said that you are able to get the data out and display - that is actually 2 steps

get your data out and i presume into a Variable - “Data” and then you do 2 - display that variable (“text”) - and displaying that variable has nothing to do with sqlite - yes it started it’s journey from sqlite but in reality you could easily change the “text” to “newtext” with one line of code

[lua] display.text = “newtext”[/lua]

and then it will have absolutely nothing to do with sqlite!

either way i’m starting to ramble here - as Rob said we need to see some code and also how you have organised your data in sqlite.

But i will say how you have organised your data in the sqlite db goes a big way towards making the coding simple.

T.

[BTS]

@Rob & @Toeknee  - Appreciate your rescue effort on a weekend. Thanks for your help.

My db schema is - Qid, Question, Opt1, Opt2, Opt3, Opt 4. Opt4 is the correct answer.

Regret not including the code earlier. Here i am putting it out now…

1. Now I am able to get random question & also print the Qid of the same, but am not able to go to the next question.

 @Toeknee - it seems i am not able to pass it (the Qid) off as a variable.

2. Also to randomize the columns (options) - i am still not able to get it done. 

local sql = “SELECT * FROM QuizCorona ORDER BY RANDOM() LIMIT 1”

–n = math.random(#row.Qid)       –  Shows error    

–print (n)

for row in db:nrows(sql) do
    print (“Row=>” …row.Qid)   – Able to print the Qid of the Question on display.
    --c = row.Qid
     – c = row.Qid +1
    --Print©
    local text = row.Question
    local t = display.newText(text, 50, 50, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text1 = row.Opt1
    local t1 = display.newText(text1, 50, 100, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text2 = row.Opt2
    local t2 = display.newText(text2, 200, 100, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text3 = row.Opt3
    local t3 = display.newText(text3, 50, 200 , native.systemFont ,14)
    t:setFillColor(255,255,255)
    
    local text4 = row.Opt4
    local t4 = display.newText(text4, 200, 200, native.systemFont ,14)
    t:setFillColor(255,255,255)
    
end
 

[ToeKnee]

I am far from an expert so please take my advice with that in mind.

1st - from what i understand when you declare local - they will only be seen where they have been declared.

In this case - you appear to declare within your database for loop - thus it may fuse problems if you want to do things relating to those variables outside of the for loop.

2nd - when you say next question do you mean “Next” as in the one after current (sequentially) - or “another different” question(random)?

3rd - I assume the colours aren’t causing problems (255, 255, 255) instead of (1, 1, 1)

have never used “BY RANDOM() limit 1” before so can’t help there.

4th - You obviously want to randomise the position of the answers b/c if you don’t then player will always be touching t4 located at 200, 200.

Something to consider (but is just how i code) i like actually adding the X & Y lines separately.

t4.x = 200

t4.y = 200

by adding some randomisation to these positioning lines you won’t have to deal with tracking the correct answer touched

it will always be t4 no matter where on the screen it is located

[lua]

local sql = “SELECT * FROM QuizCorona ORDER BY RANDOM() LIMIT 1”

–n = math.random(#row.Qid)       –  Shows error    

–print (n)

for row in db:nrows(sql) do

    print (“Row=>” …row.Qid)   – Able to print the Qid of the Question on display.

    --c = row.Qid

     – c = row.Qid +1

    --Print©

    local text = row.Question

    local t = display.newText(text, 50, 50, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text1 = row.Opt1

    local t1 = display.newText(text1, 50, 100, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text2 = row.Opt2

    local t2 = display.newText(text2, 200, 100, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text3 = row.Opt3

    local t3 = display.newText(text3, 50, 200 , native.systemFont ,14)

    t:setFillColor(255,255,255)

    

    local text4 = row.Opt4

    local t4 = display.newText(text4, 200, 200, native.systemFont ,14)

    t:setFillColor(255,255,255)

    

end

[/lua]

edit - interesting i was able to post above your post - must be a time zone thing - anyway i posted your code in the lua format to make it easier to read.

[ToeKnee]

line 3 

Unless you called the Db previously there’s no way i can think of that 

–n = math.random(#row.Qid)       –  Shows error

Can know what #row.qid IS.

How many questions in your database try using

n= math.random(min,max)

T.

[ToeKnee]

Random location of options.

a Not pretty solution but simple - only 4 options ABCD, ACDB, ADCB, ACBD, ADBC …… There are at a guess 16 different ways you could present them

Do random number between 1 & 16 

then 

If layout random == 1 then

t1xAdj = 

t1yAdj =

t2xAdj = 

t2yAdj =

t3xAdj = 

t3yAdj =

t4xAdj = 

t4yAdj =

elseIf layout random == 2 then

t1xAdj = 

t1yAdj =

t2xAdj = 

t2yAdj =

t3xAdj = 

t3yAdj =

t4xAdj = 

t4yAdj =

……

end

Do this before the db call and then when using t1.x = …. include t1xAdj in the calculation.

Yes it’s long but the concept is easy to understand and being a learner you can just get it done - then later as you refine your code look for ways to do it better - it provides a good base for the learning process.

T.

[rob]

I’ve never used Order by Random() before.  I don’t now what that behavior is.  I did a trivia game.  I do a query to get all the questions in a category (just the IDs), and store them in a table.  I then run a shuffle table method to randomize them and then if I want 20 questions, I’ll query the questions and keep them in a Corona table.  Now you have a table that you can move forward and backward in.

Rob