Member Avatar for slrobinson1983

hello all. heres my problem. i have a mysql database with 2 tables. the first table is called songs which contains records for all the songs on my site. its columns are song id and song title. the second table is called ratedsongs. its columns are song id (id of the song rated) and member id (id of the member who rated it). i can easily create a table with a list of songs rated by a user with this code...

$ratedsongs = mysql_query ("SELECT * FROM ratedsongs WHERE member_id = '$memberid'");

...what i'm having trouble with is getting the code right that'll display a list of the songs NOT rated by a specific user. i've tried...

$ratedsongs = mysql_query ("SELECT song_id FROM ratedsongs WHERE member_id = '$memberid'");

$unratedsongs = mysql_query ("SELECT * FROM songs WHERE song_id !='$ratedsongs'");

... but that dosent seem to work. its returning a list of all songs. please point me in the right direction. thanks in advance

  • 2 Contributors
  • 3 Replies
  • 75 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

For user = 4:

SELECT * FROM songs WHERE song_id NOT IN(SELECT DISTINCT songs.song_id FROM songs INNER JOIN ratedsongs ON songs.song_id = ratedsongs.song_id WHERE ratedsongs.user_id = 4)

That's something that came off the top of my head. Doubtless there are better solutions.

Member Avatar for slrobinson1983

thanks ardav. i tweaked your suggestion a bit and came up with this...

"SELECT * FROM songs WHERE song_id NOT IN(SELECT song_id FROM ratedsongs WHERE member_id='$memberid')"

...seems to be working fine now.

Member Avatar for diafol

Yes your tweak took out a lot of extraneous nonsense of mine. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.