select query with like

[ag.sitesh]

i have problum in finding data from mysql database & show data in listwidget with query given below

QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%:title%' ");


i want to find data that containing specific character that mainsioned in LIKE within query


problum comes when i add items within listWidget



code is
void VistaMedia::content()
{

QString LEdit1=searchLineEdit->text();

QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%:title%' ");
query2.bindValue(":title",LEdit1);
query2.exec();
while(query2.next());
{

listWidget->addItem(query2.value(0).toString());
QMessageBox::information(this,"Error","File not found");

}
}


thanks in advance

[jacek]

It should be:

QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%' || :title || '%' ");

QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%' || :title || '%' ");

To copy to clipboard, switch view to plain text mode 

or

query2.bindValue(":title" "%" + LEdit1 + "%");

query2.bindValue(":title" "%" + LEdit1 + "%");

To copy to clipboard, switch view to plain text mode 

[sinha.ashish]

its not working ........
plzzzzzzzzzzz check again for any problem..if there??

[ag.sitesh]

thanks for reply

check this again

this not working properly

[jacek]

this not working properly

Indeed, there's another mistake. It should be:

QSqlQuery query2;
query2.prepare(...); 
query2.bindValue( ... );
...

QSqlQuery query2;
query2.prepare(...); 
query2.bindValue( ... );
...

To copy to clipboard, switch view to plain text mode 

QSqlQuery::QSqlQuery( const QString & ) constructor executes the query, so you can't use it with placeholders.

[mazurekwrc]

I think that problem is in SELECT

QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%' || :title || '%' ");

QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%' || :title || '%' ");

To copy to clipboard, switch view to plain text mode 

it should be comma before '*'

[jacek]

it should be comma before '*'

Yes, that's the third problem.

[Lykurg]

query2.bindValue(":title" "%" + LEdit1 + "%");

query2.bindValue(":title" "%" + LEdit1 + "%");

To copy to clipboard, switch view to plain text mode 

... and there a comma is missing ;-)

query2.bindValue(":title", "%" + LEdit1 + "%");

query2.bindValue(":title", "%" + LEdit1 + "%");

To copy to clipboard, switch view to plain text mode 

And by the way, I guess that the || is only understood by oracle. If using MySql use the CONCAT function.

Lykurg

[sinha.ashish]

its not working guys........
its shows error unable to fetch data...........
can u write and test any small code on any of ur table in mysql and then post it here plzzz.....
becoz its a basic search only ...!!!
which must be done...

[mazurekwrc]

you put ';' after while

while(query2.next());
{
listWidget->addItem(query2.value(0).toString());
QMessageBox::information(this,"Error","File not found");

}

mazurek

[sinha.ashish]

tahts ok but....better u plzz give correct tested code block....will u???

[sinha.ashish]

thats a mistake ok....but better u plzz give a tested code by urself using mysql.. if possible !!
well ...i m also hanging in this query garden ...
i havn't got my answer...
god!!!

[mazurekwrc]

this code working hith PostgreSql

QString first_name = "arc";
query.prepare( "SELECT name FROM people WHERE name LIKE :name" );
query.bindValue( ":name", "%" + first_name + "%" );
query.exec();
 
while( query.next() )
{
   ....
}

QString first_name = "arc";
query.prepare( "SELECT name FROM people WHERE name LIKE :name" );
query.bindValue( ":name", "%" + first_name + "%" );
query.exec();

while( query.next() )
{
   ....
}

To copy to clipboard, switch view to plain text mode 

i hope that will help you

[sinha.ashish]

Thanks a lot!!!!!!!!!!!!!!!
thats the way!!!!!!!
now everything fine....

[sinha.ashish]

On a single text search its fine but if i have 3 lineEdits(as search criteria) for 3 columns and user can enter search text in any or every or some of those lineEdits then how to implement it???

I have written this code : but it needs that all the lineEdits must be filled ....leaving blank any lineEdit ,then query fetches every row of the table and i want to search for any text enetered in any lineEdits ....

void MultipleSearch::search()
{


}

[sinha.ashish]

On a single text search its fine but if i have 3 lineEdits(as search criteria) for 3 columns and user can enter search text in any or every or some of those lineEdits then how to implement it???

I have written this code : but it needs that all the lineEdits must be filled ....leaving blank any lineEdit ,then query fetches every row of the table and i want to search for any text enetered in any lineEdits ....

void MultipleSearch::search()
{
QString titletxt=titlesrchTxt->text();
QString composerTxt=composersrchTxt->text();
QString releaseDateTxt=rdatesrchTxt->text();
QSqlQuery query;
QString querytxt="SELECT title FROM mediadetails WHERE title LIKE :title OR composer LIKE :composer OR releasedate LIKE :rdate";
query.prepare(querytxt);
query.bindValue( ":title", "%" + titletxt + "%" );
query.bindValue( ":rdate", "%" + releaseDateTxt + "%" );
query.bindValue( ":composer", "%" + composerTxt+ "%" );
query.exec();
while(query.next())
{
listView->addItem(query.value(0).toString());
}
}

[mazurekwrc]

First of all change this line

QString querytxt="SELECT title FROM mediadetails WHERE title LIKE :title OR composer LIKE :composer OR releasedate LIKE :rdate";

to

QString querytxt="SELECT title, composer, releasedate FROM mediadetails WHERE title LIKE :title OR composer LIKE :composer OR releasedate LIKE :rdate";

QString querytxt="SELECT title, composer, releasedate FROM mediadetails WHERE title LIKE :title OR composer LIKE :composer OR releasedate LIKE :rdate";

To copy to clipboard, switch view to plain text mode 

and try

[sinha.ashish]

sorry ...tahts not the syntex which i was trying to post ...u said right...i just missed to ommit ';' ...but do u have idea to do multiple search????
..

[mazurekwrc]

you missed 'composer' and 'releasedate' not ';'
here is my code which work with multiple search

QString first_name = "arc";
QString surname = "LA";
query.prepare( "SELECT name, surname FROM people WHERE ( ( name LIKE :name ) OR ( surname LIKE :surname ) )" );
query.bindValue( ":name", "%" + first_name + "%" );
query.bindValue( ":surname", "%" + surname + "%" );
query.exec();

QString first_name = "arc";
QString surname = "LA";
query.prepare( "SELECT name, surname FROM people WHERE ( ( name LIKE :name ) OR ( surname LIKE :surname ) )" );
query.bindValue( ":name", "%" + first_name + "%" );
query.bindValue( ":surname", "%" + surname + "%" );
query.exec();

To copy to clipboard, switch view to plain text mode 

[sinha.ashish]

thanks....its working fine...