SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    May 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql selecting data from two databases

    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.

  2. #2
    SitePoint Member
    Join Date
    May 2010
    Location
    LA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try it this way:

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

  3. #3
    SitePoint Member
    Join Date
    May 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    May 2010
    Location
    LA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    beenie, please let me know what you want to do.
    It´s hard do help without info

  5. #5
    SitePoint Member
    Join Date
    May 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    @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

  6. #6
    SitePoint Member
    Join Date
    May 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SitePoint Member
    Join Date
    May 2010
    Location
    LA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:

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

  8. #8
    SitePoint Member
    Join Date
    May 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if you could please display your latest SELECT statement...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    May 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply.
    The code is as follows

    Code:
    @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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •