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...:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.