Page 1 of 1

SQL Database Where Statement Help Please

Posted: Sun Oct 25, 2009 4:06 am
by punkrock77
Hi all, I have three tables
Artists. Fields = artist id, artist_name
Albums. Fields = album_id, album_title etc
Songs. Fields = song_title, album_id, artist_id etc

I have managed to get a page listing all albums (creating recordsets), with a link from Albums.album_ID taking me to another page which displays data from Albums and Songs associated with field album ID.
I would like to be able to add data from the Artists table (ie the Artists.artist_name which is linked to the Songs.artist_id) on the linked page. as well but I don't know how to get this. I am using dreamweaver and here is the coding I have used;

SELECT albums.album_id, albums.album_title, albums.album_artist, albums.album_tracks, albums.album_year, songs.song_title, songs.song_track, artists.artist_name
FROM albums, songs, artists
WHERE (albums.album_id = songs.song_album_id) AND (albums.album_id = CD)

and here's the variable
NAME: CD TYPE:Integer VALUE:1 RUNTIME:$_GET["album_id"]

I hope I've explained this OK, I'm new to sql, php so any help or advice would be greatly appreciated. Thanks

Re: SQL Database Where Statement Help Please

Posted: Sun Oct 25, 2009 8:52 am
by midohioit
i dont understand what your trying to do. are you wanting to display data on your page from the database where the records are appriciate or are you trying to insert new data into another table? another thing is it would be helpful to include your code inside of the code tags: "" by using the code bottom above. it always help to see the bigger picture as well

Re: SQL Database Where Statement Help Please

Posted: Sun Oct 25, 2009 12:11 pm
by punkrock77
OK I'll try and explain;
TABLE=albums
Fields=album_id, album_artist, album_title
TABLE=songs
FIELDS=song_title, song_album_id, song_artrist_id
TABLE=artists
FIELDS=artist_id, artist_name
I have a page built in dreamweaver that displays all my albums displaying three field from albums table(album_list.php) from this I click on the album number (albums.album_id) and this takes me to another page (album_detail.php). On this page I would like to display data (not insert or add) from all three tables, I have managed to get it from two tables to include songs from songs.song_title. I believe this is done by the sql

Code: Select all

SELECT albums.album_id, albums.album_title, albums.album_artist, songs.song_title, songs.song_track, artists.artist_name, songs.song_artist_id
FROM albums, songs, artists
WHERE (albums.album_id = songs.song_album_id) AND (albums.album_id = CD) AND (songs.song_artist_id = artists.artist_id)
and the VARIABLES

Code: Select all

$_GET["album_id"]

In the table songs is song_artist_id but this is only 4 varchar field so I need to display from artists table artists_name this would be linked via the common artist_id and also I pressume album_id fields.
Because its using recordsets in DW I think I need to add something to the VARIABLES and maybe another WHERE SQL but not sure what to add. It works in DW test mode but not in Live mode.
Thanks for looking, cheers anyway but I will probably give up in the end :roll:

Re: SQL Database Where Statement Help Please

Posted: Sun Oct 25, 2009 2:41 pm
by midohioit
you still only posted a few lines of code and not everything.

one thing i would do first is this if this is a text sting:

Code: Select all

 
albums.album_id = [color=#FF0000][b]'[/b][/color]CD[color=#FF0000][b]'[/b][/color]