Matching up tables

I have two separate tables in my database - one with information and one with photos. I would like to link the two of these tables together. So when you go to an information page there is a small link that has a link to a photo for that fish. And vise versa when you go to the photos page.

The way I am going to have to link the tables together is by their scientific name. Because that is the only thing that they have in common. I tired something like this:

$result16 = mysql_query("SELECT * FROM photos WHERE $id=id",$db);
$result17 = mysql_query("SELECT * FROM info WHERE $result16=scientific_name",$db);
while ($myrow = mysql_fetch_array($result17)) {
printf("More information about <a href=$info_php?id=%s&author=%s><b>%s</b></a>?", $myrow["id"], $myrow["author_id"], $myrow["common_name"]);
}

I keep getting a 0 result when I do this.

I only want the link to show up if there is information about the fish in the other table. Also, the fields that I am tiring to link have multiple words because they are scientific names.

Does anyone know a better way for me to do this?

Off the subject - In my script I used "SELECT * FROM table". Does it kill a server if you use this A LOT. Sometimes when the script is loading it is being used 6-7 times.

------------------


[This message has been edited by fishindex (edited June 20, 2000).]

I'm not sure I got it completely, but it looks like you need some sort of join:

select * from info, photos where info.id=photo.id;

I probably wasn't too clear. I want to match table1 to table2 if any row in column1 is the same in the both of the tables. BUT in column1 all the records are something like "Foo foo." I think I can't get it to work because there are two words.

Would I use the join thing for something like this?

------------------

hello fishindex..

Please give full details of table with fields so that I can help U well.

vijay

select photo.picture as picture, info.scientificname as s_name, info.author as author, info.commonname as c_name from photo,info where photo.scientificname = info.scientificname;

I think that will work.

---John Holmes
---www.SepodatiCreations.com