ello there,
I am currently writing a small database for Northern Territory University
library in PHP and MySQL.
A search web page where users can search by title, author and keywords for a
particular article.
I have designed and normalized the following tables in MySQL:
Materials table contains full details:
MaterialID, title, author
(MaterialID is the primary key)
Data example:
1, Australia Today, John Smith
2, Australia and Timor news, Tom King
3, East Timor financial, Eve Li
4, Money, no one
5, East Timor, someone
6, Australia financial, someone
7, Australia Time, someone
8, Timor financial, someone
MaterialKeywords table contains the materials' keywords
(one to many relationship)
materialID, keywordID [both are foreign keys from Materials and Keywords table]
(both fields are unique and as a primary key)
Data example:
1, 1
1, 2
1, 3
1, 5
1, 9
1, 4
2, 1
2, 3
2, 4
3, 3
3, 6
3, 10
Keywords table contains words:
keyword_id, word
(both fields are unique and as a primary key)
1, timor
2, indonesia
3, culture
4, news
5, history
6, religion
7, social change
8, social order
9, foreign relations
10, financial
The problem I have is comming up with an SQL statment
(my sql is not to good and I have try inner join)
that will pull out all the details from the Materials table that contain the
"ALL" the keywords entered
eg. user enters Timor, News and it returns
1, Australia and Timor News, Tom King
2, East Timor Financial, Eve Li
But user enters Timor, money and it would return nothing
I would prefer to be able to do this all in SQL rather than at the
application level but would be grateful for any examples or help.
I hope someone can help me with this as the library is on my back now to finish
this and I am at a dead end.
Any pointers, code or links would be most appreciated.
Annalise
I am currently writing a small database for Northern Territory University
library in PHP and MySQL.
A search web page where users can search by title, author and keywords for a
particular article.
I have designed and normalized the following tables in MySQL:
Materials table contains full details:
MaterialID, title, author
(MaterialID is the primary key)
Data example:
1, Australia Today, John Smith
2, Australia and Timor news, Tom King
3, East Timor financial, Eve Li
4, Money, no one
5, East Timor, someone
6, Australia financial, someone
7, Australia Time, someone
8, Timor financial, someone
MaterialKeywords table contains the materials' keywords
(one to many relationship)
materialID, keywordID [both are foreign keys from Materials and Keywords table]
(both fields are unique and as a primary key)
Data example:
1, 1
1, 2
1, 3
1, 5
1, 9
1, 4
2, 1
2, 3
2, 4
3, 3
3, 6
3, 10
Keywords table contains words:
keyword_id, word
(both fields are unique and as a primary key)
1, timor
2, indonesia
3, culture
4, news
5, history
6, religion
7, social change
8, social order
9, foreign relations
10, financial
The problem I have is comming up with an SQL statment
(my sql is not to good and I have try inner join)
that will pull out all the details from the Materials table that contain the
"ALL" the keywords entered
eg. user enters Timor, News and it returns
1, Australia and Timor News, Tom King
2, East Timor Financial, Eve Li
But user enters Timor, money and it would return nothing
I would prefer to be able to do this all in SQL rather than at the
application level but would be grateful for any examples or help.
I hope someone can help me with this as the library is on my back now to finish
this and I am at a dead end.
Any pointers, code or links would be most appreciated.
Annalise