MySQL: how to extract multiple text files from my MySQL table

Question

I need to extract data from my MySQL database into multiple text files.

I have a table with 4 columns: UserID, UserName, Tag, Score.

I need to create a text file for each Tag, with the userID, the userName and score (ordered by score).

For example

Tag1.txt
234922 John 35
234294 David 205
392423 Patrick 21

Tag2.txt
234922 John 35
234294 David 205
392423 Patrick 21

and so on...

Edited: Sample: http://dl.dropbox.com/u/72686/expertsTable.png

Answer 1

I'm not sure there's a query that will write every file for you automatically, unless you use stored procedures or a cursor of some kind (I'm unfamiliar with those for the most part).

However here are two queries you may find useful:

This will select the top scorer from each tag and list them:

SELECT userid, name, score, tag FROM users
GROUP BY tag
ORDER BY score DESC;

This will select all the scores from a given tag (z-wave in this example), in order of score:

DECLARE @TagName AS STRING;
SET @TagName = "z-wave";
SELECT userid, NAME, score, tag FROM users
WHERE tag = @TagName
ORDER BY score DESC;

Note that in your example desired results, the scores do not seem to be in order.

Answer 2

You can write code for the normal mysql_fetch_object() function and write file function from PHP can be used to solve your problem. See below.

$Query = "select * from Table1";
$row = $mysql_query($Query);
while($rs = mysql_fetch_object($row))
{
    $filecontent = $rs->userid . ' '. $rs->username . ' '. $rs->score;
    $myFile = $rs->tag.".txt";
    $fh = fopen($myFile, 'w') or die("can't open file");
    $stringData = $filecontent."\n";
    fwrite($fh, $stringData);
    fclose($fh);
}