PDA

View Full Version : select query with like



ag.sitesh
10th April 2008, 13:06
:(

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
10th April 2008, 13:09
It should be:
QSqlQuery query2("SELECT title * from mediadetails where title LIKE '%' || :title || '%' ");
or

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

sinha.ashish
10th April 2008, 13:30
its not working ........
plzzzzzzzzzzz check again for any problem..if there??

ag.sitesh
10th April 2008, 13:51
thanks for reply

check this again

this not working properly

jacek
10th April 2008, 14:46
this not working properly
Indeed, there's another mistake. It should be:

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

mazurekwrc
10th April 2008, 15:01
I think that problem is in SELECT


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


it should be comma before '*'

jacek
10th April 2008, 15:13
it should be comma before '*'
Yes, that's the third problem.

Lykurg
10th April 2008, 16:23
query2.bindValue(":title" "%" + LEdit1 + "%");

... and there a comma is missing ;-)
query2.bindValue(":title", "%" + LEdit1 + "%");

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

Lykurg

sinha.ashish
11th April 2008, 11:17
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
11th April 2008, 11:39
you put ';' after while



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

}


mazurek

sinha.ashish
11th April 2008, 11:43
tahts ok but....better u plzz give correct tested code block....will u???

sinha.ashish
11th April 2008, 11:50
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
11th April 2008, 12:19
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() )
{
....
}

i hope that will help you

sinha.ashish
11th April 2008, 12:24
Thanks a lot!!!!!!!!!!!!!!!:D
thats the way!!!!!!!
now everything fine....

sinha.ashish
11th April 2008, 13:56
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
11th April 2008, 14:02
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
11th April 2008, 14:33
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";


and try

sinha.ashish
11th April 2008, 14:44
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
11th April 2008, 15:01
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();

sinha.ashish
14th April 2008, 06:28
thanks....its working fine...:)