MyBB Community Forums

Full Version: SQL Help please :D
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2 3
I'm working on a porject for school, a search engine. I have a KeywordMapping table, that maps a Keywords ID (KID) to a page ID (PID).

I want a query whereby I can say

"SELECT * FROM search_KeywordMapping WHERE KID in(1,2,3,4,5) GROUP BY PID ORDER BY the number of matches."

Basically, I want a PID with 5 keyword ID matches, to come above a PID with only 4 or 3 keyword matches Smile


Any ideas?
You can use LIMIT 5, like this;
"SELECT * FROM search_KeywordMapping WHERE KID in(1,2,3,4,5) GROUP BY PID ORDER BY the number of matches. LIMIT 5"
What do you mean?

Here is an example:

PID : KID
1 : 3
2 : 4
2 : 1
3 : 5
3 : 1
3 : 2

So PID 3 would rank above PID 2, which would in turn be above PID 1

So it would be:
$row[0]['PID'] = 3
$row[1]['PID'] = 2
$row[2]['PID'] = 1

Smile
OK, I thought you meant to have limit in PIDs. Did you tried using DESC after "ORDER BY the number of matches" in the query?
I was wondering if that would work, or if there was a better way. If not, i'll have to do it in a round about way within PHP Toungue
Try

SELECT field1, field2, field3, PID, count(*) FROM search_KeywordMapping WHERE KID in(1,2,3,4,5) GROUP BY PID, field1, field2, field3 order by count(*)

I don't have a table that's exactly like what you're using, but it seems to give about what you want. (You may want the order in the group by to be different.)
Unfortunately there are only two fields Confused
Then
SELECT PID, count(*) FROM search_KeywordMapping WHERE KID in(1,2,3,4,5) GROUP BY PID order by count(*)
You'll get a lot of records, each with the PID and the number of matches for that PID. (That's the type of table I tested it on - 2 fields.)
OK Try this:
(Sorry I modified the results a bit, you should change "your_table" to your table's name:

SELECT DISTINCT PID AS RPID, (SELECT COUNT(KID) FROM your_table WHERE PID = RPID AND KID IN ('1', '2', '3', '4', '5')) AS NUM FROM your_table ORDER BY NUM DESC

It will produce 2 columns, the first is called RPID and is exactly your PID, the second is called NUM and it means the number of occurrences of that KID
(I think that's what you asked for Smile)
ok, that works fine, apart from one small thing:

If I search for "catering" (that is keyword ID: 5) it gives me this:

PID: Keyword Matches
2 : 1
3 : 0
4 : 0
5 : 0
6 : 0
etc...

Basically, it lists EVERY distinct PID, even if it has 0 keyword matches. This makes for a MASSIVE overhead. Is there any way to alter that SQL to only include PID's in the resultset that have 1 or more keyword matches?


I would also like to bring in the 'URL' and 'PageTitle' from the table: "search_Pages", and only include any pages in the final results set that have 'Crawled' set to 1. So i'm thinking some kind of INNER JOIN Smile
Pages: 1 2 3