Compound Mysql Query?

[savkar]

Hi.

Although I am slowly getting up to speed on mysql queries, I admit that more complex ones have eluded me a bit.

I currently have set up two tables for a database I set up of files, in which key words in the files are stored in a keyword database and then there is a separate table which indexes files to the keywords that are in that file. The second file2keys table also stores a "weight" which is simply a count of that specific key's appearance in the file (i.e. 10 times, 5 times, etc).

I have both a keys table and a fid2keys table (file id to keys).

Keys = keyid | key
Fid2keys = fid | keyed | weight

What I want is something along the lines of

Array{fid, weight} = select fid, weight from fid2kid where keyid = { select keyid from keys where key = {list of keys}}

i.e. I figure out the list of keys from a user request, then I want to generate the list of key-ids that are returned for that whole set, then use that set to return the set of file ids and weights that correspond to the list of key_ids I get...

How would you formulate the mysql query for this? I think i understand if I had only one key_id (I think), but in this case I'd like to push off the full list of key words and simply get a list of file_ids, weights in return.

Any help is greatly appreciated!!!

Best regards.

Sunil

[drewbie]

<?
$query = mysql_query ("SELECT keyid, key FROM keys");
while ($result = mysql_fetch_array($query)) {
$query2 = mysql_query ("SELECT fid, weight from fid2keys WHERE fid='$result[keyid]'");
while ($result2 = mysql_fetch_array($query2)) {
//here use the variables $result2[fid] and $result2[weight] to make your array
}
}