Query for search engine 1

[SQLScholar]

Hey all,

I am currently learning PHP. I have a website which i am making which has a mysql backend. I am currently creating a search. I have two little issues.

Firstly i have this code:

SELECT weblink, metadesc, 1 as rank FROM items where weblink like '%[URL unfurl="true"]www%'[/URL]
union 
SELECT weblink, metadesc, 2  FROM items where metakeywords like '%[URL unfurl="true"]www%'[/URL]
union
SELECT weblink, metadesc, 3  FROM items where metadesc like '%[URL unfurl="true"]www%'[/URL]

order by rank desc

in the table "items" there are two records. If i run the above query i only get two records. If i remove all the "where" criterias - i get 6 (what i expect). Why am i only getting 2 results???

Secondly what i am trying to do is some kind of ranking system so that if the searched keyword is in the weblink field it appears higher in the search then in the metakeywords field and same with meta desc.

Now this should work.... however i have just thought that the results i want is actually the record shown with its highest rank. Because two or all three queries may come back with the same record, but i am only interested in the top one.

Any ideas?

TIA

Dan

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[r937]

you're getting only 2 results because of the WHERE clauses

to obtain an overall rank for each row, just sum up the ranks from the UNION query

it should be UNION ALL instead of UNION, by the way

SELECT weblink
     , metadesc
     , SUM(rank) AS overall_rank
  FROM ( SELECT weblink
              , metadesc
              , 1 as rank 
           FROM items 
          WHERE weblink LIKE '%[URL unfurl="true"]www%'[/URL]
         UNION ALL
         SELECT weblink
              , metadesc
              , 2
           FROM items 
          WHERE metakeywords LIKE '%[URL unfurl="true"]www%'[/URL]
         UNION ALL
         SELECT weblink
              , metadesc
              , 3
           FROM items 
          WHERE metadesc LIKE '%[URL unfurl="true"]www%'[/URL]
       ) AS u
GROUP
    BY weblink
     , metadesc
ORDER
    BY overall_rank DESC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SQLScholar]

DOH. of course.

Didnt think about what the where was doing. DOH - obvious.

Thanks for the ranking code.

Dan

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------