Complicated Database Random Record problem

BrutalSnyper · Oct 17, 2012

Hi there,

I have a problem that I am seeing as very complicated and I'm struggling to work out how to implement a solution.

I have a system that shows a random record from my database, but I don't want it to show previously shown records until every record has been shown.

Here's how I think it can be solved, every record is assigned a field that contains an integer. The integer starts as value 1, when the record is shown, it sets to value 2.
Then, when all record values are set to 2, a random record is chosen again, it is shown and the value set back to 1. It repeats like this for every record so that every record in the database is viewed before repeating previously viewed records.

The problem is that although I have this in my mind, I am struggling to work out how to actually implement it.

Any help is appreciated, I can attempt to explain more if required. Thanks!

BrutalSnyper · Oct 17, 2012

Any help please?

Harris · Oct 17, 2012

What is the problem? How to choose a random record?

kickaha · Oct 17, 2012

Why not take a slightly different approach.

1) Dim a list and fill it with integers from 1 to the number of records.

2) Shuffle the list elements (google knuth shuffle for a good method)

3) Step through the list and retrieve the record associated with the integer.

4) When you get to the end of the list goto step 2.

BrutalSnyper · Oct 20, 2012

Harris said:
What is the problem? How to choose a random record?

The problem is choosing a random record from a table but not showing a record that has already been shown once.

E.G.

I have these record: 1,2,3,4,5,6,7,8,9,10

One record is chosen randomly, 7.
7 is shown to the user, user then clicks the button to show another random record.
The app could then show record 7 again as it is chosen randomly, but we don't want that, it needs to be a record that the user hasn't seen yet.

So then we show 2,3,5,6,7,9,10 and don't show them again until 1, 4 and 8 have been seen at which point it repeats the process.

BrutalSnyper · Oct 20, 2012

Thank you for your suggestion kickaha. I'm thinking about it now.

mc73 · Oct 20, 2012

A similar approach: Dim a list, choose a random element, remove this element, set random to max the previous length, reset the list when its size goes 0.

DouglasNYoung · Oct 20, 2012

BrutalSnyper,
Something like this should work! I've not tried it, but the basic principle could get you in the right direction...

B4X:

Sub GetUniqueRandomRecord
   Dim sqlstring As String, sqlCursor As Cursor
   Dim RowCount, RandomRow as Integer
   Dim uid, rv as string

   ' Assuming SQL Table named DataTable
   ' Columns of UniqueID Integer, Used Integer, Wanted String

   sqlstring = "Select * from DataTable where Used < 1 order by UniqueID"   
   sqldata.Initialize(File.DirDefaultExternal, "DataDasename.db", False)
   sqlCursor = sqldata.ExecQuery(sqlstring)
   ' Get row count
   RowCount = sqlCursor.RowCount
   ' Get random record...
   ' (I'm assuming that if RowCount = 1 then Rnd(0,0) will give 0 -
   '  if not then an 'if/else' could be uesd for last record!)
   RandomRow = Rnd(0,RowCount-1)
   '

   ' Get Values from random record...
      sqlCursor.Position = RandomRow
   uid = sqlCursor.GetString("UniqueID")
   rv  = sqlCursor.GetString("wanted")

   ' Set used flag to ensure record not used again
   If RowCount = 1 then
       'Reset entire table for reuse!
      sqlstring = "Update DataTable set Used = 0"   
      
   else
      'Set one record as used
      sqlstring = "Update DataTable set used = 1 where UniqueID ='" & uid & "'"   
   end if

   sqldata.ExecNonQuery(sqlstring)
   sqldata.Close


   Return rv
End Sub

Douglas

BrutalSnyper · Oct 24, 2012

Thank you very much for your response Douglas, I'm looking at implementing this now.
I'll report back if it works

Thanks again

BrutalSnyper · Oct 24, 2012

B4X:

revisionnotes_widget_getuniquerandomrecord (java line: 139)


java.lang.IllegalArgumentException
   at java.util.Random.nextInt(Random.java:187)
   at anywheresoftware.b4a.keywords.Common.Rnd(Common.java:194)
   at com.snypersoft.revisionnotes.revisionnotes_widget._getuniquerandomrecord(revisionnotes_widget.java:139)
   at com.snypersoft.revisionnotes.revisionnotes_widget._btnnextnotew_click(revisionnotes_widget.java:95)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:149)
   at anywheresoftware.b4a.objects.RemoteViewsWrapper.raiseEventWithDebuggingSupport(RemoteViewsWrapper.java:137)
   at anywheresoftware.b4a.objects.RemoteViewsWrapper.HandleWidgetEvents(RemoteViewsWrapper.java:114)
   at com.snypersoft.revisionnotes.revisionnotes_widget._service_start(revisionnotes_widget.java:203)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:149)
   at com.snypersoft.revisionnotes.revisionnotes_widget.handleStart(revisionnotes_widget.java:61)
   at com.snypersoft.revisionnotes.revisionnotes_widget.onStartCommand(revisionnotes_widget.java:46)
   at android.app.ActivityThread.handleServiceArgs(ActivityThread.java:2371)
   at android.app.ActivityThread.access$1900(ActivityThread.java:127)
   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1222)
   at android.os.Handler.dispatchMessage(Handler.java:99)
   at android.os.Looper.loop(Looper.java:137)
   at android.app.ActivityThread.main(ActivityThread.java:4507)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:790)
   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:557)
   at dalvik.system.NativeStart.main(Native Method)
revisionnotes_widget_service_start (java line: 203)
java.lang.RuntimeException: java.lang.IllegalArgumentException


   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:191)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:149)
   at anywheresoftware.b4a.objects.RemoteViewsWrapper.raiseEventWithDebuggingSupport(RemoteViewsWrapper.java:137)
   at anywheresoftware.b4a.objects.RemoteViewsWrapper.HandleWidgetEvents(RemoteViewsWrapper.java:114)
   at com.snypersoft.revisionnotes.revisionnotes_widget._service_start(revisionnotes_widget.java:203)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:149)
   at com.snypersoft.revisionnotes.revisionnotes_widget.handleStart(revisionnotes_widget.java:61)
   at com.snypersoft.revisionnotes.revisionnotes_widget.onStartCommand(revisionnotes_widget.java:46)
   at android.app.ActivityThread.handleServiceArgs(ActivityThread.java:2371)
   at android.app.ActivityThread.access$1900(ActivityThread.java:127)
   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1222)
   at android.os.Handler.dispatchMessage(Handler.java:99)
   at android.os.Looper.loop(Looper.java:137)
   at android.app.ActivityThread.main(ActivityThread.java:4507)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:790)
   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:557)
   at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.IllegalArgumentException
   at java.util.Random.nextInt(Random.java:187)
   at anywheresoftware.b4a.keywords.Common.Rnd(Common.java:194)
   at com.snypersoft.revisionnotes.revisionnotes_widget._getuniquerandomrecord(revisionnotes_widget.java:139)
   at com.snypersoft.revisionnotes.revisionnotes_widget._btnnextnotew_click(revisionnotes_widget.java:95)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
   ... 21 more
java.lang.RuntimeException: Unable to start service com.snypersoft.revisionnotes.revisionnotes_widget@4165ece8 with Intent { flg=0x10000000 cmp=com.snypersoft.revisionnotes/.revisionnotes_widget bnds=[379,260][460,341] (has extras) }: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.IllegalArgumentException
   at android.app.ActivityThread.handleServiceArgs(ActivityThread.java:2388)
   at android.app.ActivityThread.access$1900(ActivityThread.java:127)
   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1222)
   at android.os.Handler.dispatchMessage(Handler.java:99)
   at android.os.Looper.loop(Looper.java:137)
   at android.app.ActivityThread.main(ActivityThread.java:4507)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:790)
   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:557)
   at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.IllegalArgumentException
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:191)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:149)
   at com.snypersoft.revisionnotes.revisionnotes_widget.handleStart(revisionnotes_widget.java:61)
   at com.snypersoft.revisionnotes.revisionnotes_widget.onStartCommand(revisionnotes_widget.java:46)
   at android.app.ActivityThread.handleServiceArgs(ActivityThread.java:2371)
   ... 10 more
Caused by: java.lang.RuntimeException: java.lang.IllegalArgumentException
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:191)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:149)
   at anywheresoftware.b4a.objects.RemoteViewsWrapper.raiseEventWithDebuggingSupport(RemoteViewsWrapper.java:137)
   at anywheresoftware.b4a.objects.RemoteViewsWrapper.HandleWidgetEvents(RemoteViewsWrapper.java:114)
   at com.snypersoft.revisionnotes.revisionnotes_widget._service_start(revisionnotes_widget.java:203)
   at java.lang.reflect.Method.invokeNative(Native Method)


   at java.lang.reflect.Method.invoke(Method.java:511)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
   ... 14 more
Caused by: java.lang.IllegalArgumentException
   at java.util.Random.nextInt(Random.java:187)
   at anywheresoftware.b4a.keywords.Common.Rnd(Common.java:194)
   at com.snypersoft.revisionnotes.revisionnotes_widget._getuniquerandomrecord(revisionnotes_widget.java:139)
   at com.snypersoft.revisionnotes.revisionnotes_widget._btnnextnotew_click(revisionnotes_widget.java:95)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:511)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
   ... 21 more

I'm getting these bunch of errors, which seem to be because of the random number part:

B4X:

RandomRow = Rnd(0, RowCount-1)

Erel · Oct 24, 2012

The second parameter must be larger than the first one.

BrutalSnyper · Oct 24, 2012

Erel said:
The second parameter must be larger than the first one.

Ah, thank you Erel!

/facepalm

Complicated Database Random Record problem

BrutalSnyper

Member

BrutalSnyper

Member

Harris

Expert

kickaha

Well-Known Member

BrutalSnyper

Member

BrutalSnyper

Member

mc73

Well-Known Member

DouglasNYoung

Active Member

BrutalSnyper

Member

BrutalSnyper

Member

Erel

B4X founder

BrutalSnyper

Member

Similar Threads

Complicated Database Random Record problem

Member

Member

Expert

Well-Known Member

Member

Member

Well-Known Member

Active Member

Member

Member

B4X founder

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency