[thelist] *crackle* *buzz* calling Rudy and his magical sql brain
Dunstan Orchard
dunstan at 1976design.com
Sat Feb 1 07:16:01 CST 2003
Hey there,
I need some mysql help if anyone has a moment to spare?
I need to write a small search engine query and I keep running into trouble.
I want to search the following fields and get distinct sites.siteid's returned:
sites.name
sites.description
sitereview.review
siteurl.url
These are the kind of searches I want to perform (where someone typed in 'web
standards' into the search box):
("SELECT DISTINCT sites.siteid FROM sites WHERE
(MATCH (sites.name) AGAINST ('web standards')) OR
(MATCH (sites.description) AGAINST ('web standards')) OR
(MATCH (sitereview.review) AGAINST ('web standards')) OR
siteurl.url LIKE '%web%' OR
siteurl.url LIKE '%standards%'
ORDER BY sites.name ASC")
(In fact, I'd ideally like the search to be ordered by the relevancy of the
MATCH searches)
Can anyone make any sense out of that mess? :oD
I've included a simple set of tables below if that helps.
A file showing the proper structure of the database can be seen here:
http://www.maccaws.org/links/structure.txt
An example of a query that Rudy helped me write for another project (and whose
format I'd like to copy) can be seen here:
http://www.maccaws.org/links/example.txt
------------------
sites
------------------
siteid - int(5)
name - tinytext
description - text
date_added - date
------------------
------------------
siteurl
------------------
urlid - int(5)
url - tinytext
siteid - int(5)
------------------
------------------
sitecategory
------------------
sitecategoryid - int(5)
categoryid - int(5)
siteid - int(5)
------------------
------------------
category
------------------
categoryid - int(5)
category - tinytext
level - int(1)
------------------
------------------
sitereview
------------------
reviewid - int(5)
reveiw - text
name - tinytext
email - tinytext
web - tinytext
siteid - int(5)
------------------
I hope that all makes sense to someone, and if anyone can help, I'd really
appreciate it.
Thanks very, very, very much - Dunstan
---------------------------
Dorset, England
http://www.1976design.com/
http://www.orchard.it/
http://www.maccaws.org/
More information about the thelist
mailing list