Hi Guys, here goes......
SELECT count(page_searchwords.word_id) as score, page_data.id, filename,
title FROM page_data, pagewords, page_searchwords WHERE
(page_searchwords.media_type='static' AND pagewords.id =
page_searchwords.word_id AND page_data.id = page_searchwords.id AND
LCASE(pagewords.word) LIKE LCASE('%hit%')) OR
(page_searchwords.media_type='static' AND pagewords.id =
page_searchwords.word_id AND page_data.id = page_searchwords.id AND
LCASE(pagewords.word) LIKE LCASE('%two%')) GROUP BY page_data.id ORDER BY
score DESC;
Hi Guys, above is one of the SQL queries my PHP codes makes up to search my
database. That is what it looks like for a two words search, i.e. "hit two".
It looks like the following when it does a one word search, i.e. "hit"
SELECT count(page_searchwords.word_id) as score, page_data.id, filename,
title FROM page_data, pagewords, page_searchwords WHERE
(page_searchwords.media_type='static' AND pagewords.id =
page_searchwords.word_id AND page_data.id = page_searchwords.id AND
LCASE(pagewords.word) LIKE LCASE('%hit%')) GROUP BY page_data.id ORDER BY
score DESC;
I'll now explain the tables to you.
I have the follwing tables in my database. (go here if the stuff below got
all muddle up http://www.ezigraphics.com/sql_explanation.txt)
table: page_data table: pagewords table:
page_searchwords
+----+----------+-------+-------------+ +----+-------------+
+----+---------+------------+
| id | filename | title | contents | | id | word | | id
| word_id | media_type |
+----+----------+-------+-------------+ +----+-------------+
+----+---------+------------+
| 1 | 01_title | ABC | information | | 1 | wtc | | 2 |
1 | static |
| 2 | fileAA | foo | wtc | +----+-------------+
+----+---------+------------+
| 3 | foobar | bar | content | | 2 | information | | 1 +
2 | static |
+----+----------+-------+-------------+ +----+-------------+
+----+---------+------------+
It works like this.
page_data has the webpage information in it, when the page get's update the
SQL I have written get's all of the keywords from the page. It then get's
the id's from the pagewords table (i.e. pagewords.id), adding the words if
it is not already there. Then once it has all of the id's from pagewords
(i.e. pagewords.id) it adds the information to the page_searchwords table.
The page_searchwords.id column contains the equivalent of the page_data.id
column. page_searchwords.word_id is the equivalent of the pagewords.id
column and the media_type is any of the following three strings; static,
pdf, news.
The search works like this it. It sorts through the page_words table and
get's the id of the words the user is searching. In the example above if the
user search's for wtc and information, the id's it would retrieve are 1 and
2. Once it has these id's it then searches the page_searchwords table and
retrieves any row which has a word_id value of either 1 or 2 (being wtc or
information respectively). Once it has this it then write the search results
out to the browser using the following columns from the page_data table; id,
filename, title.
I then also have another table:
table: news
+----+----------+---------+------+
| id | headline | content | date |
+----+----------+---------+------+
| 1 | newHead | contentH| 11.01|
+----+----------+---------+------+
| 2 | header | bulding | 12.01|
+----+----------+---------+------+
This is much the same as the page_data title. I also have the contents of
the news pages being update into the pagewords and page_searchword tables.
What I need to know is how to look through these at the same time, and get
the new id, headline and content out of the database.
Does anybody have any idea? This does make sense doesn't it? Let me know if
it doesn't thanks.
Thanks in advance.
Scott Mebberson.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
