[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