I wrote this simple page so that people can understand the SAM Broadcaster and how it interacts with the database, using samPHP web features. I also wrote this because it's my opinion that the samPHP web templates that come with SAM are bloated, execute more code than necessary, and execute code improperly (ex: SQL in the loops) - resulting in a page that loads extremely slow. This page is not here to debate whether the samPHP web templates are bad or good. This page is to teach people that they can access their database using other methods than the samPHP web template, and may get better results. It's purpose is to also show people the bare essentials of achieving this; showing that you can then add-on only the things you need.
So, instead of gutting through the samPHP templates, I wrote my own code to do exaclty and only what I need. I will share the basic elements that you need on a page to connect to your database, query it, and display the information. I am going to assume that you already have samPHP set up and working (you have a database running, can connect to it via the web, and have the samPHP config.php file filled out correctly), that you're using mySQL, and that you're using SAM3. I am going to assume this because none of this will work if you haven't opened the right ports, and set up mySQL correctly. If you have the samPHP web template(s) running already, this shows that you have a good connection to your database already established.
1. Open up notepad (not wordpad, or MS Word), or dreamweaver. We're going to make a php file. Name it dbTest.php, if you wish.
2. Enter the following code:
<?
$usr = "database_username";
$pwd = "database_password";
$db = "samdb";
$host = "database_server_ip";
$sqlCon = mysql_connect($host, $usr, $pwd);
mysql_select_db("database_name");
if (!$sqlCon) { echo("ERROR: " . mysql_error() . "\n"); }Change the values for $user, &pwd etc with the ones in your samPHP config.php file. This code connects to your database. Maintain the quotes around your values. Make sure to update the "database_name" part to whatever the name of the database is that you're wanting to access. For a typical SAM user it would be $db, but if you're starting fresh it would be the actual name in quotes.
3. Below that code, enter the following:
// GET THE CURRENTLY PLAYING SONG INFO
$curPlay = mysql_query("SELECT * FROM historylist ORDER BY date_played DESC LIMIT 1");
$curPlayRow = mysql_fetch_assoc($curPlay);
if (!$curPlay) { echo( mysql_error()); }The first line creates a variable called $curPlay and we set it equal to a command that executes the query we just wrote.
The next line creates a variable called $curPlayRow and we set it equal to another command which returns an array from which our SQL returned (if any). Since our SQL query says DESC LIMIT 1, our array will only return one element in the array (if any).
The last line is an error checker for debugging. This line will only get called if something happens to $curPlay variable. You can remove this line if you wish, or do something else between the { }As of right now, all the information we need for getting the currently playing song is in our Web site. But we need a way to display it. Continue on...
4. Enter the following code, after the above code you just wrote in:
?>
<html>
<head>
<title>Title of this Page</title>
</head>
<body>The first line closes the php programming we did above, and now we can continue with our standard opening html, head, and body tag stuff. We are now ready to program the html in order for us to view the currently playing song that way we want to.
5. Enter the following code, after the above code you just wrote in:
<? echo $curPlayRow["artist"] . " - " . $curPlayRow["title"]; ?>
</body>
</html>
<? mysql_close($sqlCon); ?>This first line will echo out (or write out) the artist name contained in the $curPlayRow array (which we instantiated in step 3), then it will write a space, then a dash, then another space, then the title name.
The second line closes the body tag
The third line closes the html tag
The fourth line closes our sql connection (important!)Now when you view your page, you should see something like:
Current Artist Name - Current Artist Title
These are the simple elements. But what if you want to display more information? Well, in step three we created the necessary code to get the currently playing song. In order to extract out other information from the database (like previous played songs, songs in the queue (songs coming up), requests, artist information etc) you need to create more of these 'chunks'; making sure to have the appropriate SQL commands, and change the variable names to keep them all separated.
Using the code we just created, here's how to add in a list of the 5 previously played songs:
6. After the code you created in step three, enter the following code:
// RECENTLY PLAYED
$recentPlayed = mysql_query("SELECT * FROM historylist ORDER BY date_played DESC LIMIT 6");
$recentPlayedRow = mysql_fetch_assoc($recentPlayed);
if (!$recentPlayed) { echo( mysql_error()); }You'll notice similarities from the code you did in step 3. The difference is the SQL string, and the variable names.
The second line, again, fetches the result returned in line 1.
At this point, it's important to understand that the historylist will always contain the currently playing song. You would think that sam would put the currently playing song into the history list when it's done playing, but it actually puts it in when it plays. Keep this in mind as we continue...
7. In step 5, you inserted a </body> tag. Just before this enter the following code:
<table>
<? while ($recentPlayedRow = mysql_fetch_assoc($recentPlayed)) { ?>
<tr>
<td><? echo $recentPlayedRow["artist"] . " - " . $recentPlayedRow["title"] ?></td>
</tr>
<? } ?>
</table>This will create a table, and for each song (up to 5) it will create a row and put the Artist - Title in for you. Now, remember the currently playing song being the first song listed in the historylist problem? Well, we don't want to show that song in the history list since we're already showing it with our curPlay command. So, you'll notice in step 6 line 4 we get the array (this is where our current song is). In step 7 line 4 we get the array again, and it gets the remaining 5, leaving out the first (currently playing song). This is why in Step 6 line 2 we have DESC LIMIT 6.
For example sake, enter a // before right before the code in Step 6 line 4, save it, and run your page again. Notice the difference? Yes, it still works, but now in your last 5 you have the currently playing song in that list. This is just a tricky way to 'knock off' the top-most row (currently playing) from our list so we don't see it. There are other ways of achieving this, but this is the way I did it.
The code in steps 2, 4, and 5 are the elements that you need. The SQL code is Steps 3, and 6 are editable by you to perform the actions you want on your database. Using all of these key elements, you can now build your own SAM webpage will better efficiency and extract out only the data you need!