trouble joining 2 tables

[boba fett]

I am joining 2 tables for  a web page about snakes.  I am currently using this code to join my 2 tables

 

 <?php
// Make a MySQL Connection

mysql_connect("localhost:8889","root","root") or die ('Error connecting');
mysql_select_db("snakebook");
// Construct our join query
$query = "SELECT eachsnake.Avid, specieslist.Scientific_Name, specieslist.Common_Name, specieslist.Image, specieslist.Map, eachsnake.Origin, eachsnake.Location ".
"FROM eachsnake JOIN specieslist ".
    "ON eachsnake.Scientific_Name = specieslist.Scientific_Name";
     
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
   
   


$avid = $row['Avid'];
$common = $row['Common_Name'];
$scientific = $row['Scientific_Name'];
$image = $row['Image'];

//display data
echo "<center><table border='0' cellpadding='5' cellspacing='0'>
";

echo "<tr>
<td><strong>$avid</strong></td>
<td><strong><i>$scientific</i></strong></td>
<td><strong>$common</strong></td>
<td>$image</td>
</tr>";
    }
?>

 

I have tried left and right joins but i only get info from one of the tables not both like i would like.

[shlumph]

Can you give us a few rows of sample data from your DB?

[samona]

Try the following

 

$query = "SELECT eachsnake.Avid, specieslist.Scientific_Name, specieslist.Common_Name, specieslist.Image, specieslist.Map, eachsnake.Origin, eachsnake.Location ".
"FROM eachsnake, specieslist ".
    "ON eachsnake.Scientific_Name = specieslist.Scientific_Name";

[boba fett]

Error Message:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON eachsnake.Scientific_Name = specieslist.Scientific_Name' at line 1

 

Sample of Data from eachsnake:

 

AVID | Scientific_Name | Location

010-532-375 | Crotalus atrox | (USA) TX: Hudspeth County, Dell City

058-556-069 | Crotalus viridis viridis | Captive Born 12-Aug-98 NTRC

 

Sample Data from specieslist:

 

Scientific_Name | Thumbnail | Diet

Crotalus atrox | | Mammals (mice, rats, squirrels, rabbits), lizards, and birds.

Crotalus viridis viridis | | small mammals (mice, ground squirrels, rabbits), birds, and amphibians.

 

I'd like to be able to include whatever information from the tables' columns that I like (both tables have about 10-15 columns) but can't seem to get the information to post for more than one snake.

 

[shlumph]

It's better to use integer values for joins, since there's a chance that the varchar values for Scientific_Name are slightly different in each table. But I'd do something like this:

 

SELECT e.*, s.* FROM eachsnake e
LEFT JOIN specieslist s ON e.Scientific_Name = s.Scientific_Name

 

See if that query works, and then you can manipulate it the way you want.