• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

SQL search query needed for delphi form with 3 search fields

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
Bianca
Asked:
Bianca
1 Solution
 
arnoldCommented:
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
 
cetafuddCommented:
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
 
arnoldCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Ephraim WangoyaCommented:
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
 
jimyXCommented:
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
 
briangochnauerCommented:
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
 
GeneralTackettCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now