Battleangel | Personal

Just cosmetic changes as well as a new SELECT statement using UNIX_TIMESTAMP to facilitate conversion and display of temporal data. It’s amazing what you can do without actually leaving MYSQL. Calculating the time difference between a stored value and “now()” is strikingly simple: ( UNIX_TIMESTAMP( now() ) - UNIX_TIMESTAMP( played ) ). It is now also possible to send a LIMIT string for the SELECT statement as a query parameter.

Recent Winamp Playlist: DISABLED (Was using WINAMP, CURL, AMIP, PHP and MYSQL)

AMIP Preset (data inside “” must not contain new line):
[code]/exec:(x:\pathto\curl.exe) -d “title=&func_ue(%name)&artist=&func_ue(%1)&track=&func_ue(%2) &album=&func_ue(%4)&bitrate=&func_ue(%br)&action=save” http://server/file.php[/code]
Database structure:
[mysql]CREATE TABLE amipsql
(
id smallint NOT NULL PRIMARY KEY,
artist char(75),
title char(75),
album char(75),
track smallint,
length TIME,
bitrate smallint,
played timestamp(14)
)[/mysql]
Store the data:
[php]function connect2db() {
global $dbhost, $dbuser, $dbpass, $dbname, $db;
@ $db = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$db) {
$error_string = “A database error has occured.” . mysql_error($db);
die($error_string); }
mysql_select_db($dbname, $db);
return $db;
}

if(isset($_REQUEST['action']) && $_REQUEST['action'] == ’save’){
$db = connect2db();
$query = “INSERT INTO amipsql (title,artist,album,bitrate) VALUES (’$_REQUEST[track]‘, ‘$_REQUEST[artist]‘, ‘$_REQUEST[album]‘, ‘$_REQUEST[bitrate]‘)”;
$result = mysql_query($query);
mysql_close($db);
}[/php]
Fetch old playlist:
[php]global $mp3s;
if( !(isset($_REQUEST['mp3s'])) || (($_REQUEST['mp3s'])>40) ){
$mp3s = ‘40′;
}
$amipsql = mysql_query(”SELECT *,( UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(played) ) AS timeago FROM amipsql GROUP BY ID DESC LIMIT $mp3s”);

echo “

“;

while ($amiprow = mysql_fetch_row($amipsql)) {

$intday = intval($amiprow[6] / 86400);
$inthour = intval($amiprow[6] / 3600);
$intmin = intval($amiprow[6] / 60);
$intsec = $amiprow[6] - ( $intmin * 60 );
if ($amiprow[6] < 60) { $timestatement = $amiprow[6]." second(s) ago"; }
elseif ($amiprow[6] < 3600) { $timestatement = $intmin." minute(s) and ".$intsec." second(s) ago"; }
elseif ($amiprow[6] < 172800) { $timestatement = "Over ".$inthour." hour(s) ago"; }
elseif ($amiprow[6] < 2592000) { $timestatement = "More than ".$intday." day(s) ago"; }
elseif ($amiprow[6] > 2592000) { $timestatement = “<ZZZzzzzz>”; }

echo “

1. “.$amiprow[2].” - “.$amiprow[1].”
   (from $amiprow[3])
   [ ".$timestatement." ]

\n”;
}

echo “

($mp3s rows printed)“;[/php]
Fetch just the last song:
[php]$amipsql_one = mysql_query(”SELECT * FROM amipsql GROUP BY ID DESC LIMIT 1″);
$amiprow = mysql_fetch_row($amipsql_one);
echo “$amiprow[2] - $amiprow[1]“;[/php]