Page 1 of 1

Trouble Joining two databases

Posted: Wed Jul 21, 2010 9:05 am
by boba fett
I'm trying to join 2 databases for my webpage. I'm using this bit of code to do it

Code: Select all

SELECT *
FROM eachsnake JOIN specieslist
ON eachsnake.Scientific_Name = specieslist.Scientific_Name
but when i try to get a response only 1 of the 700 entries comes up
Is this code right? or am i missing something?

Re: Trouble Joining two databases

Posted: Wed Jul 21, 2010 9:24 am
by kurbot
Try:

Code: Select all

SELECT * FROM eachsnake  as ea
INNER JOIN specieslist as sp
ON ea.Scientific_Name = sp.Scientific_Name
Also could you paste how your connecting to the DB and how your looping through the DB records for display?

Re: Trouble Joining two databases

Posted: Wed Jul 21, 2010 9:28 am
by boba fett

Code: Select all

 <?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>";
    }

Re: Trouble Joining two databases

Posted: Wed Jul 21, 2010 9:30 am
by boba fett
I've tried inner, outer, left and right joins. the Left and right show more results but it doesnt show both sets of information just either the left or right table.

Re: Trouble Joining two databases

Posted: Wed Jul 21, 2010 12:06 pm
by Alex-V

Code: Select all

SELECT * FROM `db1`.`eachsnake` LEFT JOIN `db2`.`specieslist` ON `eachsnake`.`Scientific_Name` = `specieslist`.`Scientific_Name`
Hope it helps.

P.S. Replace `db1` and `db2` with your db names