#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Is FULTEXT search the best for my database query


    Hello everyone

    i am building a travellers database. It contains a list of each coutry that a traveller has been to.


    The column for 'visited countries' will just contained the list of each country that the member has visited.

    My question is ; what is the best way to search that column for 'visited countries' . E.g if a member wants to search for all members who have visited the USA.

    i know that FULLTEXT searches will allow me to interate though the column to finding matching country names.

    However, i also understand that FULLTEXT has limits. for example, if the keyword search appears more than 50% then no result is returned .

    it's possible taht certain countries will indeed appear more than 50 % of the time; so how do i get aroud this.

    Is there an alternative search method or better way to search this column for matching countries.

    i am also concerned about speed. my country list wil ultimately contained millions of countries visited; will the FULLTEXT search be fast enough to return the results


    Thank you for your kind attention.

    warm regards

    Andreea
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    the easiest way is ~not~ to have a column containing the list of each country that the member has visited

    create a one-to-many table related to the members table, in which the primary key is a composite key consisting of member_id and country

    by the way, are you sure you're using microsoft sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Originally Posted by r937
    the easiest way is ~not~ to have a column containing the list of each country that the member has visited

    create a one-to-many table related to the members table, in which the primary key is a composite key consisting of member_id and country

    by the way, are you sure you're using microsoft sql server?

    Hello R937

    thank you so much for your reply.

    I am using MQSQL

    i am not 100 per cent sure i understood what you meant. could you please explain further about

    Originally Posted by r937
    the primary key is a composite key consisting of member_id and country
    Do you mean that i would create a new Id number for the menmber in the new table. If so, how would this help to speed up the process.

    sorry i am a little bit confused with how to do it.

    warm regards

    Andreea
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by andreea115
    I am using MQSQL
    could you please spell that out in words, i've never encountered that particular acronym

    Originally Posted by andreea115
    i am not 100 per cent sure i understood what you meant.
    you have a members table, right? with a column for visited countries?

    perhaps if you could show the CREATE TABLE statement for that table, i'll show you how to normalize the countries column

    for further background, do a search for first normal form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Originally Posted by r937
    could you please spell that out in words, i've never encountered that particular acronym

    you have a members table, right? with a column for visited countries?

    perhaps if you could show the CREATE TABLE statement for that table, i'll show you how to normalize the countries column

    for further background, do a search for first normal form
    Hello again R937

    i am sorry for spelling error. i meant that i am using MySQL.

    i have enclosed below the CREATE TABLE statement that i used to create the 'country list' of counties travelled to by the member. You will note that i kept this in a seperate table from the members table.

    i have also created what i beleive is 'a one-to-many table related to the members table'. Is this the correct way to do it.

    PHP Code:



    USE aupairwo_rld ;
    CREATE TABLE members(
    user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    active CHAR(32), 
    membership_type CHAR(3NOT NULL
    first_name VARCHAR(30NOT NULL,
    last_name VARCHAR(40NOT NULL,
    email VARCHAR (80NOT NULL
    pass CHAR(40NOT NULL,
    reg_date DATETIME NOT NULL,
    user_level TINYINT (1UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (user_id),
    UNIQUE KEY (email),
    INDEX login (emailpass
    ); 



    USE 
    aupairwo_rld ;
    CREATE TABLE traveller(
    user_id MEDIUMINT UNSIGNED NOT NULL ,
    country_travelled VARCHAR (400NULL,
    visaHeld VARCHAR (60NULL,
    INDEX (country_travelled ), 
    PRIMARY KEY (user_id)
    ); 
    ONE TO MANY TABLE

    Below is the one to many table i created in accordance with the earlier advice given regarding the correct way to write the code for countries visited by membes.

    Is it possible for me to know whether this is the correct way to do it.


    PHP Code:




    USE aupairwo_rld ;
    CREATE TABLE countrycode(
    country_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    country VARCHAR (400NULL,
    PRIMARY KEY (country_id)
    ); 




    USE 
    aupairwo_rld ;
    CREATE TABLE countrytravelled(
    country_id MEDIUMINT UNSIGNED NOT NULL ,
    user_id MEDIUMINT UNSIGNED NOT NULL ,
    PRIMARY KEY user_idcountry_id ),

    ); 
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by andreea115
    i am sorry for spelling error. i meant that i am using MySQL.
    that's what i suspected

    so i've moved your thread from the microsoft sql server forum to the mysql forum


    Originally Posted by andreea115
    i have also created what i beleive is 'a one-to-many table related to the members table'. Is this the correct way to do it.
    yes, that's perfect

    you can now get rid of your traveller table, but first you need to incorporate the "visaheld" column into the countrytravelled table

    what were you storing in the visaheld column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Originally Posted by r937
    that's what i suspected

    so i've moved your thread from the microsoft sql server forum to the mysql forum


    yes, that's perfect

    you can now get rid of your traveller table, but first you need to incorporate the "visaheld" column into the countrytravelled table

    what were you storing in the visaheld column?
    hello again. thank you for yoru prompt reply


    i was storing the same kind of information in the visaheld column as the country list: Its a list of the visa's held by each member. I presume that i will make the same datatable for it as the country codes.

    My final question is this;

    how will a member now search the database for a list of meembers who have visited a particular country. i am completly lost as to how the SQL should now be drafted.

    Below is the general search function to draw out the list of members. However, i need to be able to enable members to qualitfy the search by adding a specified list of countries


    PHP Code:


    // 1= United Kingdom 
    // 2= France 
    //3 = Germany 


        
    $loc          =   " '1', '2','3',";  



    function 
    traveller_search ($locNULL   )
        
         {              
                        
                 global 
    $dbc;
            
        
        
    $select "      SELECT 
                        u.first_name, u.user_id ,  i. country_id            
                        
                      
                      " 

             
                  
                      
        
    $from   "     FROM 
                     users u  LEFT OUTER JOIN countrytravelled i
                        ON u.user_id = i.user_id
                                      
                        "
    ;
                
                  
               
                   
    $where      "     WHERE 
                        u.membership_type = 'T' 
                        "

                          
         if(!empty(
    $loc) )
                {
                  
    $where .= "    AND    l.country_id IN '{$loc}'";
                }         
            
                        
        
            
             
    $order=  "ORDER BY  u.user_id DESC  ";    
            
        
    $query $select.$from.$where$order ;
            
             
             
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
              
             return 
    $result 
             
             
        }
        
      
    $TheSearchResults    =   traveller_search ($loc   ); 
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by andreea115
    i was storing the same kind of information in the visaheld column as the country list: Its a list of the visa's held by each member. I presume that i will make the same datatable for it as the country codes.
    yes

    your search function is fine, with one exception

    if the members being searched for must have one of the listed countries, then it should be an INNER JOIN, not LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo