SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    id from a table based on another variable through an URL

    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:

    PHP Code:
      /* 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
    Last edited by emke; Feb 3, 2003 at 11:11.

  2. #2
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
                    $id = ("SELECT id FROM bands WHERE band='$band'"); 
    should be
    PHP Code:
    $sql "SELECT id FROM bands WHERE band='$band'";
    $result mysql_query($sql);
    $id mysql_result($result); 
    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.

  3. #3
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that's exactly what I needed.

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

    PHP Code:
            $sql "SELECT id,aid FROM bands,albums WHERE band='$band' and album='$album_name'";
            
    $result mysql_query($sql);
            
    $id mysql_result($result0);
            
    $aid mysql_result($result0); 
    Doesn't work

    PHP Code:
            $sql "SELECT id FROM bands WHERE band='$band'";
            
    $result mysql_query($sql);
            
    $id mysql_result($result0);
    $sql "SELECT aid FROM albums WHERE album='$album_name'";
            
    $result mysql_query($sql);
            
    $aid mysql_result($result0); 
    Doesn't work either
    Last edited by emke; Feb 3, 2003 at 12:08.

  4. #4
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql "SELECT id, aid FROM bands, albums WHERE band='$band' and album='$album_name'";
            
    $result mysql_query($sql);
            
    $id and $aidmysql_result($result0); 
    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:

    PHP Code:
        $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?

  5. #5
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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());
    http://redgoals.com ... my site ... nuff said

  6. #6
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by emke; Feb 3, 2003 at 13:21.

  7. #7
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    edit...

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

    what happens when you debug?
    http://redgoals.com ... my site ... nuff said

  8. #8
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still get the same error if I use songs.id and also song_id


    PHP Code:
            $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($result0);
        
    $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.

  9. #9
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if i understand you correctly, the following should work...

    PHP Code:
    $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"
    ); 
    http://redgoals.com ... my site ... nuff said

  10. #10
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I get: Parse error: parse error in /home/public_html/files/functions.php on line 171

  11. #11
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...

    PHP Code:

    $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"
    ); 
    http://redgoals.com ... my site ... nuff said

  12. #12
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Call to undefined function: get_songs() in /home/public_html/index.php on line 17

  13. #13
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, when I changed the code to:

    PHP Code:
            $sql "SELECT id, aid FROM bands, albums WHERE band='$band' and album='$album_name'";
            
    $result mysql_query($sql);
            
    $id and $aid mysql_result($result0);
        
    $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:

    PHP Code:
         $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.
    Last edited by emke; Feb 3, 2003 at 17:03.

  14. #14
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...


    PHP Code:
    $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"
    http://redgoals.com ... my site ... nuff said

  15. #15
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
            $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.

  16. #16
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    http://redgoals.com ... my site ... nuff said

  17. #17
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    PHP 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($result0); 

  18. #18
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    give this a bash

    PHP Code:
    $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"]; 
    http://redgoals.com ... my site ... nuff said

  19. #19
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Parse error: parse error in /home/public_html/files/functions.php on line 167

    PHP Code:
         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"

  20. #20
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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...?
    Last edited by redgoals; Feb 4, 2003 at 16:50.
    http://redgoals.com ... my site ... nuff said

  21. #21
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  22. #22
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    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.
    Last edited by redgoals; Feb 4, 2003 at 17:07.
    http://redgoals.com ... my site ... nuff said

  23. #23
    SitePoint Enthusiast emke's Avatar
    Join Date
    Jun 2000
    Location
    somewhere
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got this working by deleting everything and trying from scratch. This is the code I used:

    PHP Code:
    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($result0);
            
    $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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •