I'm reading the book Programming Collective Intelligence where all the examples are done in Python and sqllite. However, I'm doing all the examples in PHP / MYSQL.
Chapter 4, pages 63 and 64 discuss how to retrieve results from a search. Specifically, it retrieves document ids whose related texts contain all the words in the search phrase and returns that id along with the location of each word's location in the text.
Thankfully, these pages and code are available online for free for reference. Here is the query from page 64:
select w0.urlid,w0.location,w1.location
from wordlocation w0,wordlocation w1
where w0.urlid=w1.urlid
and w0.wordid=10
and w1.wordid=17
Here is the example result set from the book where the first number is the ID of the document, and the second and third numbers represent the location of each word in that specific document.
e.getmatchrows('functional programming')
([(1, 327, 23), (1, 327, 162), (1, 327, 243), (1, 327, 261),
(1, 327, 269), (1, 327, 436), (1, 327, 953),..
I know nothing about databases, so this query is throwing me a bit. How would I write this in MySQL?
I'm confident in my PHP, and I know once I understand the SQL and how to construct it dynamically, I'll have no problem generating the PHP code for the getmatchrows() function. But, if you have a suggestion for the related getmatchrows PHP definition to accompany the SQL query, please share that.
That query will work, although the more modern syntax uses explicit joins:
SELECT w0.urlid, w0.location, w1.location
FROM wordlocation w0
JOIN wordlocation w1 ON w0.urlid = w1.urlid
WHERE w0.wordid = 10
AND w1.wordid = 17
with more words it would be:
SELECT w0.urlid, w0.location, w1.location, w2.location, w3.location, w4.location
FROM wordlocation w0
JOIN wordlocation w1 ON w0.urlid = w1.urlid
JOIN wordlocation w2 ON w0.urlid = w2.urlid
JOIN wordlocation w3 ON w0.urlid = w3.urlid
JOIN wordlocation w4 ON w0.urlid = w4.urlid
WHERE w0.wordid = 10
AND w1.wordid = 17
AND w2.wordid = 101
AND w3.wordid = 25
AND w4.wordid = 4
select w0.urlid,w0.location,w1.location
from wordlocation w0,wordlocation w1
where w0.urlid=w1.urlid
and w0.wordid=10
and w1.wordid=17
is a valid mySQL query and should work just fine.