MySQL

Union Site Search with Relevance

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: May 2008

Posts: 45

Reputation: DiGSGRL is an unknown quantity at this point

Solved Threads: 4

DiGSGRL

Offline

Light Poster

Union Site Search with Relevance

Dec 31st, 2008

I am building a site search that involves a union of two tables in two databases and needs to have relevancy factored in.
I do not want to use a full-text or boolean search.

I can search one database and one table using relevance fine. I can union the two tables and search without relevance fine. When I put relevance on both tables with the union it no longer works.

Here is the query with union that works:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
(SELECT gameID,title FROM searchTest.games 
WHERE((title LIKE '%$searchTerm1%' OR author LIKE '%$searchTerm1%' OR description LIKE '%$searchTerm1%')))
UNION 
(SELECT postID,title FROM searchTest2.posts WHERE((title LIKE '%$searchTerm1%' OR username LIKE '%$searchTerm1%' OR content LIKE '%$searchTerm1%') ))
(SELECT gameID,title FROM searchTest.games 
WHERE((title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%')))
UNION 
(SELECT postID,title FROM searchTest2.posts WHERE((title LIKE '%$searchTerm1%' or username LIKE '%$searchTerm1%' or content LIKE '%$searchTerm1%') ))

Here is the query on one table with relevance that works:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT gameID,title, ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))AS relevance FROM games WHERE(title LIKE '%$searchTerm1%' OR author LIKE '%$searchTerm1%' OR description LIKE '%$searchTerm1%')AND locked=0 ORDER BY relevance DESC
SELECT gameID,title, ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))AS relevance FROM games WHERE(title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%')AND locked=0 ORDER BY relevance DESC

Here is the query with union and relevance that does not work:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT gameID,title,
 ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))
AS relevance FROM games WHERE
(((title LIKE '%$searchTerm1%' OR author LIKE '%$searchTerm1%' OR description LIKE '%$searchTerm1%'))AND locked=0)
UNION 
SELECT postID,title,
((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))
AS relevance FROM searchTest2.posts WHERE
(((title LIKE '%$searchTerm1%' OR username LIKE '%$searchTerm1%' OR content LIKE '%$searchTerm1%'))AND locked=0)
SELECT gameID,title,
 ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))
AS relevance FROM games WHERE
(((title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%'))AND locked=0)
UNION 
SELECT postID,title,
((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))
AS relevance FROM searchTest2.posts WHERE
(((title LIKE '%$searchTerm1%' or username LIKE '%$searchTerm1%' or content LIKE '%$searchTerm1%'))AND locked=0)

I wouldn't be surprised if I did something really silly and screwed up a paren or something. I have been working on this for days and just can't seem to find the mistake that is keeping the union with relevance from running.
Thank you for looking and I hope someone can help!

•

Join Date: May 2008

Posts: 45

Reputation: DiGSGRL is an unknown quantity at this point

Solved Threads: 4

DiGSGRL

Offline

Light Poster

Re: Union Site Search with Relevance

Dec 31st, 2008

A couple more hours of staring at the screen and rewriting the statements have finally solved the problem. Both of the select statements needed to be encased in parens.

Quick Reply to Thread

Show Printable Version

Other Threads in the MySQL Forum

Previous Thread: MySQL backup converting all data to lowercase
Next Thread: Calling a procedure from Trigger

Thread Tools	Search this Thread
Show Printable Version Email this Page

DaniWeb Message

Cancel Changes