Subject: RE: Digital Tradition Upgrade?
From:
GUEST,Jon
Date: 10 Jan 07 - 01:44 PM
Dick, I'd already built a MySQL datbase from a text file imported from AskSam for this we talked a little about in the last thread on the dt.
I just ran a php script on the MySQL database to produce the HTML version. My coding isn't particularly good but here it is if you want to see how it was made from the database.
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return (float) $usec + (float) $sec;
}
function addkeyword($SongID)
{
global $file, $nl;
$SQL ="Select keyword.KeywordID, keyword.Keword from keyword, songkeyword
where keyword.KeywordID = songkeyword.KeywordID and songkeyword.SongID = " . $SongID;
$result = mysql_query($SQL);
$rs = mysql_fetch_array($result);
$added = false;
while (!($rs==0))
{
if ($added)
fwrite($file, ", ");
else
$added = true;
fwrite($file, "<a href='../keyword/" . $rs["Keword"] . ".html'>" . $rs["Keword"] . "</a>");
$rs=mysql_fetch_array($result);
}
fwrite($file, $nl);
}
function addtune($SongID)
{
global $file, $nl;
$SQL = "Select ABC, Title From tune, songtune where SongID = " . $SongID . " and songtune.tuneid=tune.tuneid";
$result = mysql_query($SQL);
$rs = mysql_fetch_array($result);
while (!($rs==0))
{
fwrite($file, "<a href='../tune/" . $rs["Title"] . ".html'>" .$rs["Title"] . "</a>" . " ");
$rs=mysql_fetch_array($result);
}
fwrite($file, $nl);
}
function addkeywordsong($KeywordID)
{
global $file, $nl;
$SQL = "Select Title, song.Filename From song, songkeyword
where song.SongID=songkeyword.SongID and
KeywordID = " . $KeywordID;
$result = mysql_query($SQL);
$rs = mysql_fetch_array($result);
while (!($rs==0))
{
fwrite($file, "<a href='../song/" . $rs["Filename"] . ".html'>" .$rs["Title"] . "</a>" . $nl);
$rs=mysql_fetch_array($result);
}
fwrite($file, $nl);
}
require("config.php");
require("top.php");
$start = microtime_float();
$nl = "<br>" . chr(13).chr(10);
//add the songs
echo "adding songs<br>";
$SQL = "SELECT * FROM song ORDER BY Title";
$result = mysql_query($SQL);
$rs = mysql_fetch_array($result);
@mkdir("html");
@mkdir("html/song");
@mkdir("html/tune");
@mkdir("html/keyword");
while (!($rs==0))
{
$filename = "html/song/" . $rs["Filename"] . ".html";
$file = fopen($filename, 'w');
//echo $filename . "<br>";
$SongID = $rs["SongID"];
$Song = $rs["Song"];
fwrite($file, "<b>" . $rs["Title"] . "</b>" . $nl);
fwrite($file, $Song);
fwrite($file, "<b>Keywords: </b>");
addkeyword($SongID);
fwrite($file, "<b>Tune: </b>");
addtune($SongID);
fclose($file);
$rs=mysql_fetch_array($result);
}
//add the tunes
echo "adding tunes <br>";
$SQL = "SELECT * FROM tune";
$result = mysql_query($SQL);
$rs = mysql_fetch_array($result);
while (!($rs==0))
{
$filename = "html/tune/" . $rs["Title"] . ".html";
$file = fopen($filename, 'w');
fwrite($file, str_replace(chr(13), $nl, $rs["ABC"]));
fclose($file);
$rs=mysql_fetch_array($result);
}
//add the kewords
echo "adding keywords<br>";
$SQL = "SELECT * FROM keyword";
$result = mysql_query($SQL);
$rs = mysql_fetch_array($result);
while (!($rs==0))
{
$filename = "html/keyword/" . $rs["Keword"] . ".html";
//echo $filename . "<br>";
$file = fopen($filename, 'w');
addkeywordsong($rs[KeywordID]);
fclose($file);
$rs=mysql_fetch_array($result);
}
//make an index
echo "adding index<br>";
$indexfilename = "html/index.html";
$indexfile = fopen($indexfilename, 'w');
for ($I=65; $I<=90; $I++)
{
fwrite($indexfile, "<a href=".chr($I). ".html>" . chr($I) . "</a> ");
if ($I == 65)
$where = "Title <='B'";
elseif ($I == 90)
$where = "Title >='Z'";
else
$where = "Title >='" . chr($I) . "' AND Title <'" . chr($I+1) . "'";
$SQL = "SELECT Title, Filename FROM song Where " . $where;
$result = mysql_query($SQL);
echo mysql_error();
$rs = mysql_fetch_array($result);
$file=fopen("html/" . chr($I) . ".html", 'w');
while (!($rs==0))
{
fwrite($file, "<a href='song/" . $rs["Filename"] . ".html'>" .$rs["Title"] . "<a>" . $nl);
$rs=mysql_fetch_array($result);
}
fclose($file);
}
fclose($indexfile);
$end = microtime_float();$end = microtime_float();
echo "File built in " . round($end - $start,2) . "seconds";
?>
|
sj
