In the sqlite query, how to get a specific row and column that have the required value

advertisements

I am trying to build a quiz app and the entries in the database look like this

id | term | synonym 1 | synonym 1 answered | synonym 2 | synonym 2 answered | ....
----------------------------------------------------------------------------------
1  | read | recite    |       Yes          | study     |     No             |
2  | work | effort    |       No           | labour    |     Yes            |

The idea is to present one synonym at a time. Once the next synonym is chosen, the previous synonym is marked as answered using the column next to the synonym with "Yes"

For the logic to select a word I am using a Collection.shuffle() function to get a random row and random column, query database to see if its answered column is "No". If "Yes" I am repeating the shuffle till I get a "No".

For knowing if atleast one entry in the entire table has a "No" in any of the 'answered' columns, I am using an OR clause against the answered columns (to make sure that all synonyms are not already answered)

So my app is doing lot of iterations to get the desired word which is definitely very bad way.

I am unable to figure a way to let sqlite query return me a random row and column that has the word "No". If I can get the column name of the result that had "No", I can strip the word 'answered' and get the related synonym column in a row, and present it with out much of java code.

Can any one kindly enlighten me on this and give a solution? I would require the column name and the rowid of the resulting match of a word "No" in the entire table. And it must be at RANDOM

Edit: The scenario given here is for simplicity. The actual app deals with Sanskrit grammar and requires the kind of implementation I am planning. I require to get the 'name' of the column and 'id' of the row that got a 'No'. In the implementation I won't be using 'Yes' and 'No'. The number of columns would be fixed for all the terms. For simplicity I gave this example.

I would recommend the following:

First normalize your table structure. There will be two tables: Terms and Synonyms

Terms table will have Id and TermName

In the Synonyms table there will be SynonymId, Name, Answered TermId So, you can track synonyms through TermId here.

This way, you can easily query to see what synonyms are still not answered for a specific term like:

SELECT * FROM Synomyms WHERE TermId = 1 AND Answered = "No"

Hope this helps

Related Articles

How do I echo specific rows and columns from csv in a variable?

The below script: #!/bin/bash otscurrent=" AAA,33854,4528,38382,12 BBB,83917,12296,96213,13 CCC,20399,5396,25795,21 DDD,27198,4884,32082,15 EEE,2472,981,3453,28 FFF,3207,851,4058,21 GGG,30621,4595,35216,13 HHH,8450,1504,9954,15 III,4963,2157,7120,30

Simple doctrine query - & gt; a specific row and column

I do have a table called "tax" with two column "id | tax_value". I can list the table with an foreach very well. Now my question, how does the code look like when I just want to have one specfic row and column in the php code? I don't

R-Python: how to eliminate specific rows and columns?

Take this R demo as an example : df <- matrix(1:100, nrow = 10, ncol = 10) df : > df [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [1,] 1 11 21 31 41 51 61 71 81 91 [2,] 2 12 22 32 42 52 62 72 82 92 [3,] 3 13 23 33 43 53 63 73 83 93 [4,] 4 14 2

How to display images in rows and columns?

I am querying some images from database and displaying them on the webpage. However, I would like to display these images in rows and columns as thumbnails. For example: If c1, c2, c3, c4 and c5 are 5 columns and r1, r2, and r3 are 3 rows, then the p

How to import data with row and column headers

I want to import data from a text file with row and column headers, and it in a matrix. For instance, the input file looks as follows: data c1 c2 c3 c4 r1 1 2 3 4 r2 5 6 7 8 Also, is it possible to access the row and column names with the correspondi