Trouble Joining two databases

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
boba fett
Forum Newbie
Posts: 15
Joined: Fri Jul 09, 2010 2:48 pm

Trouble Joining two databases

Post 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?
kurbot
Forum Newbie
Posts: 18
Joined: Tue Jul 20, 2010 2:20 pm

Re: Trouble Joining two databases

Post 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?
boba fett
Forum Newbie
Posts: 15
Joined: Fri Jul 09, 2010 2:48 pm

Re: Trouble Joining two databases

Post 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>";
    }
boba fett
Forum Newbie
Posts: 15
Joined: Fri Jul 09, 2010 2:48 pm

Re: Trouble Joining two databases

Post 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.
User avatar
Alex-V
Forum Newbie
Posts: 17
Joined: Mon Jul 19, 2010 3:53 pm

Re: Trouble Joining two databases

Post 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
Post Reply