SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot HardinComp's Avatar
    Join Date
    May 2003
    Location
    Boston, MA
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I get a value from table A to look up in table B?

    I run a site for a local radio program and we are finally making the switch from hardcoded playlist files to a MySQL database. (Yea! ) I have two tables (which due to server restrictions are not linked, alas), one (playlistdates) of which are the airdates for the show and the other is the actual playlists (playlists). I want to be able to have our main page grab PlaylistID field for the latest airdate from playlistdates and automatically display the current week's playlist from playlist. I have no clue how to code this and I'm hoping someone can help me.

    Also, I want to set up a drop down menu on another page to be able to select a particular date's playlist. I know I can use a SELECT query based on the airdate's PlaylistID field, but what's the best way to code this?
    J.M. Hardin
    My blog
    Registered Ubuntu User #10980

  2. #2
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    You could probably use sub-selects, but I don't know if MySQL supports them.

  3. #3
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MYSQL 4.x now has support for Sub - Selects. Never used them since I couldn't get 4.x to install on my box

    Umm.... That and PHP4.3.x.

    My PC must be jinxed huh ?

  4. #4
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    I'm running MySQL 4.1.0. It was pretty tricky to get installed, double-clicking that setup file took me like 10 mins

    Nar seriously, I didn't have any trouble installing it, did you try using the setup file or installing it as a service?

  5. #5
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm... As a service much like mySQL 3.23.x

    Doesn't matter really since I'm thinking of defaulting to PostgreSQL now once I can be bothered to get around to installing it again.

    More interested in using Oracle as well now since mySQL has some issues still yes with the licence ?

  6. #6
    SitePoint Zealot HardinComp's Avatar
    Join Date
    May 2003
    Location
    Boston, MA
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DJ P@CkMaN
    You could probably use sub-selects, but I don't know if MySQL supports them.
    Nope, it doesn't. So does anyone have an idea on how I could accomplish my task?
    J.M. Hardin
    My blog
    Registered Ubuntu User #10980

  7. #7
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What ? mySQL 4.x does not support Sub Selects ?

    Umm... Sure I read this version does from the mySQL site it's self...

  8. #8
    SitePoint Zealot HardinComp's Avatar
    Join Date
    May 2003
    Location
    Boston, MA
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, ok. I still have no clue how to use it. I'm afraid I'm a MySQL newbie and I haven't been able to get SP's book ... yet.
    J.M. Hardin
    My blog
    Registered Ubuntu User #10980

  9. #9
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe MySQL does support sub select from the latest build though.

    And Jeanne, it would be a great help if you could post the table structure/schema.

  10. #10
    SitePoint Zealot HardinComp's Avatar
    Join Date
    May 2003
    Location
    Boston, MA
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the columns for the Playlist table:
    Code:
    +----------------+--------------+------+-----+------------+----------------+
    | Field          | Type         | Null | Key | Default    | Extra          |
    +----------------+--------------+------+-----+------------+----------------+
    | PlaylistID     | int(3)       |      | PRI | NULL       | auto_increment |
    | PlaylistDateID | tinyint(3)   |      | MUL | 11         |                |
    | Date           | date         | YES  |     | 2003-09-14 |                |
    | SongNo         | tinyint(3)   |      |     | 0          |                |
    | ArtistName     | varchar(50)  |      |     |            |                |
    | SongTitle      | varchar(100) |      |     |            |                |
    | AlbumTitle     | varchar(100) | YES  |     | NULL       |                |
    +----------------+--------------+------+-----+------------+----------------+
    And here's the columns for PlaylistDates:

    Code:
    +----------------+--------------+------+-----+-------------------------------------------------------------------------+----------------+
    | Field          | Type         | Null | Key | Default                                       | Extra          |
    +----------------+--------------+------+-----+-------------------------------------------------------------------------+----------------+
    | PlaylistDateID | tinyint(3)   |      | MUL | NULL                                       | auto_increment |
    | Date           | date         |      |     | 0000-00-00                                       |                |
    | DateDisplay    | varchar(100) |      |     |                                       |                |
    | Comment        | varchar(255) | YES  |     | This is a partial playlist and only includes the songs we can identify. |                |
    +----------------+--------------+------+-----+-------------------------------------------------------------------------+----------------+
    Sorry about that not lining up. I did a cut & paste from my Command Prompt window and tried to make it look as good as possible, but I could only do so much. What I want o be able to do is to get the PlaylistDateID (also the max records no.) for the PlaylistDates table and use it to select which records match in the PlaylistDateID field in the Playlist table.
    J.M. Hardin
    My blog
    Registered Ubuntu User #10980


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
  •