Question about query strings and database

dackerman

Hello,

I'm sorry if you can't understand what I'm trying to do, but it's kind of hard to explain something you have no idea of doing. :p

Anways...

I currently have a database of ARTISTS and SONGS, and I can sort them pretty easily, either by SONG, ARTIST, or you can even sort by letter. See here:

http://www.splix.net/music.php

Ok, now what I want to do is, say if you click on the letter G, all the Artists with the letter G are displayed. Right under that there is a Sort by: area, and if the user were to click on say SONG, it would sort the Artists beggining with the letter G by song.

I kind of confused myself there, but I have seen sites that have done this before and it kind of looks like this:

http://www.example.com/music.php?artist=a&sort=song

How would I do this?

I am currently calling my database and doing all the sorting like this:

<?php 
$db = mysql_connect("localhost", "splix", "PASS"); 
mysql_select_db("splix",$db); 

if($_GET['artist']) { 
	$artist = $_GET['artist']; 
	$query = "SELECT * FROM `mp3` WHERE artist LIKE '$artist%' ORDER BY artist ASC";

}
else if ($_GET['sort'] == 'song') {
		$song = $_GET['song']; 
		$query = "SELECT * FROM `mp3` ORDER BY song ASC";
}
else if ($_GET['sort'] == 'artist') {
		$artist = $_GET['artist']; 
		$query = "SELECT * FROM `mp3` ORDER BY artist ASC";
}
else { 
	$query = "SELECT * FROM mp3 ORDER BY artist ASC"; 
} 

$result = mysql_query($query); 
$numofrows = mysql_num_rows($result); 

echo "<TABLE width='100%'>"; 
echo "<TR><TD width='50%'><b><span class='main'>Artist</b></TD><TD width='50%'><b><span class='main'>Song</b></TD></TR><br><tr></tr>"; 
for($i = 0; $i < $numofrows; $i++) { 
    $row = mysql_fetch_array($result);
    if($i % 2) {
        echo "<TR bgcolor=DADADA>"; 
    } else {
        echo "<TR bgcolor=F0F0F0>"; 
    } 
    echo "<TD class='main'>".$row['artist']."</TD><TD class='main'>".$row['song']."</TD>"; 
    echo "</TR>"; 
} 
echo "</TABLE>"; 
?>

For the individual letter links I have this:

<a href="music.php?artist=a"><? if ($artist == 'a')
																					echo "<b>A</b>";
																			else echo "A"; ?></a>

And for the Sort by links it's the same idea:

<? if ($sort == 'song')
																					echo "<b>SONG</b>";
																			else echo "SONG"; ?></a>

Now I know this probably isn't the most efficient way of doing anything, but it works for now.

So basically I am trying to make my site more dynamic, and I think this is the last thing I am going to do today if I figure it out.

Any ideas?

dackerman

No one knows? 😕

leatherback

Sorry but..

http://www.phpbuilder.com/board/showthread.php?threadid=10275087

leatherback

Anyway. On the more usefull reply now:

What you could consider: Once you have sorted by song name, you can also sort by artist. So combine the queries:

$query = "SELECT * FROM `mp3` ":


if($_GET['artist']) 
  { 
    $artist = $_GET['artist']; 
    $query .= " WHERE artist LIKE '$artist%'";
  }

if(! emtpy($_GET['sort']) ) 
  {
  $query.= " ORDER BY $_GET['sort'] ASC";
  }
else  

  {
       $query .= " ORDER BY artist ASC";
  }

dackerman

Thanks for the reply...sorry for that, I'm just getting really annoyed. 🙁

Anyways, here is my code now:

<?php 
$db = mysql_connect("localhost", "splix", "delldell"); 
mysql_select_db("splix",$db); 

$query = "SELECT * FROM mp3";


if ($_GET['artist'])  

  {  

    $artist = $_GET['artist'];  

    $query .= " WHERE artist LIKE '$artist%'"; 
  } 

if (!emtpy($_GET['sort']) )  

  { 
  $query .= " ORDER BY $_GET['sort'] ASC"; 
  } 
else   

 { 
  $query .= " ORDER BY artist ASC"; 
 }

$result = mysql_query($query); 
$numofrows = mysql_num_rows($result); 

echo "<TABLE width='100%'>"; 
echo "<TR><TD width='50%'><b><span class='main'>Artist</b></TD><TD width='50%'><b><span class='main'>Song</b></TD></TR><br><tr></tr>"; 
for($i = 0; $i < $numofrows; $i++) { 
    $row = mysql_fetch_array($result);
    if($i % 2) {
        echo "<TR bgcolor=DADADA>"; 
    } else {
        echo "<TR bgcolor=F0F0F0>"; 
    } 
    echo "<TD class='main'>".$row['artist']."</TD><TD class='main'>".$row['song']."</TD>"; 
    echo "</TR>"; 
} 
echo "</TABLE>"; 
?>

But I am getting the error:

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /home/splix/www/music.php on line 328

GRrrrr

dackerman

I'm also confused about this line:

else if (!emtpy($_GET['sort']))

leatherback

Hi,

1-> You are getting annoyed? Please remember: Everybody ni this forum is just doing it to help people. If you do no tget the reply you are expecting, in the time you expect it at.. Too bad. It is a forum to help youi learn. So while the question is out, you could work on figuring out what you need to know by eg reading a manual.

2-> An error message is very nice to have. But without knowing which line is lin 328 there is nothing we can do

3-> Th line

else if (!emtpy($_GET['sort']))

does not occur anywhere in the code you posted. It seems to be a part of an if statement, where the second half checks whether the variable sort has been defined in the $_GET array. If not, it will perform an action.

dackerman

Line 328:

  $query .= " ORDER BY $_GET['sort'] ASC";

And sorry that other line should have actually been:

if (!emtpy($_GET['sort']) )

Sorry