displaying data from 4 tables

mdwilliams

Dear all,

I have 4 tables in my db:

images
artisits
colections
link

images and artisits are self-expanitory.
collections contains id, title, description etc.
link contains the id of the collections and the id's of the images that are to be included in those collections.

i need some help in showing this data on the page. when an artisit is selected, i want a page to show the artisit's first colection, with links to the other collections he/she might have. so i need the phototgraphers name, the name of the colelction, the name of the images in that colleciton, and links to the other collections by that artist.

can anyone help?

many thanks in advance - mark

tekky

check out the mysql manaul on SELECT and AS

you will need something like this...

"SELECT photographers.name as artist, collections.name as collection, images.name AS image FROM photographers, collections, images WHERE artist='$id'"

may be some errors in taht... in fact I guarantuee there is atleast one.... 🙂

ultraslacker

Originally posted by tekky

"SELECT photographers.name as artist, collections.name as collection, images.name AS image FROM photographers, collections, images WHERE artist='$id'"

may be some errors in taht... in fact I guarantuee there is atleast one.... 🙂

Yes, the error is that the query involves a cartesian join, rarely a good idea.
mysql join syntax

mdwilliams

Thanks for the replies. Being a beginner I found it vwery hard to understand what the MySQL manual was talking about. I don;t understand JOINs very well yet, not very well at all!

what I have so far is..

<?
if ($stage == "")
	{
	$SQL = "SELECT phot_id, phot_fname, phot_sname FROM lm_phot";
	$result = mysql_query($SQL);
	while($row = mysql_fetch_array($result))
		{
		echo "<a href=view.php?stage=2&phot=$row[phot_id]>$row[phot_sname], $row[phot_fname]</a><br>\n";
		}
	}



else if ($stage == "2")
	{
	$SQL = "SELECT phot_fname, phot_sname, coll_id, coll_title FROM lm_phot, lm_coll WHERE coll_phot = phot_id AND phot_id = $phot";
	$result = mysql_query($SQL);
	while($row = mysql_fetch_array($result))
		{
		echo "<a href=view.php?stage=3&coll=$row[coll_id]&phot=$phot>$row[coll_title]</a><br>\n";
		}
	}



else if ($stage == "3")
	{
	$SQL = "SELECT * FROM lm_link, lm_img WHERE lm_link.link_coll = $coll AND lm_img.img_id = lm_link.link_img";
	$result = mysql_query($SQL);
	while($row = mysql_fetch_array($result))
		{
		echo "$row[img_title]<br>\n";
		}
	}
	?>

It works, but not exactly how I want it. The second page (stage=2) shows a list of the collections, and the third show the images in that collection, but I want the second page to show the first collection with links to the subsequent collection (if they exist).

Many thanks again - Mark