Article:
  Building a Simple Search Engine with PHP
Subject:   A more detailed query...
Date:   2003-12-23 06:52:17
From:   anonymous2
Hi Daniel,


Thanks again for your fantastic tutorial. I was wondering how one might submit a query which exclueds certain words and includes others. I know that the following should work for finding several words:


$result = mysql_query(" SELECT p.page_url AS url,
COUNT(*) AS occurrences
FROM page p, word w1, occurrence o1, word w2, occurrence o2
WHERE p.page_id = o1.page_id AND
w1.word_id = o1.word_id AND
w1.word_word = \"$keyword[1]\" AND
w2.word_id = o2.word_id AND
w2.word_word = \"$keyword[2]\" AND
GROUP BY p.page_id
ORDER BY occurrences DESC
LIMIT $results" );


But how can I also tell it to NOT provide me with an article that contains a word I don't want as well?


- Giff

Full Threads Oldest First

Showing messages 1 through 6 of 6.

  • A more detailed query...
    2003-12-23 22:43:15  anonymous2 [View]

    Hi Giff

    Thank you for that. However, there is still a minor defect. This is what came up when i pasted your code in. And keep in mind that i have not altered anything stated in the article.

    "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/jeo/dev/search/search.php on line 48
    query executed in 0.000 seconds."


    So does anyone have a suggestion?
    Please help!

    Thank you all for helping me so far.

    Best wishes for the festive season!

    Regards,
    Sean
    • A more detailed query...
      2007-03-05 03:51:57  katie_P [View]

      hi can i ask if u managed to solve this problem, i'm having the exact same problem as you
    • A more detailed query...
      2007-03-05 03:49:10  katie_P [View]

      hi can i ask if u managed to solve this problem, i'm having the exact same problem as you
  • RE: A more detailed query...
    2003-12-23 07:15:55  dsolin [View]

    Hi Giff,

    I'm not sure if I got you right here, but couldn't you just use the != or NOT LIKE operators? This would result in something like:

    w2.word_word != \"$keyword[1]\"

    or:

    w2.word_word NOT LIKE \"$keyword[1]\"

    Hope that helps!

    -Daniel
    • RE: A more detailed query...
      2003-12-23 07:44:06  anonymous2 [View]

      I tried that earlier, but it doesn't seem to work - the reason being (I think) that there ARE occurances of that word that are defined as not being in that article (aka, all the occurances in other articles).
      • RE: A more detailed query...
        2003-12-23 08:12:37  anonymous2 [View]

        Let me try to explain a little more clearly.

        Say I have an article with the word "science" and the word "computer" in it, and the search asked for all articles with "science" but without "computer."

        If I use this query:

        w2.word_word != "computer"

        than it will look for an occurance o2 that dosn't point to "computer". However, this constraint would be satisfied instantly by any other word existing in the article - even the word "science" would satisfy it. As long as there is one instance of a word other than computer in that article, the search will bring it up.

        If I put the "!= " further up with the article definition, we still get a similar problem:

        p.page_id != o1.page_id

        This occurrence o1 of "computer" may indeed not be in the page, simply because it is an occurrence of "computer" on another page. As long as there is any other page with "computer" on it, the query will bring up the page that the search dosn't work.

        What I really want is not:

        "Find a page where there EXISTS a word "computer" not on that page"

        but:

        "Find a page that, for ALL occurrences of the word "computer", NONE of them are on it"