Swift 5 SQLite to TableView

Question

I just want to populate Tableview from SQLite database. your help is very appreciated. I believe the issue is easy to solve but I couldn't do that because a lack of experience in Swift development. Here are the code I have been using.

I think the missing part is:

• Create new array
• append sqlite data to array

So, the question is: How to append many columns from database to array and show them in Table-view. Thanks:)

Swift 5

import UIKit
import SQLite

class MyClass1: UIViewController,UITableViewDelegate,UITableViewDataSource,UISearchBarDelegate {

    @IBOutlet var tableview: UITableView!
    @IBOutlet weak var searchBar: UISearchBar!
    
    
    var db: OpaquePointer?
    
    var names = [
        "Sam",
        "Mark",
        "Kia",
        "John"
    ]
    var filteredData : [String]!
    
    override func viewDidLoad() {
        super.viewDidLoad()
        
        filteredData = names
        
        // connect to database
        let fileURL = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask).first!.appendingPathComponent("myDB.db", isDirectory: false)

        //opening the database
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
            print("error opening database")
        }

        
        let queryString = "SELECT * FROM people"
        
        var stmt:OpaquePointer?
        
        if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error preparing insert: \(errmsg)")
            return
        }
        
        while(sqlite3_step(stmt) == SQLITE_ROW){
            let id = sqlite3_column_int(stmt, 0)
            let name = String(cString: sqlite3_column_text(stmt, 1))
            print("id:\(id) name:\(name)")
        }
        
        self.tableview.reloadData()
        
        
    }
    
    
    func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
        return filteredData.count
    }
    
    func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
        
        let cell = tableView.dequeueReusableCell(withIdentifier: "cell", for: indexPath)
        
        cell.textLabel?.text = filteredData[indexPath.row]
        
        return cell
    }

    func tableView(_ tableView: UITableView, didSelectRowAt indexPath: IndexPath) {
        print("You clicked me! at \(indexPath.row)")
    }

    // MARK: search bar config
    
    func searchBar(_ searchBar: UISearchBar, textDidChange searchText: String) {
        print("search word entered!")
        
        filteredData = []
        
        if searchText == "" {
            filteredData = names
        } else {
        
            for name in names {
                if name.lowercased().contains(searchText.lowercased()) {
                    filteredData.append(name)
                }
            }
        }
        
        self.tableview.reloadData()
    }
    
    // called when keyboard search button pressed
    func searchBarSearchButtonClicked(_ searchBar: UISearchBar) {
        searchBar.resignFirstResponder()
    }

}

Answer 1

Create a new custom type to hold the data from the sql table

struct People {
    let id: Int
    let name: String
}

in your view controller add an array as a class property

var people: [People] = []

and then fill that array with the result of the sql query

while(sqlite3_step(stmt) == SQLITE_ROW){
    let id = sqlite3_column_int(stmt, 0)
    let name = String(cString: sqlite3_column_text(stmt, 1))
    people.append(People(id: id, name: name))
}

You will need to let your filteredData array hold People objects as well and the filtering needs to be changed.

for person in people {
    if person.name.lowercased().contains(searchText.lowercased()) {
        filteredData.append(person)
    }
}