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.
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.
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.
@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>";
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 [COLOR="Blue"]AS name2[/COLOR]
, db2.tb2.Town [COLOR="blue"]AS town2[/COLOR]
, db2.tb2.Description [COLOR="blue"]AS description2[/COLOR]
, 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