Selecting from 2 tables at the same time

soviet · #1 07-Nov-2003, 15:30

I am new to using MySQL and I am trying to switch my site over to make it more dynamic. After reading up on databases and Normalizing them I have set it up so that there are 2 tables:
one called "album"
the other "artists"

I have an id in the Albums table called artID which is a reference [foreign key?] to the Artists table.

How should I set my select statement up so that I can get the info from a particular row from the Albums table which also has a link to the Artists table as well?

Here's the current SELECT statement:
$query="SELECT * FROM album WHERE albID = $albID";
$result=mysql_query($query) or die(mysql_error());

I then pass the albID through a query string in the URL, I am hoping to pass the artID in their as well, but that's where I get lost as to how to pull that info out of the other table....

any info is much appreciated....

thanks

misunderstood · #2 07-Nov-2003, 16:15

You could try something like:

PHP Code:


$result=mysql_query("SELECT * FROM album, art WHERE album.albid=art.artid ORDER BY artid");

Hope that helps point you in the right direction. If not wait for JdS

JdS · #3 07-Nov-2003, 23:15

Hello soviet,

If you have read and understood 'normalizing' a database, you should have used at least 3 tables in this situation. ONE artist to MANY albums.

Code:

TABLE: ARTISTS
artist_id  | artist
-----------+--------
 1         | Garth
 2         | Jay
 3         | Rob

TABLE: ALBUMS
album_id  | album
----------+---------
 1        | National Anthems of the World
 2        | Nursery Rhymes for Geeks
 3        | Coding Blues
 4        | Yet Another Bulletin Song
 5        | The Dog Ate My Script!

TABLE: ART2ALB
artist_id   | album_id
------------+---------
 1          | 3
 1          | 4
 1          | 5
 2          | 1
 3          | 2

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Display Size and maybe download time	BobbyDouglas	MySQL / PHP Forum	5	08-Nov-2003 00:18
finding time efficiency of STL	goathens	CPP / C++ Forum	1	28-Sep-2003 17:41
Need a script for selecting random tabels and from those tabels selecting random ques	mlt	MySQL / PHP Forum	2	12-Sep-2003 09:01
ambiguity in time field	zuzupus	MySQL / PHP Forum	9	28-Jul-2003 08:40
time Problem	zuzupus	MySQL / PHP Forum	9	24-Jul-2003 07:02