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