[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL search query needed for delphi form with 3 search fields

Posted on 2010-09-15
7
Medium Priority
?
750 Views
Last Modified: 2012-05-10
I have a delphi form with 3 search fields.
First Name, Last Name, City

I want to allow the user to search either 1 field (which the query is very simple) but I am having difficulties if the user enters in 2 or more criteria. Because of the OR in the statement, when user searches for 'John' in First Name and 'Oakland' in City, it returns all records where John is first name, oakland is city AND Last Name is blank.  How can I allow users to search for 1, 2 or all 3? Do I have to state every possible search combination?
0
Comment
Question by:Bianca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 80

Expert Comment

by:arnold
ID: 33685052
You need to set the parameters according to the data submitted.
i.e. user chooses field1 and field2
your query would need to be
select * from table where column1=data_extracted_from_form_field1 or column2=data_extracted_from_form_field

The query has to be built when the form is submitted based on the select fields.
It sounds as though you have the select set with all three fields whether data has been submitted or not.

unfortunately, not familiar with the delphi forms to provide a more concrete example.
0
 
LVL 2

Expert Comment

by:cetafudd
ID: 33686080
This query will do the trick by using CASE statements... When calling the stored procedure and the user did not enter the search criteria you send an empty string...

      SELECT Field1, Fields2, Fieldn
      FROM YOUR_TABLE
      WHERE FirstName =  CASE @FirstName      
                              WHEN '' THEN FirstName
                              ELSE @FirstName
                              END       
      AND LastName =  CASE @LastName
                              WHEN '' THEN LastName
                              ELSE @LastName
                              END      
      AND City =  CASE @City
                              WHEN '' THEN City
                              ELSE @City
                              END      

Please let me know if you have questions...
0
 
LVL 80

Expert Comment

by:arnold
ID: 33686331
He does not want the exclusivity of AND.
The other issue, there should not be a check for an empty strings as that will through off the results.
i.e. if I am looking for firsname="john" or city='orlando' I do not want to see entries for lastname='' i.e. someone entered firstname only.
Is it possible to dynamically create queries in delphi?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 33688148
Try

procedure TForm1.Button1Click(Sender: TObject);
var
  S: String;
  function AddToQueryStr(const ABaseStr, AFieldName, AValue: string): string;
  begin
    if ABaseStr = EmptyStr then
      Result := Format(('%s = %s'), [AFieldName, QuotedStr(AValue)])
    else
      Result := Format('%s OR (%s = %s)', [ABaseStr, AFieldName, QuotedStr(AValue)]);
  end;
begin
  S := '';
  if edtFirstName.Text <> EmptyStr then
     S := AddToQueryStr(S, 'FirstName', edtFirstName.Text);
  if edtLastName.Text <> EmptyStr then
     S := AddToQueryStr(S, 'LastName', edtLastName.Text);
  if edtCity.Text <> EmptyStr then
     S := AddToQueryStr(S, 'City', edtCity.Text);

  Query.Close;
  Query.SQL.Clear;
  Query.SQL.Add('SELECT * FROM ' + TableName);
  if S <> EmptyStr then
    Query.SQL.Add('WHERE ' + S);
  Query.Open;
end;

//where FirstName, LastName and City are your field names
0
 
LVL 24

Expert Comment

by:jimyX
ID: 33692800
Why not using the wildcard ?
var
  ParFName, ParSName, ParCity : string;
begin
  if EdFName.Text <> '' then
    begin
      ParFName := EdFName.Text;
    end
  else
    begin
      ParFName := '%';
    end;
  if EdSName.Text <> '' then
    begin
      ParSName := EdSName.Text;
    end
  else
    begin
      ParSName := '%';
    end;
  if EdCity.Text <> '' then
    begin
      ParCity := EdCity.Text;
    end
  else
    begin
      ParCity := '%';
    end;

  // the SQL code will be:
  QueryDB.SQL.add('Select * from Table1 where FirstName like '+ QuotedStr(ParFName) +' and LastName like '+ QuotedStr(ParSName) +' and City like '+ QuotedStr(ParCity));

Open in new window

0
 
LVL 5

Expert Comment

by:briangochnauer
ID: 33693312
I assume your using like a TADOQuery to get the data so it would be like;
(Changing the field names and table to be valid)

Set ADOQuery1 SQLStrings property to;
  Select * from <table_name>
    where FirstNameField  like :FirstName
       and LastNameField  like :LastName
       and CityField like :City
           

This creates parameters :First Name, :Last Name and :City in the ADOQuery object
Then you just assign the edit fields to the parameters with code something like this;
procedure Button1(Sender:TObject);
begin
    ADOQuery.Close;
  ADOQuery.Parameters.FindParam('FirstName').Value := editFirstname.text+'%';
    ADOQuery.Parameters.FindParam('LastName').Value := editLastname.text+'%';
    ADOQuery.Parameters.FindParam('City').Value := editCity.text+'%';
   ADOQuery.Open;

end.
This is the best I could come up with given the limited information you've given.
0
 
LVL 8

Expert Comment

by:GeneralTackett
ID: 33720972
Are you using SQL with parameters or building the query in code.  if using parameters then the examples with like an d% are good options

if building in code just check for "" and then skip that section as per the example above  
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question