Share My Creation JQuiz - API Server for B4XQuiz

This is the full source code for B4XQuiz server.
SQL script to generate database in post #2

For B4XQuiz client app, please check: https://www.b4x.com/android/forum/threads/b4jquiz-desktop-app.124202/

Update
It is now easier to create Web API Server using B4X Template Check: https://www.b4x.com/android/forum/threads/web-api-template-2.143310/

PHP Frontend demo
URL: https://quiz.computerise.net/frontend/
Login
name: demo
password: demo

Update 09/05/2023: PHP Frontend source is now available.

PHP Backend demo
URL: https://quiz.computerise.net/backend/
Login
name: admin
password: admin

Source is not available for download.
Warning: Do not edit/delete Admin user!

'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region

' MySQL Connector
#AdditionalJar: mysql-connector-java-5.1.37-bin

Sub Process_Globals
    Public srvr As Server
    Public const ELEMENT_ROOT As Int = 0
    Public const ELEMENT_ACTION As Int = 1
    Public const ELEMENT_ID As Int = 2
    Public const MAX_ELEMENTS As Int = 3
    Public const VERSION As Float = 1.02
End Sub

Sub AppStart (Args() As String)
    Dim config As Map = Utility.LoadConfigMap
    srvr.Initialize("")
    #If RELEASE
    ConfigureSSL(config.Get("SSLPort"))
    #End If
    srvr.Port = config.Get("ServerPort")
    srvr.AddHandler("", "APIHandler", False)
    srvr.AddHandler("/*", "APIHandler", False)
    srvr.Start
    Log($"API server (version = $1.2{VERSION}) is running on port ${srvr.Port}"$)
    #If RELEASE
    Log($"Response will be redirected to port ${srvr.SslPort}"$)
    #End If
    StartMessageLoop
End Sub

Private Sub ConfigureSSL (SslPort As Int)
    'example of SSL connector configuration
    Dim ssl As SslConfiguration
    ssl.Initialize
    #If RELEASE
    ssl.SetKeyStorePath("/etc/letsencrypt/live/api.puterise.com", "jetty.keystore") 'path to keystore file
    #End If
    ssl.KeyStorePassword = "keystorepassword"
    'ssl.KeyManagerPassword = "passwordkeystore"
    srvr.SetSslConfiguration(ssl, SslPort)
    'add filter to redirect all traffic from http to https (optional)
    srvr.AddFilter("/*", "HttpsFilter", False)
End Sub

'Handler class
Sub Class_Globals
    Dim Request As ServletRequest
    Dim Response As ServletResponse
    Dim pool As ConnectionPool
End Sub

Public Sub Initialize
 
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    Dim elements() As String = Regex.Split("/", req.RequestURI)
    If elements.Length = 0 Then
        Utility.ReturnConnect(Response)
        Return
    'Else If elements.Length > Main.MAX_ELEMENTS Then
    '    Response.SendError(500, "Unknown action")
    '    Return
    End If
 
    Dim ActionList As List
    ActionList.Initialize2(Array As String("register", "login", "results", "answers", "topics", "questions", "question"))
 
    If ActionList.IndexOf(elements(Main.ELEMENT_ACTION)) > -1 Then
        OpenConnection
    End If
 
    Select elements(Main.ELEMENT_ACTION)
        Case "connect"
            Utility.ReturnConnect(Response)
        Case "register"
            Register
        Case "login"
            Login
        Case "results"
            GetResults
        Case "answers"
            PutAnswers
        Case "topics"
            GetTopics
        Case "questions"
            If elements.Length < Main.MAX_ELEMENTS Then Return
            GetQuestions(elements(Main.ELEMENT_ID))
        Case "question"
            If elements.Length < Main.MAX_ELEMENTS Then Return
            GetQuestion(elements(Main.ELEMENT_ID))
        Case Else
            Response.SendError(500, "Unknown action")
            Return
    End Select
 
    If ActionList.IndexOf(elements(Main.ELEMENT_ACTION)) > -1 Then
        CloseConnection
    End If
End Sub

Sub OpenConnection
    Try
        Dim MaxPoolSize As Int
        Dim config As Map = Utility.LoadConfigMap
        pool.Initialize(config.Get("DriverClass"), _
        config.Get("JdbcUrl"), _
        config.Get("User"), _
        config.Get("Password"))
        MaxPoolSize = config.Get("MaxPoolSize")
        ' change pool size...
        ' Credit to Harris
        ' https://www.b4x.com/android/forum/threads/poolconnection-problem-connection-has-timed-out.95067/post-600974
        Dim jo As JavaObject = pool
        jo.RunMethod("setMaxPoolSize", Array(MaxPoolSize))
    Catch
        Log(LastException.Message)
    End Try
End Sub

Sub CloseConnection
    If pool.IsInitialized Then pool.ClosePool ' Release connection to pool?
End Sub

Sub Request2Map As Map
    Try
        Dim data As Map
        Dim ins As InputStream = Request.InputStream
        If ins.BytesAvailable = 0 Then
            Return Null
        End If
        Dim tr As TextReader
        tr.Initialize(ins)
        Dim json As JSONParser
        json.Initialize(tr.ReadAll)
        data = json.NextObject
    Catch
        Log("[Request2Map] " & LastException)
    End Try
    Return data
End Sub

Sub Register
    Dim con As SQL = pool.GetConnection
    Dim res As ResultSet
    Dim strSQL As String
    Try
        Dim Map1 As Map = Request2Map
        If Map1 = Null Or Map1.IsInitialized = False Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
 
        Dim uid As String = Map1.Get("uid")
        Dim pwd As String = Map1.Get("pwd")
        If uid = "" Or pwd = "" Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
 
        strSQL = "SELECT"
        strSQL = strSQL & " id"
        strSQL = strSQL & " FROM users"
        strSQL = strSQL & " WHERE name = ?"
        res = con.ExecQuery2(strSQL, Array As String(uid))
        If res.NextRow Then
            Utility.ReturnError("Error-User-Exist", Response)
            con.Close
            Return
        Else
            strSQL = "INSERT INTO users"
            strSQL = strSQL & " (name,"
            strSQL = strSQL & " password)"
            strSQL = strSQL & " VALUES (?, md5(?))"
            con.ExecNonQuery2(strSQL, Array As String(uid, pwd))
            Dim Map2 As Map = CreateMap("register": 1)
            Utility.ReturnSuccess(Map2, Response)
            con.Close
            Return
        End If
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub Login
    Dim con As SQL = pool.GetConnection
    Dim res As ResultSet
    Dim strSQL As String
    Try
        Dim Map1 As Map = Request2Map
        If Map1 = Null Or Map1.IsInitialized = False Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
        Dim uid As String = Map1.Get("uid")
        Dim pwd As String = Map1.Get("pwd")
        If uid = "" Or pwd = "" Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
 
        strSQL = "SELECT"
        strSQL = strSQL & " id AS `result`,"
        strSQL = strSQL & " 'success' AS `message`,"
        strSQL = strSQL & " name"
        strSQL = strSQL & " FROM users"
        strSQL = strSQL & " WHERE name = ?"
        strSQL = strSQL & " AND password = md5(?)"
        res = con.ExecQuery2(strSQL, Array As String(uid, pwd))
        If res.NextRow Then
            Dim Map2 As Map = CreateMap("login": 1)
            Utility.ReturnSuccess(Map2, Response)
        Else
            Utility.ReturnError("Error-User-Denied", Response)
        End If
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub GetTopics
    Dim con As SQL = pool.GetConnection
    Dim res As ResultSet
    Dim strSQL As String
    Try
        strSQL = "SELECT"
        strSQL = strSQL & " id,"
        strSQL = strSQL & " topic"
        strSQL = strSQL & " FROM topics"
        strSQL = strSQL & " WHERE enabled = 1"
        strSQL = strSQL & " ORDER BY id"
        res = con.ExecQuery(strSQL)
        Dim List2 As List
        List2.Initialize
        Do While res.NextRow
            List2.Add(CreateMap("id": res.GetInt("id"), "topic": res.GetString("topic")))
        Loop
        Utility.ReturnSuccess2(List2, Response)
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub GetResults
    Dim con As SQL = pool.GetConnection
    Dim strSQL As String
    Try
        Dim Map1 As Map = Request2Map
        If Map1 = Null Or Map1.IsInitialized = False Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
        Dim uid As String = Map1.Get("uid")
        Dim pwd As String = Map1.Get("pwd")
        Dim topic As String = Map1.Get("topic")
        If uid = "" Or pwd = "" Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
        strSQL = "SELECT"
        strSQL = strSQL & " t.id,"
        strSQL = strSQL & " t.topic,"
        strSQL = strSQL & " t.shortdesc,"
        strSQL = strSQL & " IFNULL(r.score, '') AS score"
        strSQL = strSQL & " FROM results r"
        strSQL = strSQL & " JOIN users u"
        strSQL = strSQL & " ON r.user = u.id"
        strSQL = strSQL & " AND u.name = ?"
        strSQL = strSQL & " AND u.password = md5(?)"
        strSQL = strSQL & " RIGHT JOIN topics t"
        strSQL = strSQL & " ON t.id = r.topic"
        If topic = "" Or topic = "null" Then
            strSQL = strSQL & " WHERE t.enabled = 1"
            strSQL = strSQL & " ORDER BY t.id"
            Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(uid, pwd))
        Else
            strSQL = strSQL & " WHERE t.id = ?"
            Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(uid, pwd, topic))
        End If
        Dim List2 As List
        List2.Initialize
        Do While res.NextRow
            List2.Add(CreateMap("id": res.GetInt("id"), _
            "topic": res.GetString("topic"), _
            "shortdesc": res.GetString("shortdesc"), _
            "score": res.GetString("score")))
        Loop
        Utility.ReturnSuccess2(List2, Response)
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub GetQuestion(id As String)
    Dim con As SQL = pool.GetConnection
    Dim strSQL As String
    Try
        Dim qid As Int
        Dim question As String
        Dim correct As Int
        Dim Answers As List
        Dim Questions As List
        Answers.Initialize
        Questions.Initialize
        strSQL = "SELECT"
        strSQL = strSQL & " q.id AS qid,"
        strSQL = strSQL & " q.question,"
        strSQL = strSQL & " q.answer AS correct,"
        strSQL = strSQL & " a.id AS aid,"
        strSQL = strSQL & " a.answer"
        strSQL = strSQL & " FROM questions q"
        strSQL = strSQL & " LEFT JOIN answers a"
        strSQL = strSQL & " ON q.id = a.question"
        strSQL = strSQL & " AND a.enabled = 1"
        strSQL = strSQL & " WHERE q.enabled = 1"
        strSQL = strSQL & " AND q.id = ?"
        strSQL = strSQL & " ORDER BY a.id"
        Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(id))
        Do While res.NextRow
            Dim ans As Map
            ans.Initialize
            For i = 0 To res.ColumnCount - 1
                Select res.GetColumnName(i)
                    Case "qid"
                        qid = res.GetInt2(i)
                    Case "question"
                        question = res.GetString2(i)
                    Case "correct"
                        correct = res.GetInt2(i)
                    Case "aid"
                        ans.Put(res.GetColumnName(i), res.GetInt2(i))
                    Case "answer"
                        ans.Put(res.GetColumnName(i), res.GetString2(i))
                End Select
            Next
            Answers.Add(ans)
        Loop
        If qid > 0 Then
            Questions.Add(CreateMap("qid": qid, "question": question, "correct": correct, "answers": Answers))
            Utility.ReturnSuccess2(Questions, Response)
        Else
            Utility.ReturnError("Error-No-Result", Response)
        End If
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub GetQuestions(topic As String)
    Dim con As SQL = pool.GetConnection
    Dim strSQL As String
    Try
        Dim qid As Int
        Dim List1 As List
        Dim Answers As List
        Dim Questions As List
        List1.Initialize
        Answers.Initialize
        Questions.Initialize
 
        strSQL = "SELECT"
        strSQL = strSQL & " q.id AS qid,"
        strSQL = strSQL & " q.question,"
        strSQL = strSQL & " q.answer AS correct,"
        strSQL = strSQL & " a.id AS aid,"
        strSQL = strSQL & " a.answer"
        strSQL = strSQL & " FROM questions q"
        strSQL = strSQL & " LEFT JOIN answers a"
        strSQL = strSQL & " ON q.id = a.question"
        strSQL = strSQL & " AND a.enabled = 1"
        strSQL = strSQL & " WHERE q.enabled = 1"
        strSQL = strSQL & " AND q.topic = ?"
        strSQL = strSQL & " GROUP BY q.id, a.id"
        strSQL = strSQL & " ORDER BY q.id, a.id"
        Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(topic))
        Do While res.NextRow
            If qid <> res.GetInt("qid") Then
                Questions.Add(CreateMap("qid": res.GetInt("qid"), "question": res.GetString("question"), "correct": res.GetInt("correct")))
                qid = res.GetInt("qid")
            End If
            Answers.Add(CreateMap("qid": res.GetInt("qid"), "aid": res.GetInt("aid"), "answer": res.GetString("answer")))
        Loop
        res.Close
        If Questions.Size > 0 Then
            For Each que As Map In Questions
                Dim List2 As List
                List2.Initialize
                For Each ans As Map In Answers
                    If que.Get("qid") = ans.Get("qid") Then
                        List2.Add(CreateMap("aid": ans.Get("aid"), "answer": ans.Get("answer")))
                    End If
                Next
                List1.Add(CreateMap("qid": que.Get("qid"), "question": que.Get("question"), "correct": que.Get("correct"), "answers": List2))
            Next
        End If
        Utility.ReturnSuccess2(List1, Response)
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub PutAnswers
    Dim con As SQL = pool.GetConnection
    Dim strSQL As String
    Try
        Dim Map1 As Map = Request2Map
        Log(Map1)
        If Map1 = Null Or Map1.IsInitialized = False Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
        If Map1.ContainsKey("uid") = False Or _
            Map1.ContainsKey("pwd") = False Or _
            Map1.ContainsKey("topic") = False Or _
            Map1.ContainsKey("submitted") = False Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
 
        Dim uid As String = Map1.Get("uid")
        Dim pwd As String = Map1.Get("pwd")
        Dim topic As String = Map1.Get("topic")
        Dim submitted As Map = Map1.Get("submitted")
        If uid = "" Or pwd = "" Or topic = "" Then
            Utility.ReturnError("Error-No-Value", Response)
            con.Close
            Return
        End If
        'Log(submitted)
        ' Marking answers
        Dim score As Int
        Dim total As Int
        For i = 0 To submitted.Size - 1
            strSQL = "SELECT"
            strSQL = strSQL & " answer"
            strSQL = strSQL & " FROM questions"
            strSQL = strSQL & " WHERE topic = ?"
            strSQL = strSQL & " AND id = ?"
            Dim row As ResultSet = con.ExecQuery2(strSQL, Array As String(topic, submitted.GetKeyAt(i)))
            Do While row.NextRow
                If row.GetInt2(0) = submitted.GetValueAt(i) Then
                    score = score + 1
                End If
            Loop
            row.Close
            total = total + 1
        Next
 
        strSQL = "SELECT"
        strSQL = strSQL & " id"
        strSQL = strSQL & " FROM users"
        strSQL = strSQL & " WHERE name = ?"
        strSQL = strSQL & " AND password = md5(?)"
        Dim usr As ResultSet = con.ExecQuery2(strSQL, Array As String(uid, pwd))
        If usr.NextRow Then
            ' Find existing record and Insert/Update
            strSQL = "SELECT"
            strSQL = strSQL & " id"
            strSQL = strSQL & " FROM results"
            strSQL = strSQL & " WHERE topic = ?"
            strSQL = strSQL & " AND user = ?"
            Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(topic, usr.GetInt("id")))
            If res.NextRow Then
                strSQL = "UPDATE results"
                strSQL = strSQL & " SET score = ?,"
                strSQL = strSQL & " modified_date = now()"
                strSQL = strSQL & " WHERE topic = ?"
                strSQL = strSQL & " AND user = ?"
                strSQL = strSQL & " AND id = ?"
                con.ExecNonQuery2(strSQL, Array As String(score & "/" & total, topic, usr.GetInt("id"), res.GetInt("id")))
                Utility.ReturnSuccess2(Null, Response)
            Else
                strSQL = "INSERT INTO results"
                strSQL = strSQL & " (user, topic, score) VALUES"
                strSQL = strSQL & " (?, ?, ?)"
                con.ExecNonQuery2(strSQL, Array As String(usr.GetInt("id"), topic, score & "/" & total))
                Utility.ReturnSuccess2(Null, Response)
            End If
            res.Close
        Else
            Utility.ReturnError("Error-User-Denied", Response)
        End If
        usr.Close
    Catch
        Log(LastException)
        Utility.ReturnError("Error-Execute-Query", Response)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Sub Process_Globals

End Sub

Sub LoadConfigMap As Map
    Return File.ReadMap(File.DirAssets, "config.properties")
End Sub

Sub Map2Json(M As Map) As String
    Dim gen As JSONGenerator
    gen.Initialize(M)
    Return gen.ToString
End Sub

Sub ReturnConnect(resp As ServletResponse)
    Dim Map1 As Map = CreateMap("Connected": 1)
    resp.ContentType = "application/json"
    resp.Write(Map2Json(Map1))
End Sub

Sub ReturnError(Message As String, resp As ServletResponse)
    If Message = "" Then Message = "unknown"
    Dim List1 As List
    List1.Initialize
    Dim Map1 As Map = CreateMap("s": "failed", "r": List1, "e": Message)
    resp.ContentType = "application/json"
    resp.Write(Map2Json(Map1))
End Sub

Sub ReturnSuccess(Map As Map, resp As ServletResponse)
    If Map.IsInitialized = False Then Map.Initialize
    Dim Result As List
    Result.Initialize
    Result.Add(Map)
    Dim Map1 As Map = CreateMap("s": "success", "r": Result, "e": Null)
    resp.ContentType = "application/json"
    resp.Write(Map2Json(Map1))
End Sub

Sub ReturnSuccess2(Result As List, resp As ServletResponse)
    If Result.IsInitialized = False Then Result.Initialize
    Dim Map1 As Map = CreateMap("s": "success", "r": Result, "e": Null)
    resp.ContentType = "application/json"
    resp.Write(Map2Json(Map1))
End Sub

#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
DriverClass=com.mysql.jdbc.Driver
JdbcUrl=jdbc:mysql://localhost/b4x_quiz?characterEncoding=utf8
MaxPoolSize=100
User=aeric
Password=1LoveB4X

#Java server port
ServerPort=17180
SSLPort=17182

 

[aeric]

-- Adminer 4.7.7 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `answers`;
CREATE TABLE `answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `answer` varchar(255) NOT NULL,
  `question` int(11) NOT NULL DEFAULT '0',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime DEFAULT NULL,
  `enabled` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `answers` (`id`, `answer`, `question`, `created_date`, `modified_date`, `enabled`) VALUES
(1,    'MySQL',    3,    '2020-08-08 18:05:11',    NULL,    1),
(2,    'SQLite',    3,    '2020-08-08 18:30:00',    NULL,    1),
(3,    'PHP',    3,    '2020-08-08 18:34:22',    '2020-08-09 03:02:20',    1),
(4,    'True',    2,    '2020-08-08 18:46:23',    NULL,    1),
(5,    'False',    2,    '2020-08-08 18:46:30',    NULL,    1),
(6,    'B4A',    1,    '2020-08-08 18:47:00',    NULL,    1),
(7,    'B4J',    1,    '2020-08-08 18:47:10',    NULL,    1),
(8,    'B4i',    1,    '2020-08-08 18:47:20',    NULL,    1),
(9,    'B4i runs on Windows 10.',    4,    '2020-08-09 07:35:47',    NULL,    1),
(10,    'B4i can develop mac OS desktop app.',    4,    '2020-08-09 07:36:35',    NULL,    1),
(11,    'B4i is use to build and release app in jar format.',    4,    '2020-08-09 07:38:30',    '2020-08-15 04:30:49',    1),
(12,    'VSCode',    1,    '2020-08-10 14:15:23',    '2020-08-15 04:32:05',    1),
(13,    'Android',    5,    '2020-08-29 16:07:55',    NULL,    1),
(14,    'Microsoft Office',    5,    '2020-08-29 16:08:43',    NULL,    1),
(15,    'iOS',    5,    '2020-08-29 16:08:54',    NULL,    1),
(16,    'C stands for Create new record in database',    6,    '2020-10-05 19:12:36',    '2020-10-27 19:10:37',    1),
(17,    'R stands for Read or retrieve saved records',    6,    '2020-10-05 19:14:14',    '2020-10-27 19:11:07',    1),
(18,    'B4J is a 100% free development tool for desktop, server and IoT solutions.',    7,    '2020-10-27 10:33:55',    NULL,    1),
(19,    'With B4J you can easily create desktop applications (UI), console programs (non-UI) and server solutions.',    7,    '2020-10-27 10:34:12',    NULL,    1),
(20,    'The compiled apps can run on Windows, Mac, Linux and ARM boards (such as Raspberry Pi).',    7,    '2020-10-27 10:34:23',    NULL,    1),
(21,    'All of the above.',    7,    '2020-10-27 10:34:36',    NULL,    1),
(22,    'B4J creates cross platform desktop application that run on Windows, Mac, Linux and ARM boards.',    8,    '2020-10-27 10:52:19',    NULL,    1),
(23,    'B4A creates mobile apps that run on Apple iOS and iPad OS.',    8,    '2020-10-27 10:54:41',    '2020-10-27 18:55:27',    1),
(24,    'B4R is a 100% free development tool for native Arduino, ESP8266 and ESP32 programs.',    8,    '2020-10-27 10:56:28',    NULL,    1),
(25,    'B4i with the hosted Mac builder allow you to develop iOS applications on Windows.',    8,    '2020-10-27 11:03:19',    NULL,    1),
(26,    'U stands for Update or modify existing records.',    6,    '2020-10-27 11:12:42',    NULL,    1),
(27,    'D stands for Debug or error checking of the records.',    6,    '2020-10-27 11:14:18',    '2020-10-27 19:15:15',    1),
(28,    'SELECT',    9,    '2020-10-27 11:17:35',    NULL,    1),
(29,    'WHERE',    9,    '2020-10-27 11:17:50',    NULL,    1),
(30,    'SORT BY',    9,    '2020-10-27 11:20:55',    NULL,    1),
(31,    'GROUP BY',    9,    '2020-10-27 11:21:48',    NULL,    1);

DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(255) NOT NULL,
  `topic` int(11) NOT NULL DEFAULT '0',
  `answer` int(11) NOT NULL DEFAULT '0',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime DEFAULT NULL,
  `enabled` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `questions` (`id`, `question`, `topic`, `answer`, `created_date`, `modified_date`, `enabled`) VALUES
(1,    'Which of the following is not a product of Anywhere Software?',    1,    12,    '2020-08-07 18:52:02',    '2020-10-27 18:30:01',    1),
(2,    'B4A is use to build Android apps.\r\nTrue or False?',    1,    4,    '2020-08-08 06:41:22',    '2020-08-30 03:15:14',    1),
(3,    'Which of the following is not a database?',    3,    3,    '2020-08-08 06:44:40',    '2020-10-06 16:04:05',    1),
(4,    'B4i is one of the B4X suite family.\r\nWhich statement is true about B4i?',    1,    9,    '2020-08-09 07:35:17',    '2020-08-30 10:16:07',    1),
(5,    'Which of the following is not mobile operating system?',    2,    14,    '2020-08-15 07:17:30',    '2020-08-30 00:08:58',    1),
(6,    'Which of the following does not describe CRUD of database operation?',    3,    27,    '2020-10-05 19:10:54',    '2020-10-27 19:14:25',    1),
(7,    'Which of the following statements is most accurate?',    1,    21,    '2020-10-27 10:33:32',    '2020-10-27 18:34:40',    1),
(8,    'Which is not true about the following products of B4X suite?',    1,    23,    '2020-10-27 10:50:44',    '2020-10-27 19:03:35',    1),
(9,    'Which is the following not a SQL keyword?',    3,    30,    '2020-10-27 11:17:19',    '2020-10-27 19:21:10',    1);

DROP TABLE IF EXISTS `results`;
CREATE TABLE `results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL DEFAULT '0',
  `topic` int(11) NOT NULL DEFAULT '0',
  `score` varchar(255) DEFAULT '0',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `results` (`id`, `user`, `topic`, `score`, `created_date`, `modified_date`) VALUES
(1,    3,    1,    '5/5',    '2020-11-03 16:34:34',    NULL),
(2,    3,    2,    '1/1',    '2020-11-03 16:34:40',    NULL),
(3,    3,    3,    '3/3',    '2020-11-03 16:34:52',    NULL);

DROP TABLE IF EXISTS `topics`;
CREATE TABLE `topics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime DEFAULT NULL,
  `enabled` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `topics` (`id`, `topic`, `shortdesc`, `created_date`, `modified_date`, `enabled`) VALUES
(1,    'Topic 1: B4X',    'Questions related to B4X programming language and tools',    '2020-08-07 17:40:12',    '2020-09-01 00:06:26',    1),
(2,    'Topic 2: Mobile Development',    'Mobile development and applications related',    '2020-08-07 17:54:55',    '2020-10-27 19:28:28',    1),
(3,    'Topic 3: Database',    'Database or SQL commands',    '2020-08-14 20:33:01',    '2020-10-27 19:25:44',    1),
(4,    'Topic 4: Other',    'Software development and hardware related questions',    '2020-08-28 06:16:40',    '2020-10-27 19:29:36',    1),
(5,    'Topic 5',    '',    '2020-08-28 06:17:03',    '2020-10-27 19:26:37',    0);

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT 'user',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime DEFAULT NULL,
  `enabled` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `name`, `email`, `password`, `role`, `created_date`, `modified_date`, `enabled`) VALUES
(1,    'Admin',    NULL,    '21232f297a57a5a743894a0e4a801fc3',    'admin',    '2020-08-14 15:43:14',    NULL,    1),
(2,    'test',    '[email protected]',    '098f6bcd4621d373cade4e832627b4f6',    'user',    '2020-08-14 15:44:44',    '2020-10-06 03:09:12',    0),
(3,    'demo',    '[email protected]',    'fe01ce2a7fbac8fafaed7c982a04e229',    'user',    '2020-08-29 04:41:13',    '2020-09-01 12:37:04',    1);

-- 2020-11-04 08:02:47

 

[kimstudio]

I hope this can be running on a real public server and anyone from this community can submit new questions to build a large question database after a while.

 

[Mashiane]

Thanks a lot for sharing this! You are a star!

 

[aeric]

Update: PHP Frontend script attached in post #1.

The PHP app is based on old Bootstrap v3.
It is built using a simple MVC framework and PDO.