id from a table based on another variable through an URL

[emke]

I want to send the $band variable through a URL and based on that variable to get the band id from a mysql table. I can't have $id in my URL.

This is the code that I have:

  /* This function pulls out the album names of corresponding band */
       function get_albums()
       {
        global  $aid,$album_name,$band,$image,$purchase;
        connect_db();
                $id = ("SELECT id FROM bands WHERE band='$band'");
        $result = mysql_query("select albums.album_id,albums.album_name,albums.aid,albums.image,albums.purchase,bands.id
        from albums as albums,bands as bands where albums.album_id= bands.id
        and albums.album_id=$id order by album_id")
        or die (mysql_error());
        abc_links();
                      echo "</td>";
                      echo "</tr>";
                      echo "<tr>";
                      echo "<td>";
                      echo "<table cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" border=\"0\" class=\"content\">";
                      echo "<tr>";
                      echo "<td>";
                      echo "<table cellpadding=\"0\" cellspacing=\"0\" width=\"650\" border=\"0\" class=\"smallline\">";
                      echo "<tr><td valign=\"top\" width=\"500\"><table cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" border=\"0\" class=\"smallline\">";       
                echo stripslashes("<tr><td><center><h1>$band albums</h1></center></td></tr>");
        while ($row = mysql_fetch_array($result))
        {
        $aid=$row['aid'];
        $album_name=$row['album_name']; 

I get the following error:

You have an error in your SQL syntax near 'SELECT id FROM bands WHERE band='Eminem' order by album_id' at line 3

[anode]

                $id = ("SELECT id FROM bands WHERE band='$band'"); 

should be

$sql = "SELECT id FROM bands WHERE band='$band'";
$result = mysql_query($sql);
$id = mysql_result($result, 0 ); 

Your confusion may lie in the fact that MySQL doesn't support subqueries(well, the very latest bleeding edge releases do, but I'm assuming you're not running them.) In any case, it would still be an error as your parentheses are outside the quotes.

[emke]

Thanks, that's exactly what I needed.

What if I need to get values for two variables from two different tables?

        $sql = "SELECT id,aid FROM bands,albums WHERE band='$band' and album='$album_name'";
        $result = mysql_query($sql);
        $id = mysql_result($result, 0);
        $aid = mysql_result($result, 0); 

Doesn't work

        $sql = "SELECT id FROM bands WHERE band='$band'";
        $result = mysql_query($sql);
        $id = mysql_result($result, 0);
$sql = "SELECT aid FROM albums WHERE album='$album_name'";
        $result = mysql_query($sql);
        $aid = mysql_result($result, 0); 

Doesn't work either

[emke]

$sql = "SELECT id, aid FROM bands, albums WHERE band='$band' and album='$album_name'";
        $result = mysql_query($sql);
        $id and $aid= mysql_result($result, 0); 

So I changed the code a little bit. Now I get the following error:

You have an error in your SQL syntax near 'order by id' at line 3

There seems to be some kind of conflict as I right after this query have:

    $result = mysql_query("select songs.song_id,songs.song_name,song_lyric,albums.aid
    from songs as songs,albums as albums where songs.song_id = albums.aid
    and songs.song_id = $aid order by id") 

Anyone can help?

[redgoals]

what table is the "id" attribute in the order clause referring to in your last query?

if its ok then do some debuggin...

and songs.song_id = $aid order by id") or die(mysql_error());

[emke]

Here are my three tables.

# Table structure for table `albums`

TABLE albums (
aid int(11) NOT NULL auto_increment,
album_name varchar(100) NOT NULL default '',
album_id int(3) NOT NULL default '0',
image varchar(100) NOT NULL default '',
purchase varchar(255) NOT NULL default '',
PRIMARY KEY (aid)
) TYPE=MyISAM;

# Table structure for table `bands`

TABLE bands (
id int(3) unsigned NOT NULL auto_increment,
band varchar(50) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY band (band)
) TYPE=MyISAM;

# Table structure for table `songs`

TABLE songs (
id int(3) unsigned NOT NULL auto_increment,
song_name varchar(100) NOT NULL default '',
song_lyric text NOT NULL,
song_id int(3) NOT NULL default '0',
PRIMARY KEY (id),
KEY song_name (song_name)
) TYPE=MyISAM;

It's reffering to the songs id. If I remove the code snippet "order by id" I get: You have an error in your SQL syntax near '' at line 3

[redgoals]

edit...

then why not reference the id from the songs table as songs.id?

what happens when you debug?

[emke]

I still get the same error if I use songs.id and also song_id

        $sql = "SELECT id, [B]aid[/B] FROM bands, albums WHERE band='$band' and album='$album_name'";
        $result = mysql_query($sql);
        $id and [B]$aid[/B]= mysql_result($result, 0);
    $result = mysql_query("select songs.song_id,songs.song_name,song_lyric,albums.aid
    from songs as songs,albums as albums where songs.song_id = albums.aid
    and [B]songs.song_id = $aid [/B] order by songs.id")
    or die (mysql_error()); 

The thing is that I'm kinda confused about this entire query here and don't really know what to do. I don't think that the problem is about the songs.id I think that the two $aid that I've put in bold above are in conflict and don't know how to solve it in a really god way.

[redgoals]

if i understand you correctly, the following should work...

$result = mysql_query("select songs.song_id,songs.song_name,song_lyric,albums.aid,
    from songs LEFT JOIN album ON songs.song_id = albums.aid AND songs.song_id = '$aid'
    order by songs.songs_id"); 

[emke]

Now I get: Parse error: parse error in /home/public_html/files/functions.php on line 171

[redgoals]

...

$result = mysql_query("SELECT songs.song_id,songs.song_name,song_lyric,albums.aid 
    FROM songs LEFT JOIN album ON songs.song_id = albums.aid AND songs.song_id = '$aid'
    ORDER BY songs.songs_id"); 

[emke]

Call to undefined function: get_songs() in /home/public_html/index.php on line 17

[emke]

OK, when I changed the code to:

        $sql = "SELECT id, aid FROM bands, albums WHERE band='$band' and album='$album_name'";
        $result = mysql_query($sql);
        $id and $aid = mysql_result($result, 0);
    $result = mysql_query("select songs.song_id,songs.song_name,songs.song_lyric,albums.aid
    from songs as songs,albums as albums where songs.song_id = albums.aid
    and songs.song_id = '$aid' order by songs.id") 

Now I can see the title of the album ($song_name), it just needs to list the songs under the album.

I use this code to list the songs:

     $num=mysql_num_rows($result);
     for($i=1;$i<=$num;)
     {
     while ($row = mysql_fetch_array($result))
     {
     $song_name=$row['song_name'];
         $song_lyric=$row['song_lyric'];
     echo stripslashes("<tr><td width=\"6%\" align=\"center\">$i.</td><td><a href=\"$PHP_SELF/lyrics/$band/$album_name/song_name/\">$song_name</a></td></tr>");
     $i++;
     }
     } 

Can anyone see if I'm missing something? I'm so close now.

[redgoals]

looking at your tables schema you have no way of linking songs.song_name to the album the song is from. If you add a column called 'aid' to the 'songs' table then you can do this...

$result = mysql_query("select songs.song_id,songs.song_name,songs.song_lyric,albums.aid
    from songs as songs,albums as albums where songs.song_id = albums.aid
    and songs.song_id = '$aid' GROUP BY songs.aid order by songs.id") 

[emke]

I used this snippet of code and got all the lyrics from the database to show, now I just need to find a way to show lyrics from just the specific band that I've choosen.

        $result = mysql_query("SELECT songs.song_id,songs.song_name,songs.song_lyric,albums.aid 
    FROM songs LEFT JOIN albums ON songs.song_id = albums.aid AND songs.song_id = '$aid'
    ORDER BY songs.id") 

I tried using the code above but I don't get any songs at all to show.

[redgoals]

echo out the contents of $aid before the code you last posted and see if that value does match records in both the songs and albums tables.

if all seems well than are you sure this...

songs.song_id = albums.aid

does match song_id in songs and aid in albums?

[emke]

Originally posted by redgoals
echo out the contents of $aid before the code you last posted and see if that value does match records in both the songs and albums tables.

When I echo $aid nothing shows up. That must be the problem.

Could it be a problem with this code?

        $sql = "SELECT id, aid FROM bands, albums WHERE band='$band' and album_name='$album_name'";
        $result = mysql_query($sql);
        $id and $aid = mysql_result($result, 0); 

[redgoals]

give this a bash

$sql = "SELECT id, aid FROM bands, albums WHERE band='$band' and album_name='$album_name'";
while ( $row = mysql_fetch_array($result) ) {
$id = $row["id"];
$aid = $row["aid"]; 

[emke]

Parse error: parse error in /home/public_html/files/functions.php on line 167

     function get_songs()
      {
       global  $aid,$album_name,$band,$id,$song_name,$image,$purchase;
    connect_db();

        $sql = "SELECT id, aid FROM bands, albums WHERE band='$band' and album_name='$album_name'";
        while ($row = mysql_fetch_array($result)) {
        $id = $row['id'];
        $aid = $row['aid'];

        $result = mysql_query("select songs.song_id,songs.song_name,songs.song_lyric,albums.aid
    from songs as songs,albums as albums where songs.song_id = albums.aid
    and songs.song_id = '$aid' GROUP BY songs.song_id order by songs.id") 

[redgoals]

$result = mysql_query("select songs.song_id,songs.song_name,songs.song_lyric,albums.aid
    from songs as songs,albums as albums where songs.song_id = albums.aid
    and songs.song_id = '$aid' GROUP BY songs.song_id order by songs.id"); 

Edit: i can see your not sure about which query to use, you keep posting different queries, i make one for you and then you go back to your old one...?

[emke]

As it's a parse error it's easy to try any of the queries. I have three that I've been messing with that're commented out but I get a parse error so it must be something with PHP.

btw I really appreciate you helping me. I can't believe how patient you're. THANKS!

[redgoals]

function get_songs()
{
global  $aid,$album_name,$band,$id,$song_name,$image,$purchase;
connect_db();
// } IS THERE SUPPOSED TO BE A CLOSING PARENTHESIS HERE ?

$sql = "SELECT id, aid FROM bands, albums WHERE band='$band' and album_name='$album_name'";
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$aid = $row['aid'];
}
$result = mysql_query("SELECT songs.song_id,songs.song_name,songs.song_lyric,albums.aid 
    FROM songs LEFT JOIN albums ON songs.song_id = albums.aid AND songs.song_id = '$aid'
    ORDER BY songs.id") 

edit: thats my time up im startin to squint, must get some sleep, bye now.

[emke]

I got this working by deleting everything and trying from scratch. This is the code I used:

function get_songs()
{
global  $aid,$album_name,$band,$id,$song_name;
connect_db();

        $sql = "SELECT aid FROM albums WHERE album_name='$album_name'";
        $result = mysql_query($sql);
        $aid = mysql_result($result, 0);
        $result = mysql_query("select songs.id,songs.song_name,songs.song_id,albums.aid
    from songs as songs,albums as albums where songs.song_id = '$aid'
    order by songs.id")
    or die (mysql_error());
        abc_links();
                      echo "</td>";
                      echo "</tr>";
                      echo "<tr>";
                      echo "<td>";
                      echo "<table cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" border=\"0\" class=\"content\">";
                      echo "<tr>";
                      echo "<td>";
                      echo "<table cellpadding=\"0\" cellspacing=\"0\" width=\"650\" border=\"0\" class=\"smallline\">";
                      echo "<tr><td valign=\"top\" width=\"500\"><table cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" border=\"0\" class=\"smallline\">";       
    echo stripslashes("<tr><td colspan=\"2\"><center><h1>Songs of $album_name</h1></center></center></td></tr>");
     $num=mysql_num_rows($result);
     for($i=1;$i<=$num;)
     {
     while ($row = mysql_fetch_array($result))
     {
     $song_name=$row['song_name'];
     echo stripslashes("<tr><td width=\"6%\" align=\"center\">$i.</td><td><a href=\"$PHP_SELF/lyrics/$band/$album_name/$song_name/\">$song_name</a></td></tr>");
     $i++;
     }
     }
} 

The only problem I'm having is that now songs are displayed seven times. Am I doing something wrong?