Mysql selecting data from two databases

[beenie]

Hi

I am having a problem with calling data from two databases, both on the same server.

$result = mysql_query("SELECT * FROM db1.tb1,db2.tb2");

I am getting the data only from db2 displayed on the web page.

It seems to only connect to the last mentioned database

Any help appreciated.

[sbbtina]

Try it this way:

mysql_connect($db_server, $db_user, $db_pass, true);

[beenie]

Hi

I tried and now the following happens.

I have approx 20 rows in db1 and 2 rows in db2
The web page with the results shows 20 rows but has the data from db2

It does not make sense.
Thanks

[sbbtina]

beenie, please let me know what you want to do.
It´s hard do help without info

[beenie]

Sorry - I have spent days trying to figure this out so I am pulling my hair out.

I have two databases. There is a table in each db and both tables have a field called Town.

I have a web page which needs to get info from both databases with the condition that the Town is the same for that page.

My page is for the town of Dublin. So on this page I want to list the data from the two databases which have the same town ( Dublin)

This is my code taken from the page of Dunlin

@mysql_connect("localhost", "username", "password", true)
	or die("Could not connect to database.");



$result = mysql_query("SELECT * FROM db1.tb1,db2.tb2 WHERE    ");
								
 if (mysql_num_rows($result)==0) {
 echo "<p class='listing'>Sorry there is currently no listings for " . $town . ".</p>";
										 } else {			
										 echo "<table class='listing'>";
										  while ($row = mysql_fetch_array($result))
										 {
										 			
										 echo "<tr>";
										 ?>
										 
										 <?php
										 echo 
													 The rest of echo code here

.

I want to also add the WHERE condition of the town = Dublin but I have not been able to get to work properly either.

I hope I have made myself clear.

Many Thanks

[beenie]

Just an update - I can get the results that I need displayed on the page by doing the following roundabout way.

<?php
$town="Dublin";
$result = mysql_query("SELECT * FROM db1.tb1 WHERE Town LIKE '$town'");
if (mysql_num_rows($result)==0) {
echo "<p class='listing'>Sorry there is currently no listings for " . $town . ".</p>";
} else {
echo "<table class='listing'>";
while ($row = mysql_fetch_array($result))
{
echo "<tr>";
echo statements go here
?>


I then repeat the code for the 2nd database
<?php
$town="Dublin";
$result = mysql_query("SELECT * FROM db2.tb2 WHERE Town LIKE '$town'");
etc



The above works fine (at last 2 days of pure stress)
But I am unable to get it to work with one set of code under one select command.

Thanks

[sbbtina]

Do not select every field from your tables. Select only the fields you need.
In your sql query, you have to join the two tables.
Try it like this:

<?PHP
mysql_connect("localhost", "user", "pwd", true) or die("Could not connect to database.");

$town = "LA";
$result = mysql_query("SELECT db1.tbl1.town, db1.tbl1.test, db2.tbl2.town, db2.tbl2.otherfield FROM db1.tbl1, db2.tbl2 where db1.tbl1.town = db2.tbl2.town and db1.tbl1.town LIKE '$town'"  );

if (mysql_num_rows($result)==0) 
{
    echo "<p class='listing'>Sorry there is currently no listings for " . $town . ".</p>";
} 
else 
{
    echo "<table class='listing'>";
    while ($row = mysql_fetch_assoc($result))
    {
        echo "<tr><td>" . $row['town'] . " " .   $row['otherfield'] . " " . $row['test']. "</td></tr>";
    }
}
echo "</table>"
?>

[beenie]

Hi

Thanks for your reply.

I made the changes as directed, there should be now two rows displayed (I changed the Town in question to a lesser population in the db).

The results displayed is just one row and coming from the db2 mentioned in the Select function.

In the results I also wanted to display a logo for each listing. In one db it is called Logo and in the other it is called Clip. Not Important this can be changed but in the echo statments I had typed LOGO which is from db1.

The results show all details from db2 but also the logo from the listing in db1. It seems to be displaying the details all mixed up on one row only.

Beenie

[r937]

if you could please display your latest SELECT statement...

[beenie]

Thanks for the reply.
The code is as follows

@mysql_connect("localhost", "user", "pass", true)
	or die("Could not connect to database.");

 $town="Dublin";

 $result = mysql_query("SELECT db1.tb1.Logo, db1.tb1.Name, db1.tb1.Town, db1.tb1.Description, db1.tb1.Webpage, db2.tb2.Clip, db2.tb2.Name, db2.tb2.Town, db2.tb2.Description, db2.tb2.Website FROM db1.tb1, db2.tb2 where db1.tb1.town = db2.tb2.town and db1.tb1.town LIKE '$town'"  );
								 if (mysql_num_rows($result)==0) {
								 echo "<p class='listing'>Sorry there is currently no listings for " . $town . ".</p>";
						
 } else {			
								 echo "<table class='listing'>";
								 while ($row = mysql_fetch_array($result))
								 {
 echo "<tr>";

Many Thanks

[r937]

your query is fine

i suspect the problem is due to php not being able to distinguish between two columns with the same name

therefore, you should assign column aliases where necessary, and then access the values by the appropriate column or column alias name in your php code

SELECT db1.tb1.Logo
     , db1.tb1.Name
     , db1.tb1.Town
     , db1.tb1.Description
     , db1.tb1.Webpage
     , db2.tb2.Clip
     , db2.tb2.Name AS name2
     , db2.tb2.Town AS town2
     , db2.tb2.Description AS description2
     , db2.tb2.Website 
  FROM db1.tb1
INNER
  JOIN db2.tb2 
    ON db2.tb2.town = db1.tb1.town
 WHERE db1.tb1.town LIKE '$town'

two tips for working with SQL --

first, use JOIN syntax instead of the old-style comma joins

second, use line breaks and indents to make the query more readable instead of plastering everything into one long humoungous single line