How to do it…

Follow these steps to display information from a database on a model view widget:

1. We will be using the database table called employee, which we used in the previous example, Creating a login screen with Qt. This time, we need a lot more data in the employee table. Open up your web browser and log into your phpMyAdmin control panel.

Add data for a few more employees so that we can display it later in our program:

2. Open Qt Creator, create a new Qt Widgets Application project, and then add the SQL module to your project.
3. Open mainwindow.ui and add a table widget (not a table view) from Item Widget (Item-Based) under the Widget box pane. Select the main window on the canvas and click on either the Lay Out Vertically or Lay Out Horizontally button to make the table widget stick to the size of the main window, even when it's resized:

4. Double-click on the table widget and a window will then appear. Under the Columns tab, add five items by clicking on the + button in the top-left corner. Name the items ID, Name, Age, Gender, and Married. Click OK when you're done:

5. Right-click on the table widget and select Go to slot… in the pop-up menu. Scroll all the way down, select the itemChanged(QTableWidgetItem*) option in the pop-up window, and press OK. A slot function will be created in both your source files.
6. Open mainwindow.h and add these private variables to the MainWindow class:

private:
    Ui::MainWindow *ui;
    bool hasInit;
    QSqlDatabase db;

7. Add the following class headers to mainwindow.h:

#include <QtSql>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QMessageBox>
#include <QDebug>
#include <QTableWidgetItem>

8. Open mainwindow.cpp – we're going to write tons of code there. We need to declare what will happen when the program is started. Add the following code to the constructor of the MainWindow class:

MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow) {
    hasInit = false;
    ui->setupUi(this);
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setUserName("yourusername");
    db.setPassword("yourpassword");
    db.setDatabaseName("databasename");
    ui->tableWidget->setColumnHidden(0, true);

9. The SQL query code looks like this:

    if (db.open()) {
        QSqlQuery query;
        if (query.exec("SELECT id, name, age, gender, married FROM employee")) {
            while (query.next()) {
                qDebug() << query.value(0) << query.value(1) << query.value(2) << query.value(3) << query.value(4);
                QString id = query.value(0).toString();
                QString name = query.value(1).toString();
                QString age = query.value(2).toString();
                int gender = query.value(3).toInt();
                bool married = query.value(4).toBool();

10. Create several QTableWidgetItem objects:

                ui->tableWidget->setRowCount(ui->tableWidget->rowCount() + 1);
                QTableWidgetItem* idItem = new QTableWidgetItem(id);
                QTableWidgetItem* nameItem = new QTableWidgetItem(name);
                QTableWidgetItem* ageItem = new QTableWidgetItem(age);
                QTableWidgetItem* genderItem = new QTableWidgetItem();
                if (gender == 0)
                    genderItem->setData(0, "Male");
                else
                    genderItem->setData(0, "Female");
                QTableWidgetItem* marriedItem = new QTableWidgetItem();
                if (married)
                    marriedItem->setData(0, "Yes");
                else
                    marriedItem->setData(0, "No");

11. Move those objects to the table widget:

                ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 0, idItem);
                ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 1, nameItem);
                ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 2, ageItem);
                ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 3, genderItem);
                ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 4, marriedItem);
            }
            hasInit = true;
        } else {
            qDebug() << query.lastError().text();
        }
    } else {
        qDebug() << "Failed to connect to database.";
    }
}

12. Declare what will happen when an item of the table widget has been edited. Add the following code to the on_tableWidget_itemChanged() slot function:

void MainWindow::on_tableWidget_itemChanged(QTableWidgetItem *item) {
    if (hasInit) {
        QString id = ui->tableWidget->item(item->row(), 0)->data(0).toString();
        QString name = ui->tableWidget->item(item->row(), 1)->data(0).toString();
        QString age = QString::number(ui->tableWidget->item(item->row(), 2)->data(0).toInt());
        ui->tableWidget->item(item->row(), 2)->setData(0, age);
        QString gender;
        if (ui->tableWidget->item(item->row(), 3)->data(0).toString() == "Male") {
            gender = "0";
        } else {
            ui->tableWidget->item(item->row(), 3)->setData(0, "Female");
            gender = "1";
        }
        QString married;
        if (ui->tableWidget->item(item->row(), 4)->data(0).toString() == "No") {
            married = "0";
        } else {
            ui->tableWidget->item(item->row(), 4)->setData(0, "Yes");
            married = "1";
        }
        qDebug() << id << name << age << gender << married;
        QSqlQuery query;
        if (query.exec("UPDATE employee SET name = '" + name + "', age = '" + age + "', gender = '" + gender + "', married = '" + married + "' WHERE id = " + id)) {
            QMessageBox::information(this, "Update Success", "Data updated to database.");
        } else {
            qDebug() << query.lastError().text();
        }
     }
}

13. Close the database at the class destructor:

MainWindow::~MainWindow() {
    db.close();
    delete ui;
}

14. Compile and run the example now and you should get something like this: