[SOLVED] sqlite song tracking

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
brandan
Forum Commoner
Posts: 37
Joined: Sat Jul 24, 2004 6:39 pm
Location: fort smith, ar

[SOLVED] sqlite song tracking

Post by brandan »

i started work on a script last night, and i don't really know much about php or sqlite. what i've done so far is based on what i've learned in the past day or so. what i'm trying to do is something similar to audioscrobbler.com, only on a 'personal' level. here is the problem that i am having, maybe somebody can help me out.

Code: Select all

<?
$lines = file('playlist.inc');
$db = $_SERVER['DOCUMENT_ROOT']."/../music.db";
$db2 = $_SERVER['DOCUMENT_ROOT']."/../artist.db";
$db3 = $_SERVER['DOCUMENT_ROOT']."/../track.db";
$handle = sqlite_open($db,0666,$error_message) or die($error_message);
$handle2 = sqlite_open($db2,0666,$error_message) or die ($error_message);
$handle3 = sqlite_open($db3,0666,$error_message) or die ($error_message);
foreach ($lines as $ln) {
	parse_str($ln);
	if(isset($artist)) {
		$query = "insert into entries (artist, album, title, date, time) values (\"$artist\", \"$album\", \"$title\", \"$date\", \"$time\")";
		$result = sqlite_query($handle, $query) or die ("Error in query: ".sqlite_error_string(sqlite_last_error($handle)));
		$artist_plays = "select * from entries where artist = \"$artist\"";
		$artist_plays_results = sqlite_query($handle, $artist_plays) or die ("Error in queeeeery: ".sqlite_error_string(sqlite_last_error($handle)));
		if (sqlite_num_rows($artist_plays_results) > 0) {
			$artist_plays_results0 = sqlite_num_rows($artist_plays_results);
			$artist_plays = "select * from plays where artist = \"$artist\"";
			$artist_plays_results = sqlite_query($handle2, $artist_plays) or die ("Error in queeeeery: ".sqlite_error_string(sqlite_last_error($handle2)));
			if (sqlite_num_rows($artist_plays_results) == 0) {
				$query2 = "insert into plays (artist, plays) values (\"$artist\", \"$artist_plays_results0\")";
				$result2 = sqlite_query($handle2, $query2) or die ("Error in quarrrry: ".sqlite_error_string(sqlite_last_error($handle2)));
			}
			if (sqlite_num_rows($artist_plays_results) > 0) {
				$query2 = "update plays set plays=\"$artist_plays_results0\" where artist=\"$artist\"";
				$result2 = sqlite_query($handle2, $query2) or die ("Error in query: ".sqlite_error_string(sqlite_last_error($handle2)));
			}
			$track_plays = "select * from entries where title = \"$title\" and artist = \"$artist\"";
			$track_plays_results = sqlite_query($handle, $track_plays) or die ("Error in queeeasdfeery: ".sqlite_error_string(sqlite_last_error($handle3)));[b]
			if (sqlite_num_rows($track_plays_results) == 0) {
				$track_plays_results = sqlite_num_rows($track_plays_results);
				echo $track_plays_results;
				echo "1";
				$query3 = "insert into plays (track, artist, plays, album) values (\"$title\", \"$artist\", \"$track_plays_results\", \"$album\")";
				$result3 = sqlite_query($handle3, $query3) or die ("Error in query: ".sqlite_error_string(sqlite_last_error($handle3)));
			}
			if (sqlite_num_rows($track_plays_results) > 0) {
				$track_plays_results = sqlite_num_rows($track_plays_results);
				$query3 = "update plays set plays=\"$track_plays_results\",artist = \"$artist\", track=\"$track\" where artist=\"$artist\" and track=\"$track\"";
				$result3 = sqlite_query($handle3, $query3) or die ("Error in query: ".sqlite_error_string(sqlite_last_error($handle2)));[/b]
			}
		}
	}
}

sqlite_close($handle);
sqlite_close($handle2);
sqlite_close($handle3);
unlink('playlist.inc');
?>
basically the entire bolded section of the script doesn't want to work correctly. when i comment it out, everything else runs fine, and i can't seem to get any specific error messages from php to point me in the right direction. any help would be much appreciated.


feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
brandan
Forum Commoner
Posts: 37
Joined: Sat Jul 24, 2004 6:39 pm
Location: fort smith, ar

Post by brandan »

thanks, now the bolded section of the script doesn't show up. :>

Code: Select all

$track_plays = "select * from entries where title = \"$title\" and artist = \"$artist\"";
            $track_plays_results = sqlite_query($handle, $track_plays) or die ("Error in queeeasdfeery: ".sqlite_error_string(sqlite_last_error($handle3)));[b]
            if (sqlite_num_rows($track_plays_results) == 0) {
                $track_plays_results = sqlite_num_rows($track_plays_results);
                echo $track_plays_results;
                echo "1";
                $query3 = "insert into plays (track, artist, plays, album) values (\"$title\", \"$artist\", \"$track_plays_results\", \"$album\")";
                $result3 = sqlite_query($handle3, $query3) or die ("Error in query: ".sqlite_error_string(sqlite_last_error($handle3)));
            }
            if (sqlite_num_rows($track_plays_results) > 0) {
                $track_plays_results = sqlite_num_rows($track_plays_results);
                $query3 = "update plays set plays=\"$track_plays_results\",artist = \"$artist\", track=\"$track\" where artist=\"$artist\" and track=\"$track\"";
                $result3 = sqlite_query($handle3, $query3) or die ("Error in query: ".sqlite_error_string(sqlite_last_error($handle2)));[/b]
            }
this is what seems to be causing problems.
Last edited by brandan on Mon Mar 21, 2005 10:09 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

not that it would show in a

Code: Select all

block..
brandan
Forum Commoner
Posts: 37
Joined: Sat Jul 24, 2004 6:39 pm
Location: fort smith, ar

Post by brandan »

'not that it would show in a

Code: Select all

block..'

[code]
[b]ac[/b]tually, it did. :|
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it shouldn't... and will be resolved soon.
brandan
Forum Commoner
Posts: 37
Joined: Sat Jul 24, 2004 6:39 pm
Location: fort smith, ar

Post by brandan »

could you roll your eyes for me again? please?
brandan
Forum Commoner
Posts: 37
Joined: Sat Jul 24, 2004 6:39 pm
Location: fort smith, ar

Post by brandan »

ok, the problem was with a way i was approaching something, and i'm pretty sure that nobody would really figure it out unless they had actual access to my databases. anyway, it's solved, thanks.
Post Reply