SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple box search tool, not all fields required

    Hi

    I have a single database with a table containing Job Number, Client, Brand, Country, Value and Rep.

    I’d like to create a search facility that allows a user to select (from drop down boxes for 4 of the 6 fields) criteria and returns any valid items.

    What I’d like to know is how I can make it so the user doesn’t have to enter data into all of the search fields and still have relevant data returned to them.

    Currently this is my query for searching the db for one term (Job Number).

    Code:
    SELECT item_table.Filename, first_table.Job_Number, first_table.Client, first_table.Brand, first_table.Country, first_table.Value, first_table.Rep  FROM first_table
                            WHERE (first_table.Job_Number = '$jobNo');") or die(mysql_error());
    So for example, if the user enters data into all six fields then only one job should be returned, but if they only filled in Country with ‘Mexico’ then all the jobs for Mexico would be returned. If they entered ‘Mexico’ in Country and ‘Dave Smith’ in Rep then all the jobs Dave has handled in Mexico would appear.

    I searched the forum before posting this, but I'm not too sure what the proper description of this would be - sorry if this has been covered before.

    If someone could help, or point me in the right direction I'd be very happy.

    CC

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    If you are working from a dumb client POV, where the boxes can be filled in willy-nilly then you are going to need a smart client-end that goes:

    Aahh, you filled in a job number, so I will search for that first.

    Ok, no job number matching, so I will go on and sort by country.

    Hang on, no country entered so I will search by Rep.

    Is that what you are on about?

    Don't you think the gui should be asking "How do you want to search?" then showing the appropriate option?

    check out isset() if you really want to query each input field to sort by.

  3. #3
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Mr. Cups has a point (I can't call you just Cups no matter what )

    The dirty idea to build the query is simply:

    Code php:
    $where="WHERE 1";
     
    if(!empty($_POST['jobno'])){
      $where.=" AND first_table.Job_Number = '".mysql_real_escape_string($_POST['jobno'])."'";
    }
     
    if(!empty($_POST['country'])){
      $where.=" AND first_table.Country = '".mysql_real_escape_string($_POST['country'])."'";
    }
     
    //...
    Saul

  4. #4
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand your point, the idea of giving different options was so they could narrow down the potential results from a search.

    They may know only that Rep x was involved in the job so search only for him, but as he's done 1500 jobs this year they've not really narrowed it down by much.

    If they know it's Rep x and he did the job for Brand y then that returns only 12 jobs and is much easier to work with.

    Ideally they will all know the job numbers to every job, they are unique and that means they'll always hit the job they need RFT - however, we all know things are never ideal!

    I'll take a look at isset() as you suggest.

    Thanks for your reply

    CC

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cups View Post
    Don't you think the gui should be asking "How do you want to search?" then showing the appropriate option?
    as a forms user, i sure don't

    5 options to choose or not choose is 32 different ways to search

    i would prefer the back end to "skip" the criteria which i do not select from dropdowns or enter a value into

    php_daemon's approach works well

    (i would start with WHERE 1=1, which is more portable)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    (i would start with WHERE 1=1, which is more portable)
    Oh! Note taken
    Saul

  7. #7
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello All

    Thanks for the replies.

    I understand the concept of Where 1 = 1, but would you mind explaining how the code you've quoted works please?

    Thanks again for your help

    CC

  8. #8
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    That's basically checking if user entered anything to the fields provided and dynamically adding a condition to where clause for each.

    PHP Code:
    if(!empty($_POST['jobno'])) 
    This checks if the user filled in a field named jobno.

    If so, we add a condition:
    PHP Code:
    $where.=" AND first_table.Job_Number = '".mysql_real_escape_string($_POST['jobno'])."'"
    Once we go through all possible fields, we have a full where clause in $where variable which you just append to your query:
    PHP Code:
    mysql_query(
    "SELECT item_table.Filename
         , first_table.Job_Number
         , first_table.Client
         , first_table.Brand
         , first_table.Country
         , first_table.Value
         , first_table.Rep  
     FROM first_table
     
    $where") or die(mysql_error()); 
    Saul

  9. #9
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for taking the time out to go through this for me.

    So $where will keep having parts added to it if the corresponding field is filled in? I always though (and my PHP knowledge is poor!) that something like this would overwrite the previous value of $where each time a new one was made?

    I will add this code to the page later today.

    Kind regards & thanks

    CC

  10. #10
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes.

    Good thinking, yes it would overwrite $where over and over again, if it wasn't for the concatenation:

    $where.='string';

    as opposed to

    $where='string';

    Note the dot.
    Saul

  11. #11
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent!

    Thanks alot - this has probably doubled my PHP knowledge in one foul swoop!

    Cheers

    CC

  12. #12
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having gotten further into this I've found that my initial description of the database was inaccurate.

    The data is split over two tables.

    'customdata' contains three rows, Field_ID | Record_ID | StringValue and essentially carries all the data I will be searching.

    'item_table' contains all the other info about the jobs excluding the search data - this is the data I'd like to have returned for a possitive search.

    Both tables are linked by Record_ID.

    Field_ID in 'customdata' is defines the catagory of data that is in StringValue; it is a 5 digit number starting 10001, eg:

    10001 = Rep
    10002 = Country
    10003 = Brand
    etc, etc, etc

    So the first row of 'customdata' is

    10001 | 278 | Dave Smith

    10001 = Field_ID
    278 = Record_ID
    StringValue = Dave Smith

    Obviously the problem I have with the script kindly made for me above is that the $where if statement was looking at a different table column for each search category, now I have the same table column for all the search terms, but it's split into the different categories by a value in the Field_ID column.

    I assume I need to JOIN the two tables on the Record_ID then have some sort of loop to go through the different search categories selecting every record where the the Field_ID = that of the search category and the StringValue = the term being searched for.

    I hope this makes sense, and I realise I'm pushing my luck asking for help again but if anyone can offer a suggestion it would really help.

    If it doesn't make sense please let me know and I'll try and explain it a different way

    Cheers

    CC

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ah, the good old Entity-Attribute-Value (EAV) design

    no wonder you're having problems searching it -- the records which satify the search will span multiple rows

    my suggestion is to redesign your tables


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot Charlie_chalk's Avatar
    Join Date
    Sep 2002
    Location
    THE North East, UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi R937

    I'd love to redesign, but these tables are being driven by another application so they are fixed and what I have to work with I'm afraid!

    FYI the application that I'm working with is Extensis Portfolio Server with SQLConnect addon. The SQL database is automaticall generated by Portfolio and then kept up to date by it.

    I'll take a look at your links though - thanks!

    CC

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Off Topic:

    Funny. I looked at the 2 table spec you just posted and thought, ah maybe a job for "views". ( as I just found out about in this recent post ) .. then I see views mentioned in your very helpful EAV link, ( yes, I have made both of these mistakes all by myself in the past, I just didnt know these meanderings had a name - could they be sql anti-patterns ?)


    @R937 Creating a database View, would it be helpful in this situation?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cups View Post
    Creating a database View, would it be helpful in this situation?
    i don't think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    I usually use a variation on what PHP_daemon suggested. Any fields that are text, get their search terms from a single search field. I then provide a dropdown that allows those search terms to be used with All of the relevant fields, or any one of them.

    For instance

    All
    First Name and Last Name
    address
    city
    email

    For any checkbox or radiobutton yes/no values, I provide a checkbox or radio button. For any values that are represented by an id linked to another table, I may provide a dropdown. In the case of jobs, I would provide a job category dropdown with the value attributes being the id key for each category. If each job row had a field for that key, linking it to a job category, then the user could search by category. The rest would be very similar to PHP_daemons example.

    PHP Code:
    $where = array();
    $fields '';
    $orderby 'jobtitle';
    if(!empty(
    $_GET['searchterms'])) // search term text field
    {
        
    $terms escape_string_function($_GET['searchterms']);
       
    $in $_GET['in']; //value from field selection dropdown
       
    $sfields '';
       switch(
    $in)
       {
            case 
    'all':
                  
    $sfields 'companyname,jobtitle,address,city,email';
                break;
            case 
    'name':
                 
    $sfields 'companyname';
                 break;
            case 
    'jobtitle':
                
    $sfields 'jobtitle';
                break;
            case 
    'address':
                
    $sfields 'address';
                break;
            case 
    'city':
                
    $sfields 'city';
                break;
            case 
    'email':
                
    $sfields 'email';
                break;
                    default:
                           
    $sfields 'companyname,jobtitle,address,city,email';
                           break;
       }
       if(!empty(
    $sfields))
       {
               
    $where[] = "match(".$sfields.") against('".$terms."')";
            
    $fields .= "match(".$sfields.") against('".$terms."') as relevance,";
            
    $orderby 'relevance';
       }
    }
    if(!empty(
    $_GET['jobcategory'])) // job category dropdown
    {
        
    $catid = (int) $_GET['jobcategory'];
        
    $where[] = 'catid='.$catid;
    }
    if(!empty(
    $_GET['willtrain'])) // will train? checkbox
    {
        
    $where[] = 'willtrain=1';
    }
    $wrstr implode(' AND ',$where);
    $fields .= 'jobtitle,company,address,city,state,zip,email';
    $sql "SELECT $fields FROM jobs WHERE $wrstr ORDER BY $orderby"
    In this example I am using fulltext search. If you use that, you will have to have your indexes set up to use that technique. You can do as many logical groupings of fields in the field select dropdown as make sense.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •