SQLite - Music Library and Music Selector

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
Straterra
Forum Regular
Posts: 527
Joined: Mon Nov 24, 2003 8:46 am
Location: Indianapolis, Indiana
Contact:

SQLite - Music Library and Music Selector

Post by Straterra »

I have a music system that I wrote just for my website. Now, I am sharing it all for the world to see. I am going to post all of the code here, as well as a link to the files.

To run this script, you must first open the config.php file in your favorite PHP editor and supply the file location information and setting information.

Then, you must execute the setup.php program in a browser window (like you would any PHP script.)

Next, you can start adding music and organizing your music library! Enjoy!!


config.php

Code: Select all

<?php
$dbname = "musicdatabase";//Set this to your database name
$cook = "sitemusic";//Set this to the cookie name
$table = "musictable";//Set this to the table name
$menupage = "pickmusic.php";//Set this to the page where the music menu will be present
$musicpage = "playmusic.php";//Set this to the page where the music will be played from
$addmusicpage = "addmusic.php";//Set this to the page where you can add music
$addmusicpageform = "addmusicform.php";//Set this to the form of where you can add music
$cookiepage = "cookie.php";//Set this to the page where it processes the data and puts it into a cookie
$deletemusicpage = "deletemusic.php";//Set this to the page where you can delete music
$deletemusicpageform = "deletemusicform.php";//Set this to the form of where you can delete music
$loop = "true";//If you want the music to play foreever, then make this true. If not, then set it to false
$autostart = "true";//If you want the music to play automatically, then set this to true. If you don't, set it to false
$visible = "true";//Keep this true if you want to allow the user to stop, pause and restart this song. BEWARE, if this is set to false, many people will NOT like your site, as it does not allow the user to disable the music. Also, the only way to disable the music when this value is set to false, is to stop the page. By stopping it, you also stop all animated images (.gif) and perhaps maybe even stop your Javascript
?>
setup.php

Code: Select all

<?php
include('config.php');//This must be the first thing
if ($dbname == "" or $cook == "" or $table == "" or $menupage == "" or $musicpage == "" or $addmusicpage == "" or $addmusicpageform == "" or $cookiepage == "" or $deletemusicpage == "" or $deletemusicpageform == "" ) {//Checks and sees if the config.php file has been filled out
echo "You did not completely setup the config file (config.php). Please go back and do so.";//Tells the user to fill out the config.php file
}
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){//Attempts to open the database. If it doesn't exist, it makes the database.
$moo = sqlite_query($db, "create table $table (artist varchar(60), songname varchar(60), url varchar(150), dte varchar(50), cookiename varchar(12))");//Creates a new table in the database
echo "Database and table created!";//Alerts the user the database has been made
} else {
  die ($sqliteerror);//Displays any errors that may have occured when trying to open or make the database.
}
?>
log.php

Code: Select all

<?php
include('config.php');//This must be the first thing
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){
$result = (sqlite_query($db, "SELECT cookiename from $table ORDER BY artist"));//Gets the cookiename from the table
?>
<table width="100%" border="2" cellspacing="0" cellpadding="0">
<b>  <tr>
    <td><b>Artist</td>
    <td><b>Song Name</td>
    <td><b>URL</td>
    <td><b>Date</td>
  </tr>
  <?php
while ($row=sqlite_fetch_array($result)) {
$moo = $row['cookiename'];
$result2 = sqlite_query($db, "select artist from $table where cookiename = '$moo'");//Gets the artist from the table
$result2 = sqlite_fetch_array($result2);
$result3 = sqlite_query($db, "select songname from $table where cookiename = '$moo'");//Gets the songname from the table
$result3 = sqlite_fetch_array($result3);
$result4 = sqlite_query($db, "select url from $table where cookiename = '$moo'");//Gets the url from the table
$result4 = sqlite_fetch_array($result4);
$result5 = sqlite_query($db, "select dte from $table where cookiename = '$moo'");//Gets the date from which the song was added from the table
$result5 = sqlite_fetch_array($result5);
//It will now display the information in a user friendly table//
?>
  <tr>
    <td><?php echo stripslashes($result2['artist']); ?></td>
    <td><?php echo stripslashes($result3['songname']); ?></td>
    <td><a href="<?php echo stripslashes($result4['url']); ?>"><?php echo stripslashes($result4['url']); ?></a></td>
    <td><?php echo stripslashes($result5['dte']); ?></td>
  </tr>

<?php
}//Closes the while statement (By default, its on line 14)
}//Closes the database opening if statement (By default, its on line 3)
?>
</table>
cookie.php

Code: Select all

<?php
include('config.php');//This must be the first thing
?>
<?php
if ( isset($_POST['dropdown']) == true ) {//Detects if there has been any data posted.
$dropdown = $_POST['dropdown'];//Gets the cookiename selected from the dropdown from the previous screen.
setcookie ($cook, $dropdown,time()+60*60*24*7);//Sets the cookiename
header('Location: '.$musicpage);//Redirects to the website that will play the music after the cookie is set
} else {
header('Location: '.$addmusicpageform);//Redirects the user to pick a song, if they didn't before.
}//Closes the data posting if statement (By default, its on line 5)
?>
addmusicform.php

Code: Select all

<?php
include('config.php');//This must be the first thing
?>
<form action="<?php echo $addmusicpage; ?>" method="post">
Artist
<input type="text" name="artist"><br>
Song Name
<input type="text" name="songname"><br>
URL
<input type="text" name="url"><br>
<input type="submit" name="submit" value="Submit">
<input type="reset" name="reset" value="Reset">
addmusic.php

Code: Select all

<?php
include('config.php');//This must be the first thing
if ( isset($_POST['artist']) == true and isset($_POST['songname']) == true and isset($_POST['url']) == true ) {//Makes sure that the user came from the addmusic form page
$artist = sqlite_escape_string($_POST['artist']);//Makes the string $artist SQLite friendly
$songname = sqlite_escape_string($_POST['songname']);//Makes the string $songname SQLite friendly
$dte = sqlite_escape_string(date("F j, Y, g:i a"));//Makes the string $dte SQLite friendly
$url = sqlite_escape_string($_POST['url']);//Makes the string $url SQLite friendly
$cookiename = $artist.$songname;//Creates a unique cookie name
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){//Tries to open the database
$sql = "SELECT url FROM $table WHERE url = '$url'";//Retrives all URL's from the table, to make sure no copies are inserted
$sql_result = sqlite_query($db, $sql);
if (sqlite_num_rows($sql_result) != 1) {
sqlite_query($db, "insert into $table
(artist, songname, url, cookiename, dte)
values ('$artist', '$songname', '$url', '$cookiename', '$dte')");//Once found that the URL doesn't exist in the table, SQLite adds it to the table
header('Location: '.$addmusicpageform);
} else {
echo "You have tried to add a song that is already in the Music Database...";//This message is returned when a song with that URL was already in the table
?>
     <meta http-equiv="REFRESH" content="3;URL=<?php echo $addmusicpageform; ?>">
     <?php
     }//Closes the if statement that checks to make sure that the URL doesn't exist in the table. (By default, its on line 12)
     } else {
  die ($sqliteerror);//If the database could not be opened, it will return this error
  }//Closes the database opening if statement (By default, its on line 9)
  } else {
  echo "Please go back and fill out all of the forms. Thank you.";//Tells the user to use the form to add music
  }//Closes the if statement that checks and makes sure that all necessary data was posted.(By default, its on line 3)
?>
deletemusicform.php

Code: Select all

<?php
include('config.php');//This must be the first thing
?>
<center>
<form action="<?php echo $deletemusicpage; ?>" method="post">
<select name="dropdown">
<?php
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){//Tries to open the database.
$result = sqlite_query($db, "select cookiename from $table ORDER BY artist, songname");//Gets all of the music from the database, ordered first by Arist, then by songname
while ($row=sqlite_fetch_array($result)) {
$moo = $row['cookiename'];
$result2 = sqlite_query($db, "select songname from $table where cookiename = '$moo'");//Gets the songname from the database where the cookiename is that of the one stored in the cookie
$result4 = (sqlite_query($db, "SELECT artist from $table where cookiename = '$moo'"));//Gets the artist from the database where the cookiename is that of the one stored in the cookie
$result2 = sqlite_fetch_array($result2);
$result4 = sqlite_fetch_array($result4);
?>
<option value="<?php echo $row['cookiename']; ?>"><?php echo stripslashes($result4['artist']); ?> - <?php echo stripslashes($result2['songname']); ?></option>
<?php
}//Closes the while statement above
?>
</select>
<br>
<input type="submit" name="submit" value="Submit">
<input type="reset" name="reset" value="Reset">
<?php
} else {
  die ($sqliteerror);//If the database could not be opened, it will return this error
}//Closes the database opening if statement (By default, its on line 8)
?>
deletemusic.php

Code: Select all

<?php
include('config.php');//This must be the first thing
if ( (isset($_POST['dropdown']) == true) ) {//Tests to make sure that the user used a form to submit data
$dropdown = stripslashes($_POST['dropdown']);//Removes all \ slashes.
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){//Tries to open the database.
sqlite_query($db, "delete from $table
where cookiename = '$dropdown'");//Deletes the song from the table, according to it's cookiename value
header('Location: '.$deletemusicpageform);//Directs the user back to the delete page to delete another song
} else {
  die ($sqliteerror);//If the database could not be opened, it will return this error
}//Closes the database opening if statement (By default, its on line 5)
} else {
echo "You did not pick a song to delete. Please go back and do so...";
?>
<meta http-equiv="REFRESH" content="3;URL=<?php echo $deletemusicpageform; ?>">
<?php
}//Closes the submitted data if statement (By default, its on line 3)
?>
pickmusic.php

Code: Select all

<?php
include('config.php');//This must be the first thing
?>
<center>
Please select what song you would like to play.
<form method="post" action="<?php echo $cookiepage; ?>">
<select name="dropdown">
<option value="none" selected>None</option>
<option value="none">----------------</option>
<?php
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){ //Tries to open the database.
$result = sqlite_query($db, "select cookiename from $table ORDER BY artist, songname"); //Gets all of the music from the database, ordered first by Arist, then by songname
while ($row=sqlite_fetch_array($result)) {
$moo = $row['cookiename'];
$result2 = sqlite_query($db, "select songname from $table where cookiename = '$moo'");//Gets the songname from the database where the cookiename is that of the one stored in the cookie
$result4 = (sqlite_query($db, "SELECT artist from $table where cookiename = '$moo'"));//Gets the artist from the database where the cookiename is that of the one stored in the cookie
$result2 = sqlite_fetch_array($result2);
$result4 = sqlite_fetch_array($result4);
?>
<option value="<?php echo $row['cookiename']; ?>"><?php echo stripslashes($result4['artist']); ?> - <?php echo stripslashes($result2['songname']); ?></option>
<?php
}//Closes the while statement (By default, its on line 13)
?>
</select>
<br>
<input type="submit" name="Submit" value="Submit">
<?php
} else {
  die ($sqliteerror);//If the database could not be opened, it will return this error
}//Closes the database opening if statement (By default, its on line 11)
?>
playmusic.php

Code: Select all

<?php
include('config.php');//This must be the first thing
if ( isset($_COOKIE[$cook]) == true ) {//Tests to see if the cookie exists
$cookiename = $_COOKIE[$cook];//Assigns the cookie into an easier to manage variable
if ( $cookiename == "none" ) {
echo "<center>You have selected to have no music. <br>";//Displays if the person has selected to not play any music.
} else {
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){//Tries to open the database.
$result = sqlite_query($db, "select * from $table where cookiename = '$cookiename'");//Selects everything from the table.
$result = sqlite_fetch_array($result);
?>
<center>
<embed src="<?php echo stripslashes($result['url']); ?>" autostart="true" loop="true">
<?php
} else {
  die ($sqliteerror);//If the database could not be opened, it will return this error
}//Closes the database opening if statement (By default, its on line 8)
}//Closes the  if statement that checks to see if the cookie contains the value "none" (By default, its on line 5)
} elseif ( isset($_COOKIE[$cook]) == false ) {
header('Location: '.$menupage);//Redirects the user to pick a song, if they didn't before.
}//Closes the cookie checking if statement (By default, its on line 3)
?>
<br>
Click <a href="<?php echo $menupage; ?>">here</a> to pick another song.

If anyone sees any errors (and no, not design, because I don't care how friendly it looks, the point is that the script works) please post here. Thanks!

Oops, I almost forgot! Here is the link for the files, if you would rather download them than copy and paste! Enjoy!

Download Link 1
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

First, it is good to see people sharing code...

.. but this isn't a code SNIPPET, is it?

You said you didn't want design comments but since this forum is dedicated to teaching good php programming and since other people might be tempted to re-use these (ahem) snippets..

Bare scripts are bad unless you are a big fan of Italian food (ie spaghetti). At the very least you should wrap up individual tasks in custom functions. Better still, use OOP. Like any literary effort, code is much easier to understand if you set it out in chapters & paragraphs. You currently have a mental map which allows you to see at a glance what's going on. We don't. Neither will you in a few months time when you've forgotten how this script was put together.

A very large part of the effort of programming is in writing code for people to understand quite apart from the trivial task of making computers do stuff.

One example of this is that you don't have a separate presentation layer. Html mixed up with php raises the spaghetti quotient with a big cheesy dollop of parmesan and then some. Define vars in one layer, then combine with templates & print in another. Echo html directly if you must - but only in the presentation layer.

The db structure (a single table) is simple enough that I guess I won't need to post this link http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf

..but note that you can get all the fields with a single query rather than several queries, one field at a time.
Straterra
Forum Regular
Posts: 527
Joined: Mon Nov 24, 2003 8:46 am
Location: Indianapolis, Indiana
Contact:

Post by Straterra »

The only real reason I posted this was because I am constantly hearing people complain (some on here, mostly not) about not having music on their website, or how to atleast kinda controll their music library...

I understand where you are coming from with the echoing the html, but the way I learned PHP is that you can go in and out of PHP anytime you want..It's a hard habit to break, and alot easer than putting slashes infront of certain characters..

And the deal about the multiple queries..Yes, this is something I was meaning to fix, but originally the script used more than 1 table and database, because it fetched more than what it does now. And for that, I needed multiple queries..I have a tendency to adapt my scripts and keep old code that works, rather than totally rewrite it for the sake of 2 lines space..Also, I know how my code is rather hard for newer people to learn off of, but that is also why I commented nearly ever PHP line in the script..

Thank you for your criticism. I appreciate it, and I will try to work on these "faults" in my future scripts. :)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Ask away any time you have a question about any of these issues.

I'll try to be a bit more tactful :)
Straterra
Forum Regular
Posts: 527
Joined: Mon Nov 24, 2003 8:46 am
Location: Indianapolis, Indiana
Contact:

Post by Straterra »

UPDATE:

playmusic.php

Code: Select all

<?php
include('config.php');//This must be the first thing
if ( isset($_COOKIE[$cook]) == true ) {//Tests to see if the cookie exists
$cookiename = $_COOKIE[$cook];//Assigns the cookie into an easier to manage variable
if ( $cookiename == "none" ) {
echo "<center>You have selected to have no music. <br>";//Displays if the person has selected to not play any music.
} else {
if ($db = sqlite_open($dbname, 0666, $sqliteerror)){//Tries to open the database.
$result = sqlite_query($db, "select * from $table where cookiename = '$cookiename'");//Selects everything from the table.
$result = sqlite_fetch_array($result);
?>
<center>
<embed src="<?php echo stripslashes($result['url']); ?>" visible="<?php echo $visible; ?>" autostart="<?php echo $autostart; ?>" loop="<?php echo $loop; ?>">
<?php
} else {
  die ($sqliteerror);//If the database could not be opened, it will return this error
}//Closes the database opening if statement (By default, its on line 
}//Closes the  if statement that checks to see if the cookie contains the value "none" (By default, its on line 5)
} elseif ( isset($_COOKIE[$cook]) == false ) {
header('Location: '.$menupage);//Redirects the user to pick a song, if they didn't before.
}//Closes the cookie checking if statement (By default, its on line 3)
?>
<br>
Click <a href="<?php echo $menupage; ?>">here</a> to pick another song.
Straterra
Forum Regular
Posts: 527
Joined: Mon Nov 24, 2003 8:46 am
Location: Indianapolis, Indiana
Contact:

Post by Straterra »

I have updated my project to make it easier to seamlessly integrate my script. You now only have to add 2-7 lines onto your existing pages for it to work!! To get the updated version, click here to download it. It comes will all the documentation that you need. Like always, post if you have a problem with anything! Thanks!
Post Reply