Playlist Thingy

RaythMistwalker · February 28, 2010

Right Anyone who uses SAM Broadcaster will know about this.

Yes, SAM does come with its own Playlist creator but this is easier (or should be).

Currently, SAM runs on the database used for my website so when i add songs if i have a webpage to display it will automatically update, and it does.

However, due to having LOTS of songs i need to display these on pages which go by Artist name (eg ABBA on page A, Vengaboys on V) but atm it all displays on 1 page and i cant seem to work out why.

Here is the current code:

<?
require("../config.php"); 
     $link = mysql_connect(DB_HOST, SAM_DB_USER, SAM_PASSWORD);
    if(!$link) {
        die('Failed to connect to server: ' . mysql_error());
    }
    
    //Select database
    $db = mysql_select_db(SAM_DB_USER);
    if(!$db) {
        die("Unable to select database");
    }
$where = " WHERE (songtype='S') AND (status=0) ";
if (!isset($_GET['ltr'])) { $ltr = "0"; }
else { $ltr = $_GET['ltr']; }

//########## BUILD SEARCH STRING ################

if((isset($ltr)) && (!$ltr==""))
{
  $nextletter = chr(ord($ltr)+1);
  if($ltr=='0')
   $where .= " AND NOT((artist>='A') AND (artist<'ZZZZZZZZZZZ')) ";
  else
   {
    $where .= " AND ((artist>=:".$ltr.") AND (artist<:".$nextletter.")) ";
   }
}
else
{
	$ltr="";
}

//########## =================== ################ 

//Query
$qry = "SELECT * FROM songlist $where ORDER BY artist ASC, title ASC";
$res = mysql_query($qry);
$total = mysql_numrows($res);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>DJ Thunder</title>
<link href="../red.css" rel="stylesheet" type="text/css" />
</head>
<body text="#0000ff" link="#00a5e9" vlink="#00a5e9" alink="#00a5e9" leftmargin="2" topmargin="2" marginwidth="2" marginheight="0">
<center><h1>DJ Thunder</h1></center>
<table width=100% class='tableborder'><tr><td valign=top width=27%><div class='navigation'>Links</div><br>
<a href='index.php'>Home</a><br>
<a href='playlist.php'>Playlist</a><br>
<a href='http://rockinradio.net' target='blank'>Rockin Radio</a><br>
</td><td width=73%>
<a href='playlist.php?ltr=0'>0-9</a> |
<a href='playlist.php?ltr=a'>A</a> |
<a href='playlist.php?ltr=b'>B</a> |
<a href='playlist.php?ltr=c'>C</a> |
<a href='playlist.php?ltr=d'>D</a> |
<a href='playlist.php?ltr=e'>E</a> |
<a href='playlist.php?ltr=f'>F</a> |
<a href='playlist.php?ltr=g'>G</a> |
<a href='playlist.php?ltr=h'>H</a> |
<a href='playlist.php?ltr=i'>I</a> |
<a href='playlist.php?ltr=j'>J</a> |
<a href='playlist.php?ltr=k'>K</a> |
<a href='playlist.php?ltr=l'>L</a> |
<a href='playlist.php?ltr=m'>M</a> |
<a href='playlist.php?ltr=n'>N</a> |
<a href='playlist.php?ltr=o'>O</a> |
<a href='playlist.php?ltr=p'>P</a> |
<a href='playlist.php?ltr=q'>Q</a> |
<a href='playlist.php?ltr=r'>R</a> |
<a href='playlist.php?ltr=s'>S</a> |
<a href='playlist.php?ltr=t'>T</a> |
<a href='playlist.php?ltr=u'>U</a> |
<a href='playlist.php?ltr=v'>V</a> |
<a href='playlist.php?ltr=w'>W</a> |
<a href='playlist.php?ltr=x'>X</a> |
<a href='playlist.php?ltr=y'>Y</a> |
<a href='playlist.php?ltr=z'>Z</a><Br>
<table width=100% class='tableborder'>
<?
$count = 0;
while ($count < $total) {
?>
<tr><td><? echo mysql_result($res,$count,"artist"); ?></td><td><? echo mysql_result($res,$count,"title"); ?></td></tr>
<?
++$count;
}
echo "</table>";
?>
<a href='playlist.php?ltr=0'>0-9</a> |
<a href='playlist.php?ltr=a'>A</a> |
<a href='playlist.php?ltr=b'>B</a> |
<a href='playlist.php?ltr=c'>C</a> |
<a href='playlist.php?ltr=d'>D</a> |
<a href='playlist.php?ltr=e'>E</a> |
<a href='playlist.php?ltr=f'>F</a> |
<a href='playlist.php?ltr=g'>G</a> |
<a href='playlist.php?ltr=h'>H</a> |
<a href='playlist.php?ltr=i'>I</a> |
<a href='playlist.php?ltr=j'>J</a> |
<a href='playlist.php?ltr=k'>K</a> |
<a href='playlist.php?ltr=l'>L</a> |
<a href='playlist.php?ltr=m'>M</a> |
<a href='playlist.php?ltr=n'>N</a> |
<a href='playlist.php?ltr=o'>O</a> |
<a href='playlist.php?ltr=p'>P</a> |
<a href='playlist.php?ltr=q'>Q</a> |
<a href='playlist.php?ltr=r'>R</a> |
<a href='playlist.php?ltr=s'>S</a> |
<a href='playlist.php?ltr=t'>T</a> |
<a href='playlist.php?ltr=u'>U</a> |
<a href='playlist.php?ltr=v'>V</a> |
<a href='playlist.php?ltr=w'>W</a> |
<a href='playlist.php?ltr=x'>X</a> |
<a href='playlist.php?ltr=y'>Y</a> |
<a href='playlist.php?ltr=z'>Z</a>
</table>
</body>
</html>

RaythMistwalker · February 28, 2010

Ok update and found my error log:

PHP Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/pureclas/public_html/djthunder/playlist.php on line 39

Line 39:

$total = mysql_numrows($res);

jl5501 · February 28, 2010

Ok so your $res is not a valid result set, which means the query failed.

You need to show the value of $qry, and echo what mysql_error() returns, to fined the problem

RaythMistwalker · February 28, 2010

Query Failed: SELECT * FROM songlist WHERE (songtype='S') AND (status=0) AND ((artist>=:a) AND (artist<:b)) ORDER BY artist ASC, title ASC

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':a) AND (artist<:b)) ORDER BY artist ASC, title ASC' at line 1

This is now returned.

jl5501 · February 28, 2010

So you can see the error in your where clause.

This line of your code is what is wrong

$where .= " AND ((artist>=:".$ltr.") AND (artist<:".$nextletter.")) ";

It probably should be

$where .= " AND ((SUBSTRING(artist,0,1) >='".$ltr."') AND (SUBSTRING(artist,0,1) <'".$nextletter."')) ";


or something similar to that

RaythMistwalker · February 28, 2010

So you can see the error in your where clause.

This line of your code is what is wrong
$where .= " AND ((artist>=:".$ltr.") AND (artist<:".$nextletter.")) ";
It probably should be

$where .= " AND ((SUBSTRING(artist,0,1) >='".$ltr."') AND (SUBSTRING(artist,0,1) <'".$nextletter."')) ";
or something similar to that

Ok i did that and now im getting no error message in either file on in browser and just returns all the songs on 1 page

the page is http://pureclassacting.com/djthunder/playlist.php

jl5501 · February 28, 2010

ok, so when you have built all of that into $qry, can you echo $qry, to see what it is sending to the db

RaythMistwalker · February 28, 2010

isn't even adding the second part now.

SELECT * FROM songlist WHERE (songtype='S') AND (status=0) ORDER BY artist ASC, title ASC

Thats the query its sending so technically its doing what it says it is but not what im telling it to

jl5501 · February 28, 2010

ok, interesting, can you add an echo of $where in there

RaythMistwalker · February 28, 2010

ok, interesting, can you add an echo of $where in there

Query: SELECT * FROM songlist WHERE (songtype='S') AND (status=0) ORDER BY artist ASC, title ASC

$where: WHERE (songtype='S') AND (status=0)

jl5501 · February 28, 2010

This is not making a great deal of sense, but it could be my screw up with the code tags in my earlier post

$where .= " AND ((SUBSTRING(artist,0,1) >='".$ltr."') AND (SUBSTRING(artist,0,1) <'".$nextletter."')) ";

Is what I meant for you to add to the $where.

It should either work as required, or produce an error due to my typing

RaythMistwalker · February 28, 2010

K when im on page 0 it returns my above post.

On page A-Z it returns:

Query: SELECT * FROM songlist WHERE (songtype='S') AND (status=0) AND ((SUBSTRING(artist,0,1) >='a') AND (SUBSTRING(artist,0,1) <'b')) ORDER BY artist ASC, title ASC

$where: WHERE (songtype='S') AND (status=0) AND ((SUBSTRING(artist,0,1) >='a') AND (SUBSTRING(artist,0,1) <'b'))

But still isnt separating songs

jl5501 · February 28, 2010

do your titles begin with 'a' 'b' or 'A' 'B' ?

RaythMistwalker · February 28, 2010

All the artist's have Capitals

jl5501 · February 28, 2010

You will need to get capitals into your query then

make your

$ltr =  strtoupper($_GET['ltr']);

RaythMistwalker · February 28, 2010

still making no difference at all

jl5501 · February 28, 2010

Ok I got the query wrong, we have to use like on these names my fault

$where .= " AND artist like '".$ltr."%')";

RaythMistwalker · February 28, 2010

Ok that worked for letters but still if i goto page 0-9 (ltr=0) it comes up the whole list.

  if($ltr=='0') {
   $where .= " AND NOT((artist>='A') AND (artist<'ZZZZZZZZZZZ')) ";
  }

jl5501 · February 28, 2010

for that one, we change it slightly

$where .= " AND SUBSTRING(artist,1,1) BETWEEN 0 AND 9 ";

RaythMistwalker · February 28, 2010

now its not adding that part onto the query so is returning everything on the 0-9 page and correctly on A-Z pages

jl5501 · February 28, 2010

if that is a 0 zero then the if should be if($ltr == 0) not if($ltr == '0')

RaythMistwalker · March 1, 2010

  if ($ltr == 0) {
   $where .= " AND SUBSTRING(artist,1,1) BETWEEN 0 AND 9 ";
  }
  else
   {
$where .= " AND artist like '".$ltr."%'";   }
}

Still not doin it. With it like that it returns EVERY song on EVERY page where as with it as '0' it worked apart from the 0-9 page where it returned everything

jl5501 · March 1, 2010

I know you are only posting relevant bits of the code, but there seems to be an extra closing brace in the last snippet. Can you check that

RaythMistwalker · March 1, 2010

I know you are only posting relevant bits of the code, but there seems to be an extra closing brace in the last snippet. Can you check that

if((isset($ltr)) && (!$ltr==""))
{
  $nextletter = chr(ord($ltr)+1);
  if ($ltr == 0) {
   $where .= " AND SUBSTRING(artist,1,1) BETWEEN 0 AND 9 ";
  }
  else
   {
$where .= " AND artist like '".$ltr."%'";   }
}

EDIT: Tried with # instead of 0 and that just brings the same result and my $where and $qry echo's are telling me that it isn't adding "AND SUBSTRING(artist,1,1) BETWEEN 0 AND 9 " to the where part

jl5501 · March 1, 2010

Ok so now it is telling us that in the case of the 0 it was not getting past the original if, so added nothing to the where clause.

So, double check what you are sending as $_GET['ltr'] from the form and try the if like this

It might also be useful to echo $ltr

if(isset($ltr) && $ltr != "")
{
$nextletter = chr(ord($ltr)+1);
if ($ltr == 0) 
{
   		$where .= " AND SUBSTRING(artist,1,1) BETWEEN 0 AND 9 ";
}
else
{
	$where .= " AND artist like '".$ltr."%'";   
}
}

Sign In

Playlist Thingy

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information