Re: [PHP-DB] properly a simple question

[Miles Thompson]

Barry,

You're 3/4 of the way there.

You understand how data is normalized, and you have the proper keys so that 
you can link your tables in a multi-table join. For specific syntax you 
should check the MySQL manual, but what you want is roughly this ..and I'll 
make up some field names. (Because I'm too lazy to check back to the msg 
which contained the table structure.)

select
  table1.artist as artist,
  table3.image as image,
  table2.song as song
from
  table1, table2, table3
where
  table3.art_id = table2.art_id and
  table2.art_id = table1.art_id and
  table1.art_id = $whch_artist?

If there are 20 songs this will return 20 rows. In your PHP code you will 
have to weed out the duplicate fields as you loop though the returned 
record set.

Alternately, you could break  this into two queries - one to return the 
artist name and image, the other to fetch the songs.

The third alternative would be to GROUP the results. I've not tested this, 
but if it can be properly processed by mysql_fetch_array(), then we're 
doing the work in SQL, not PHP.  Add this line after the where:

group by
  group by artist, image, song

Again, if you can test this at the mysql console it will be faster than 
making changes to a script and re-running it. You may not need to group on 
image. A very brief test script to see how mysql_fetch_array() handles a 
grouped record set is a good idea. Might be worthwhile to check Google for 
"mysql_fetch_array group"

Regards - Miles

PS Where is "cx"? /mt

At 12:38 AM 1/14/2002 +1300, Barry Rumsey wrote:

>This is properly a simple question but I can't figure it out.
>Lets say I've got there tables: table1(art_id,artist), 
>table2(song_id,art_id,song), table3(image_id,art_id,image).
>What I'm trying to do is get 1 image displayed with the songs on that 
>album displayed next to it. All I can get is 1image,1 song or 1 image per 
>song (20 songs = 20 images).


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] properly a simple question

[DL Neil]

Barry,

This is properly a simple question but I can't figure it out.

=please don't ask im-proper questions, this is a family environment!!!

Lets say I've got there tables: table1(art_id,artist), table2(song_id,art_id,song),
table3(image_id,art_id,image).
What I'm trying to do is get 1 image displayed with the songs on that album displayed 
next to it. All I can get
is 1image,1 song or 1 image per song (20 songs = 20 images).

=insufficient information. One (or a fourth) table will need to be populated with 
"album" data.

=Have I misunderstood? Perhaps you could clarify your question with the code that you 
have already attempted for
yourself (regardless of the fact that it isn't (yet) working), and maybe a 
demonstration of how you would like
the result to appear from a set of sample data.

=As you say, it is probably quite simple. If you haven't RTFM, then try throwing 
"join" at the manual's search
facility. Most of the examples show two-table joins, but a three-table join can be set 
up by extending the WHERE
clause to relate two-pairs of tables (and so on...) joined by an AND.

=Regards,
=dn



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] properly a simple question

[Barry Rumsey]

This is properly a simple question but I can't figure it out.
Lets say I've got there tables: table1(art_id,artist), table2(song_id,art_id,song), 
table3(image_id,art_id,image).
What I'm trying to do is get 1 image displayed with the songs on that album displayed 
next to it. All I can get is 1image,1 song or 1 image per song (20 songs = 20 images).