Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: Pure SQL query question

    Pure SQL query question
    TABLE example

    fields: name, description, address

    record1
    -------
    name: Judo Center The Judokas
    description: Dojo in the heart of the City of Paris. Also for Kids
    address: Blossom Avenue 333, Paris

    record2
    -------
    name: The Aikis
    description: Aikido gym in brussels, Belgium. Amateur & Professional.
    address: Mainstreet 44, Brussels


    User searches for "Judo Center Paris", or "Aikido Brussels"
    (let`s call the search "searchstring")

    So far, i have come up with:
    SELECT * FROM example WHERE name LIKE '%searchstring%' OR description LIKE '%searchstring%' OR address LIKE '%searchstring%'";

    Obviously, this doesn`t return any of the records. Which SQL query should i use?

  2. #2
    Join Date
    Oct 2011
    Posts
    3
    If I understand your question correctly you are essentially wanting to store user input into a variable and use that variable to run a SQL query.
    I apologize if I miss understand, but if I follow what your wanting to do I believe it is out of the confines of using SQL independently.
    If I do understand your question correctly what you would want to do is create a java program(for example) and write the program to prompt the user. Their input could then be stored in a variable and used make a query of the database by utilizing JDBC.

    I guess I am confused about who the user is and how they are interacting with your database? Are they coding the SQL or writing some value into a GUI or command line?
    Last edited by CapC; 10-24-11 at 23:06.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Emark View Post
    So far, i have come up with:
    SELECT * FROM example WHERE name LIKE '%searchstring%' OR description LIKE '%searchstring%' OR address LIKE '%searchstring%'";
    you see where you have put wildcards around the searchstring that the user entered?

    i'm pretty sure you were planning on doing this yourself, i.e. you're not asking the user to type in %Judo Center Paris% so this tells me that you have more than a pure sql question, you have an sql question where a portion of the query itself has been manipulated, likely by some code in some language like php, to put those wildcards around whatever the user entered

    correct?

    okay, so use that same application language to split up the user's search string into separate words -- i don't do php but i believe you would use either the implode or the explode function

    anyhow, what you want to end up with is
    Code:
    WHERE name LIKE '%Judo%'
       OR name LIKE '%Center%'
       OR name LIKE '%Paris%'
       OR description LIKE '%Judo%' 
       OR description LIKE '%Center%' 
       OR description LIKE '%Paris%' 
       OR address LIKE '%Judo%'
       OR address LIKE '%Center%'
       OR address LIKE '%Paris%'
    get the idea?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2011
    Posts
    4
    @CapC: the user searches for a martial arts school, in a specific city, for a specific style of martial arts. This search is done in the search box you can find on DutchMartialArts.com - winkels & vechtsportclubs right side above the yellow button.
    The search box says: "Zoek in deze website.." which means: "Search in this website".

    As for sample data you could take a look at Hapkido, taekwondo - vechtsportclubs
    "Vechtsportclubs" means: "martial arts organizations"
    "Hapkidi, taekwondo" are specific styles of martial arts.

    All records consist of 3 fields: name, description, address
    The address fiels contains the name of the City (along with other data like street,numer, postal code etc.)

    Somebody told me i should think about SELECT MATCH AGAINST together with the UNION clause, since we'll have to split up the search term.

    Can you work with this info? If any questions, please do not hesitate to ask. Big thanks in advance!

  5. #5
    Join Date
    Oct 2011
    Posts
    4
    @R937:

    you have an sql question where a portion of the query itself has been manipulated
    Yes, you`ll have to split up the search term into separate words.

    The solution you`re going at
    WHERE name LIKE '%Judo%'
    OR name LIKE '%Center%'
    OR name LIKE '%Paris%'
    OR description LIKE '%Judo%'
    OR description LIKE '%Center%'
    OR description LIKE '%Paris%'
    OR address LIKE '%Judo%'
    OR address LIKE '%Center%'
    OR address LIKE '%Paris%'
    will return ALL schools in Paris, ALL judoschools, ALL centers, ... which is not what the user is looking for..

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd design your data so it meets the requirements of your users.
    ferinstance if your users want to search by country and or city then those should be separate lines of the address and separate columns in the db.

    likewise if the type of martial arts is important consider defining two tables, one that defines types of martial arts, and one that associates types of martial arts with specific premises.
    bear in mind its quite possible that a single martial arts school may have more than one set or premises, it may not offer all types of martial arts in all premises
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2011
    Posts
    3
    Quote Originally Posted by healdem View Post
    ferinstance if your users want to search by country and or city then those should be separate lines of the address and separate columns in the db.
    I agree completely with this point. If you broke up your address column into city,zip,etc. The issue at hand would be much easier. My personal knowledge of front end is limited but it seems as r937 stated you would have to have some sort of code or language.

    For example using java - if your data is separated by a space
    String line = "Blossom Avenue Paris" ;
    String[] parts = line.split(" ");

    Which you could then store into a variable and concatenate into your SQL query. I have no knowledge of php syntax and it seems that is what you are using. I visited the site you referenced and viewed some of the code there. The search box in reference is:

    <div id="navbarright">
    <form id="searchform" method="get" action="/index.php">
    <input type="text" value="Zoek in deze website..." name="s" id="searchbox" onfocus="if (this.value == 'Zoek in deze website...') {this.value = '';}" onblur="if (this.value == '') {this.value = 'Zoek in deze website...';}" />
    <input type="submit" id="searchbutton" value="GO" /></form>
    </div>


    So it appears you are using php with a get / set method for the search most likely. So you can split the search data and concatenate the string together for the query or you could use a different searchform system that allowed for separate tabs for address,city etc.

    Hope this helps some.
    Last edited by CapC; 10-25-11 at 10:55.

  8. #8
    Join Date
    Oct 2011
    Posts
    4
    personally I'd design your data so it meets the requirements of your users.

    Correct. Unfortunately, that cannot be done. Database structure will remain the same.

    This is what i got so far from a friend. But he is not able to put it into a complete sql query that suits the needs of the user:

    Assuming you have indexed the 3 fields for fulltext searches, You would split the search term up and do something similar to the below for each term using a UNION and then ordering all final results by relevance.

    SELECT *,
    MATCH(`name`,`description`,`address`)
    AGAINST ('+searchrequest' IN BOOLEAN MODE) AS relevance
    FROM `mytable`
    WHERE MATCH(`name`,`description`,`address`)
    AGAINST ('+searchrequest' IN BOOLEAN MODE);


    anyone knows how to combine these queries (for each individual split up search term) with UNION clause?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Emark View Post
    anyone knows how to combine these queries (for each individual split up search term) with UNION clause?
    i'd try this before i resort to UNION --
    Code:
    SELECT *
         , MATCH(name,description,address) 
             AGAINST ('+searchword1' IN BOOLEAN MODE) +
           MATCH(name,description,address) 
             AGAINST ('+searchword2' IN BOOLEAN MODE) +
           MATCH(name,description,address) 
             AGAINST ('+searchword3' IN BOOLEAN MODE ) AS relevance 
      FROM mytable 
     WHERE MATCH(name,description,address) 
             AGAINST ('+searchword1' IN BOOLEAN MODE)
        OR MATCH(name,description,address) 
             AGAINST ('+searchword2' IN BOOLEAN MODE)
        OR MATCH(name,description,address) 
             AGAINST ('+searchword3' IN BOOLEAN MODE)
    ORDER
        BY relevance DESC
    this assumes your code has split the search term into three words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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