MySQL database table row value display in QTableView like below image

[abhiabhi]

rsz_screenshot.jpg

I need to show the primary key column values like specified in the above image. Can it be achieved? As QTableView will only make rows and columns available in the DB table, I need only particular values (of primary key column) iterated with for(each) loop inside while(query.next())...kindly guide me, thank you in advance.

[Santosh Reddy]

If you want to use QTableView, then you will need either need to use QSqlTableModel to read database or implement a QAbstractTableModel, a proxymodel, and a delegate

[abhiabhi]

Thank you for ur reply , can u plz share an example for each one? and when we use a TableModel, it will be in rowXcolumn view, but I need a plain interface like I stated in my designer file. plz explain me the further approach, thanK u again

[Santosh Reddy]

Here is an example, see if you can build and run, if not I have also attached the screenshot of the output. I use Sqlite but once can use MySql also

//Helper.pro
QT += core gui sql
 
SOURCES += \
    main.cpp

//Helper.pro
QT += core gui sql

SOURCES += \
    main.cpp

To copy to clipboard, switch view to plain text mode 

//main.cpp
#include <QtGui>
#include <QDebug>
#include <QSqlQuery>
#include <QSqlTableModel>
 
class MyView : public QWidget
{
public:
    explicit MyView(QWidget * parent = 0)
        : QWidget(parent)
        , mLayout(new QGridLayout(this))
        , mGroup(0)
        , mModel(0)
        , mColumn(0)
    {
        setLayout(mLayout);
        updateView();
    }
 
    void setModel(QAbstractTableModel *model)
    { mModel = model, updateView(); }
 
    void setColumn(int column)
    { mColumn = column, updateView(); }
 
private:
    QGridLayout * mLayout;
    QGroupBox * mGroup;
    QAbstractTableModel * mModel;
    int mColumn;
 
    void updateView(void)
    {
        if(mGroup)
            delete mGroup;
 
        mGroup = new QGroupBox;
 
        if(mGroup->layout())
            delete mGroup->layout();
 
        QVBoxLayout * layout = new QVBoxLayout;
 
        if(mModel != 0)
            for(int i = 0; i < mModel->rowCount(); i++)
                layout->addWidget(new QRadioButton(mModel->index(i, mColumn).data().toString()));
 
        mGroup->setLayout(layout);
        mLayout->addWidget(mGroup);
        update();
    }
};
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
 
    // Create Sample Databse (can use MYSQL, instead of Sqlite)
    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE", "Test-sqlite");
    database.setDatabaseName("Test.sqlite");
 
    // open database
    if(database.open())
    {
        // if table does not exists
        if(database.tables().count() == 0)
        {
            // Create table
            QString q("CREATE TABLE Employee ( Name varchar(255), Salary  int );");
 
            QSqlQuery query(q, database);
            query.exec();
            database.commit();
        }
    }
 
    // Create model for the sql table
    QSqlTableModel * model = new QSqlTableModel(&app, database);
    model->setTable("Employee");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();
 
    // if not rows exist in table
    if(model->rowCount() == 0)
    {
        // Add sample rows/records
        model->insertRows(0, 10);
        for(int i = 0; i < model->rowCount(); i++)
        {
            model->setData(model->index(i, 0), QString("Employee-%1").arg(i + 1));
            model->setData(model->index(i, 1), i*555);
        }
        model->submit();
    }
 
    // Create a Table View
    QTableView v1;
    v1.setModel(model), v1.setWindowTitle("QTableView"), v1.show();
 
 
    // Create a MyView 1
    MyView v2;
    v2.setModel(model), v2.setWindowTitle("MyView 2"), v2.setColumn(0), v2.show();
 
    // Create a MyView 2
    MyView v3;
    v3.setModel(model), v3.setWindowTitle("MyView 3"), v3.setColumn(1), v3.show();
 
    return app.exec();
}

//main.cpp
#include <QtGui>
#include <QDebug>
#include <QSqlQuery>
#include <QSqlTableModel>

class MyView : public QWidget
{
public:
    explicit MyView(QWidget * parent = 0)
        : QWidget(parent)
        , mLayout(new QGridLayout(this))
        , mGroup(0)
        , mModel(0)
        , mColumn(0)
    {
        setLayout(mLayout);
        updateView();
    }

    void setModel(QAbstractTableModel *model)
    { mModel = model, updateView(); }

    void setColumn(int column)
    { mColumn = column, updateView(); }

private:
    QGridLayout * mLayout;
    QGroupBox * mGroup;
    QAbstractTableModel * mModel;
    int mColumn;

    void updateView(void)
    {
        if(mGroup)
            delete mGroup;

        mGroup = new QGroupBox;

        if(mGroup->layout())
            delete mGroup->layout();

        QVBoxLayout * layout = new QVBoxLayout;

        if(mModel != 0)
            for(int i = 0; i < mModel->rowCount(); i++)
                layout->addWidget(new QRadioButton(mModel->index(i, mColumn).data().toString()));

        mGroup->setLayout(layout);
        mLayout->addWidget(mGroup);
        update();
    }
};

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);

    // Create Sample Databse (can use MYSQL, instead of Sqlite)
    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE", "Test-sqlite");
    database.setDatabaseName("Test.sqlite");

    // open database
    if(database.open())
    {
        // if table does not exists
        if(database.tables().count() == 0)
        {
            // Create table
            QString q("CREATE TABLE Employee ( Name varchar(255), Salary  int );");

            QSqlQuery query(q, database);
            query.exec();
            database.commit();
        }
    }

    // Create model for the sql table
    QSqlTableModel * model = new QSqlTableModel(&app, database);
    model->setTable("Employee");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();

    // if not rows exist in table
    if(model->rowCount() == 0)
    {
        // Add sample rows/records
        model->insertRows(0, 10);
        for(int i = 0; i < model->rowCount(); i++)
        {
            model->setData(model->index(i, 0), QString("Employee-%1").arg(i + 1));
            model->setData(model->index(i, 1), i*555);
        }
        model->submit();
    }

    // Create a Table View
    QTableView v1;
    v1.setModel(model), v1.setWindowTitle("QTableView"), v1.show();


    // Create a MyView 1
    MyView v2;
    v2.setModel(model), v2.setWindowTitle("MyView 2"), v2.setColumn(0), v2.show();

    // Create a MyView 2
    MyView v3;
    v3.setModel(model), v3.setWindowTitle("MyView 3"), v3.setColumn(1), v3.show();

    return app.exec();
}

To copy to clipboard, switch view to plain text mode 

Example.jpg

[abhiabhi]

Yeah! Yeah! you made it, but one question, how to reduce the spacing between one radio button to another??

[Santosh Reddy]

void updateView(void)
{
    ...
    QVBoxLayout * layout = new QVBoxLayout;
    layout->setSpacing(0); // << add this
    ...
}

void updateView(void)
{
    ...
    QVBoxLayout * layout = new QVBoxLayout;
    layout->setSpacing(0); // << add this
    ...
}

To copy to clipboard, switch view to plain text mode 

[abhiabhi]

Hmmm... Just one doubt, if I want to add another Form fields like PushButton or ButtonGroup or some checkboxes, how I would add it? As I dont have the designer file..

[Santosh Reddy]

You can create a form as you want in void updateView(void).

[abhiabhi]

yes, if I need to add buttons?? plz kindly help me, i am in a bit hurry, i need to add two push buttons (OK and Cancel)

[Santosh Reddy]

yes, if I need to add buttons?? plz kindly help me, i am in a bit hurry, i need to add two push buttons (OK and Cancel)

If you want push buttons then add them, what is stopping you from doing that?
Did you try somting?

Any way here is an example (just an example)

void updateView(void)
{
    if(mGroup)
        delete mGroup;
 
    mGroup = new QGroupBox;
 
    if(mGroup->layout())
        delete mGroup->layout();
 
    QVBoxLayout * layout = new QVBoxLayout;
    layout->setSpacing(0);
 
    if(mModel != 0)
        for(int i = 0; i < mModel->rowCount(); i++)
            layout->addWidget(new QRadioButton(mModel->index(i, mColumn).data().toString()));
 
    mGroup->setLayout(layout);
    mLayout->addWidget(mGroup, 0, 0, 1, 2); // Here
    mLayout->addWidget(new QPushButton("Next >"), 1, 0, 1, 1); // Here
    mLayout->addWidget(new QPushButton("Cancel"), 1, 1, 1, 1); // Here
    update();
}

void updateView(void)
{
    if(mGroup)
        delete mGroup;

    mGroup = new QGroupBox;

    if(mGroup->layout())
        delete mGroup->layout();

    QVBoxLayout * layout = new QVBoxLayout;
    layout->setSpacing(0);

    if(mModel != 0)
        for(int i = 0; i < mModel->rowCount(); i++)
            layout->addWidget(new QRadioButton(mModel->index(i, mColumn).data().toString()));

    mGroup->setLayout(layout);
    mLayout->addWidget(mGroup, 0, 0, 1, 2); // Here
    mLayout->addWidget(new QPushButton("Next >"), 1, 0, 1, 1); // Here
    mLayout->addWidget(new QPushButton("Cancel"), 1, 1, 1, 1); // Here
    update();
}

To copy to clipboard, switch view to plain text mode