www.webdeveloper.com
Results 1 to 14 of 14

Thread: [RESOLVED] full text search multiple tables?

  1. #1
    Join Date
    Aug 2005
    Posts
    76

    resolved [RESOLVED] full text search multiple tables?

    I am trying to put a full text search together. I can search one table just fine, but I would like to be able to search multiple tables. Everytime I try to search multiple tables I end up with a ambiguos error. Heres my current sytax for searching one table.
    Code:
    $sql = mysql_query("SELECT *, MATCH(title, message) AGAINST('$keywords') AS score FROM news WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC") or die(mysql_error());
    Any ideas how I could expand this to search more than one table? Thanks for your time.

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    are the tables related at all?
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Aug 2005
    Posts
    76
    No the tables are not related. Basically there are 3 tables I want to search. News, Downloads, and Tutorials. However all the tables do have a "title" field and a "message" field. Any ideas?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    i'm not 100% sure what you're trying to do.
    you have select * in your query. does all information get displayed in some way? it seems like these three objects are completely different, and contain different things. how can you expect to merge 3 things that are very different?

    my opinion would be to search separately, not in a single query, unless you can find some way of making them inherit a common item that makes them searchable.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  5. #5
    Join Date
    Aug 2005
    Posts
    76
    I am using a while statement in php to display the info
    PHP Code:
    while($result mysql_fetch_array($sql)) {
        
    $title $result['title'];
        
    $message $result['message'];
        
    //display the info

    Even thou the info is in different tables it is basically the same. A title of the news, download, or tutorial and a description of the news, download, or tutorial.

    I thought I could do something like this
    Code:
    $sql = mysql_query("SELECT news.title, news.message, downloads.title, downloads.message, MATCH(title, message) AGAINST('$keywords') AS score FROM news, downloads WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC") or die(mysql_error());
    but that just gives me the error "Column: 'title' in field list is ambiguous". I plan on offering the user to search tables indivisualy but would like it possible to search the entire group of tables also.

  6. #6
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    don't do it like that. if the tables are not related, joining them will cause headaches.

    you should use the UNION clause between multiple statements to link them.

    Code:
    SELECT title,message, MATCH(title, message) AGAINST('$keywords') AS score FROM news WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC
    UNION
    SELECT title,message, MATCH(title, message) AGAINST('$keywords') AS score FROM downloads WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC
    ...
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  7. #7
    Join Date
    Aug 2005
    Posts
    76
    Ahh thanks Chazzy thats what I was looking for. It works great now. I have a new problem thou. I am attempting to count the rows that match my search for pagination. I have it working fine when using just one table, but the UNION clause doesn't seem to be working when I use the COUNT clause. It does not return an error but it seems to be returning the count of only the first table.
    Code:
    $countresult = mysql_query("SELECT COUNT(*) as totalnum from news WHERE MATCH(title, message) AGAINST('$keywords') UNION SELECT COUNT(*) as totalnum from downloads WHERE MATCH(title, message) AGAINST('$keywords')") or die(mysql_error());
    Been searching for an answer but haven't found anything on UNION and COUNT together.

  8. #8
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you shouldn't call this union directly.

    SELECT COUNT(*) FROM
    (SELECT.... the union goes here ) a;

    since it looks like you're using PHP, i'd recommend doing something like

    PHP Code:
    $sql "
    SELECT * FROM
    (SELECT title,message, MATCH(title, message) AGAINST('
    $keywords') AS score FROM news WHERE MATCH(title, message) AGAINST('$keywords')
    UNION
    SELECT title,message, MATCH(title, message) AGAINST('
    $keywords') AS score FROM downloads WHERE MATCH(title, message) AGAINST('$keywords')
    ...) a ORDER BY SCORE DESC"
    ;
    $result mysql_query($sql) or die("Unable to issue query: ".mysql_error());
    $count mysql_num_rows($result);
    ... 
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  9. #9
    Join Date
    Aug 2005
    Posts
    76
    Thanks Chazzy I read something similair to this but it didn't make sense. Makes sense now. Thanks again.

  10. #10
    Join Date
    Nov 2006
    Posts
    2

    Question

    Hi.

    Sorry to bring back an old topic.

    I'm trying to do the exact same thing here, but am experiencing errors.

    The code i'm using is this:

    PHP Code:
    // Formulate Query
        // This is the best way to perform a SQL query
        // For more examples, see mysql_real_escape_string()
        
    $query = ("SELECT * FROM 
        (SELECT title,description, MATCH(title, description) AGAINST('
    $product_search') AS score FROM tshirts WHERE MATCH(title, description) AGAINST('$product_search') 
        UNION 
        SELECT title,description, MATCH(title, description) AGAINST('
    $product_search') AS score FROM bandanas WHERE MATCH(title, description) AGAINST('$product_search') 
        ...) a ORDER BY SCORE DESC"
    );
        
        
    // Perform Query
        
    $results mysql_query($query);
        
        
    // Check result
        // This shows the actual query sent to MySQL, and the error. Useful for debugging.
        
    if (!results) {
           
    $message  'Invalid query: ' mysql_error() . "\n";
           
    $message .= 'Whole query: ' $query;
           die(
    $message);
        }
        
        
    // Store item information
        
    $chosen_item mysql_fetch_array($results); 

    But I get the error message 'Error performing query: ', do you know where i'm going wrong?

  11. #11
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    no because the text "Error performing query: " is not in your code.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  12. #12
    Join Date
    Dec 2008
    Location
    Philadelphia
    Posts
    2

    Question additional question

    I have a similar issue which this thread helped me figure out but now I have a problem: once I get the results, how can I tell from which table the result is from? What would be the best way to determine that? Would I need to create a temporary table and add the table name as a field so I can differentiate which records are from table A or table B or is there a simpler (or faster) way I can retrieve that in PHP?

    I need that because once someone click on one of the results, I would need to load that particular record in the next screen and I don't have a field that would allow me to figure out if that is a table A record or a table B record.

    Thanks in advance.

    Fabiano

  13. #13
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you could add a column and for each table make the value of this column the table's name.

    something like

    Code:
    select 'tablea' as table, other_columns.... from tablea where....
    union
    select 'tableb' as table, other_columns... from tableb where....
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  14. #14
    Join Date
    Dec 2008
    Location
    Philadelphia
    Posts
    2
    Thanks a million Chazzy! That worked like a charm.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles