Field2 "NOT HAVING" %field1% 1

[Olavxxx]

Hi,

I have a table with content.
Field1 is a keyword-field, containing one keyword.
Field2 is a content-field, containing conent.

My Chaos CMS, has no menus at all! It simply replaces occurrances of keywords INSIDE CONTENT with hyperlinks to the correct content (which contain the keywords).
This is done thru PHP and works perfectly.

eg. Page1:
Keyword: Apple
Text: Apples and Oranges are great!

Page2:
Keyword: Orange
Text: Oranges are better than apples!

In this case, the word Apple will be hyperlinked in page2, and the word Orange will be hyperlinked in page1.

However, my problem is when adding more pages, what if no pages contains some of the keywords that some pages have?

If I where to add Page3 to this example:
Keyword: Peas
Text: Peas is not a fruit, but you can read about Oranges and Apples

In page3, oranges and apples would be hyperlinked, but there is no link from Apples or Oranges to the page about Peas.

This is where my problem comes in, How should I write a select query, for selecting the content containing the keywords that are NOT IN the content?

Something like:
SELECT * FROM tbl_content WHERE content NOT HAVING '%keywords%';

I know that query will not work, but I think you get my point.

I hope someone can help me. I've tried looking thru the manual, but at first glance, I could not see anything that helped me.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

What kind of store is present in no cities?

SELECT DISTINCT store_type FROM Stores
WHERE NOT EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);

(

http://dev.mysql.com/doc/mysql/en/exists-and-not-exists-subqueries.html)

I tried modifying that:

SELECT DISTINCT content_keywords AS unused_keywords
FROM content
  WHERE NOT EXISTS (SELECT * FROM content
                    WHERE content_text <> content_keywords)

but it only returns an error..

MySQL 4.0.23-standard


Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[r937]

4.0 means you can't use a subquery

i'm not sure i understood which column is the searching column and which one is the searchee

select c1.content_keywords as unused_keywords
  from content as c1
left outer
  join content as c2
    on c1.content_keywords 
       like concat('%',c2.content_text,'%')
 where c2.content_keywords is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Olavxxx]

Thank you for your suggestion, but your suggestion returns all rows.

content_id | content_keywords | content_text
-------------------------------------------------
1 | apple | I have oranges and apples!
2 | orange | I like apples
3 | fish | fish is no good, but apples are
4 | potatoes | cook those potatoes like you mean it

How do I find fish and potatoes in this case?
fish is not in the content_text, except it's own.
potatoes is not in any content_text.

eg. linethru means that they should be excluded, since the keyword belongs to that content's content_keyword.

Working example:

http://www.raskt.net/bba/

press the "bergensbrannen" to check how it works.
The site generates the links on the fly, like a smiley script works. It replaces keywords with keywords wrapped in hyperlinks to other contents. It ignores the keywords on pages that own the keyword.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[r937]

oops, i had the fields backwards in the LIKE

select c1.content_keywords as unused_keywords
  from content as c1
left outer
  join content as c2
    on c2.content_text
       like concat('%',c1.content_keywords ,'%')
   and c1.content_id <> c2.content_id
 where c2.content_id is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Olavxxx]

Hi,
Thank you very much!
I didnt test it yet, as I seem to have exceeded my bandwith quota! I mailed my hosting company and asked them to increase my quota ASAP.

*give stars for the help*

I'll post back later with the result

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

Hi, Your query worked perfectly
Again, I thank you

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster