SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    add search of news to search of games (was "Query Syntax, Need a little help")

    I have this query, used to search my database:

    PHP Code:
    $MySQL->query("SELECT `id`, `title`, `user`, `userid`, `shortdescription`, `description`, MATCH (`title`, `user`, `shortdescription`, `description`) AGAINST ('".$search_string."' IN BOOLEAN MODE) AS `score` FROM `games` WHERE MATCH (`title`, `user`, `shortdescription`, `description`) AGAINST ('".$search_string."' IN BOOLEAN MODE) AND `active` = '1' AND `flag` != 3 ORDER BY `score` DESC LIMIT ".($_page $_perpage).", ".$_perpage);




    It works really well, but I need to add searching of another table with different columns to it. The table is `news` with columns `title` and `message` that I need to include in the search.

    How would I go about adding that into the query? Thank you in advance.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how are the two tables related?

  3. #3
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Only have one column in column and that is the `title` column.

    The `games` column you can see clearly from the MySQL query. The `news` column needs to have the `title` and `message` columns searched.

    Hopefully this answers your question.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, some hints:

    1) you need to join the tables. i suggest a left outer join so that games that have no news will be included in the results. see: http://dev.mysql.com/doc/refman/5.0/en/join.html
    2) you might need to "group by", otherwise you will get multiple results if the game has multiple news items.
    3) you can't mention columns from two different tables in the same match/against pair. you will need to add another match/against pair for the news table.

    if that's not enough to get you started, let us know and someone will take a crack at it for you.

  5. #5
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the advice. One problem though, the news does not actually belong to a game. They are entirely seperate, I just need them both to be searched.

    The third number you wrote about seems to be what I need to know, but when I tried that it brought up an error.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, then you don't need a join; you need a union. here is a sample:
    Code:
    select 'game' as type
         , title
      from games
     where match(title) against('$search_string')
     union all
    select 'news' as type
         , title
      from games
     where match(title) against('$search_string')

  7. #7
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So would I add more columns to search after the titles?

    Such as:

    , title
    , message

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you can add as many columns as you want to any part of the query, what i gave you was just a frame to get started. just note that both select statements must return the same data in the same column.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck
    both select statements must return the same data in the same column.
    in other words, the columns need to be union compatible -- they need compatible datatypes (not "same data"): strings with strings, numbers with numbers, dates with dates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright I used your query and plugged it into what I had previously. It is returning an error for some reason now but I'm sure its just me screwing up the syntax. Here's how i have it now

    Code:
    $MySQL->query("SELECT `game` AS TYPE `id`, `title`, `user`, `userid`, `shortdescription`, `description` FROM `games` WHERE MATCH(`title`, `user`, `userid`, `shortdescription`, `description`) AGAINST('".$search_string."' IN BOOLEAN MODE) UNION ALL
                           SELECT `news` AS TYPE `id`, `title`, `message` FROM `games` WHERE MATCH(`title`, `message`) AGAINST ('".$search_string."' IN BOOLEAN MODE) AND `active` = '1' ORDER BY `game` DESC, `news` DESC LIMIT ".($_page * $_perpage).", ".$_perpage);
    Thank you for all the help so far.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, you have 6 columns in one SELECT, and only 3 in the other

    you need the exact same number of columns in both SELECTs

    note that NULL is union compatible with any datatype!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by proph3t
    It is returning an error for some reason now but I'm sure its just me screwing up the syntax.
    why-oh-why don't people POST THE ERROR!!!???

  13. #13
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because it didn't give me the error. It just said, MySQL Error: and outputted the syntax I used.

    Edit: found out it was commented in the HTML:

    MySQL Error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id`, `title`, `user`, `userid`, `shortdescription`, `description


    Thats with this updated syntax:
    Code:
    $MySQL->query("SELECT `game` AS TYPE `id`, `title`, `user`, `userid`, `shortdescription`, `description` FROM `games` WHERE MATCH(`title`, `user`, `userid`, `shortdescription`, `description`) AGAINST('".$search_string."' IN BOOLEAN MODE) UNION ALL
     SELECT `news` AS TYPE `id`, `title`, `message`, NULL, NULL, NULL FROM `games` WHERE MATCH(`title`, `message`) AGAINST ('".$search_string."' IN BOOLEAN MODE) AND `active` = '1' ORDER BY `game` DESC, `news` DESC LIMIT ".($_page * $_perpage).", ".$_perpage);

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT `game` AS TYPE `id`, ...
    comma missing after TYPE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •