[thelist] Newbie PHP request..

[Matt Warden]

Hi Trish,

I'll give it a shot here, but if you need more help, feel free to
contact me off list. See below:

On Fri, 11 Mar 2005 10:41:04 -0500, VanBuskirk, Patricia
<pvanbuskirk at otc.fsu.edu> wrote:
> I've created a database for a band I'm doing a website for.  It is to list their songlist.  I've been able so far to create a page that pulls the info in and sorts it by Song Title (http://www.webmisstrish.com/musiclist/music.php)
> What I'd like have are links to sort by Title, Artist or Genre.

There are a number of ways to do this. The easiest is probably to make
the 'title', 'artist', 'genre' text links of the form:


<?php $thispage = $_SERVER['PHP_SELF']; ?>
<a href="<?php echo $thispage; ?>?sortby=title">Title</a>
<a href="<?php echo $thispage; ?>?sortby=artist">Artist</a>
<a href="<?php echo $thispage; ?>?sortby=genre">Genre</a>

Now, when you are querying your DB, you are probably using something like this:

$sql = 'SELECT title, artist, genre, clip FROM songs ORDER BY title ASC';

We need to change it to something like this:

$sql = 'SELECT title, artist, genre, clip FROM songs ORDER BY ';
$sortby = $_GET['sortby'];
if ($sortby == 'artist')
{
    $sql = $sql . 'artist ASC';
}
elseif ($sortby == 'genre')
{
    $sql = $sql . 'genre ASC';
}
else
{
    $sql = $sql . 'title ASC';
} // end if

So, at this point, $sql will contain one of the following:

SELECT title, artist, genre, clip FROM songs ORDER BY title ASC
SELECT title, artist, genre, clip FROM songs ORDER BY artist ASC
SELECT title, artist, genre, clip FROM songs ORDER BY genre ASC

based on the value of 'sortby' in the query string. You might ask "Why
not just put whatever is in the querystring into the SQL in the
correct place?" You could do this, but you have to be careful, as you
are opening yourself up to possible SQL injection attack (google it up
if you're interested, or ask me). I generally stick to methods like
the above when I can.

> I'd also like for the "listen" link to only show up if there is a link in the field (not null).
> 
> Can anyone assist me with these tasks?

Let's say you currently have something like the following:

while ($row = mysql_fetch_assoc($result))
{
   // .. other code here ...
   echo $row['clip'];
   // .. other code ere ...
} // end while

We'll need to change it to something like this:

while ($row = mysql_fetch_assoc($result))
{
   // .. other code here ...
   $clip = $row['clip'];
   if ($clip != NULL && $clip != '')
   {
       echo $clip;
   } // end if
   // .. other code ere ...
} // end while

If you have any questions about my suggestions here, or just what a
piece of code is doing, just ask.

Good luck, Trish.

-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.