Complicated Database Random Record problem

B

BrutalSnyper

Member
Licensed User
Longtime User
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!
 
Sort by date Sort by votes
Harris

Harris

Expert
Licensed User
Longtime User
What is the problem? How to choose a random record?
 
Upvote 0
K

kickaha

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0
B

BrutalSnyper

Member
Licensed User
Longtime User
Harris said:
What is the problem? How to choose a random record?
Click to expand...

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.
 
Upvote 0
mc73

mc73

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0
D

DouglasNYoung

Active Member
Licensed User
Longtime User
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
 
Upvote 0
B

BrutalSnyper

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

Thanks again
 
Upvote 0
B

BrutalSnyper

Member
Licensed User
Longtime User
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)
 
Last edited:
Upvote 0
You must log in or register to reply here.
Top