Results 1 to 13 of 13
  1. #1

    Query from multiple MySQL DB

    Kinda a shot in the dark... does anyone know if there is a way to make a query that references tables from multiple databases? I am basically trying to create a table that every image in my site will be stored in and then different parts of the site which are on different DBs will use the image table which is located in the main DB. Can I basically open up two connections to databases and then reference tables as db1.table1 and db2.table2. For example, will
    SELECT image.image_title, greetings.date FROM db1.image AND db2.greetings WHERE greetings.image_id=image.imageid
    work?

    Thanks a lot.

  2. #2
    Join Date
    Jun 2001
    Location
    Earth
    Posts
    1,258
    Very close

    SELECT image.image_title, greetings.date
    FROM db1.image,
    db2.greetings
    WHERE greetings.image_id=image.imageid

    It's just a "," between the two table names, not an and.

    Frank
    Umbra Hosting
    cPanel | Softaculous | CloudLinux | R1Soft | Ksplice
    Web Hosting, Reseller Hosting, VPS, Dedicated Servers, Colocation
    UmbraHosting.com

  3. #3
    Join Date
    Jan 2002
    Location
    Kuwait
    Posts
    679
    Now can you couple a SELECT statement from one table with an INSERT into another one, where two tables are from different DB's?

    I tried the notation dbname.tablename once, but that didn't work. It seems that you can only use the notation in SELECT statements. (?)
    Ahmad Alhashemi
    PHP, Apache, C, Python, Perl, SQL
    18 related BrainBench certificates

  4. #4
    Join Date
    Jun 2001
    Location
    Earth
    Posts
    1,258
    Ahmad,

    I do not believe that MySQL 3.x supports that (but it's perfect valid SQL). I seem to remember reading that MySQL 4.x does support that.

    Frank
    Umbra Hosting
    cPanel | Softaculous | CloudLinux | R1Soft | Ksplice
    Web Hosting, Reseller Hosting, VPS, Dedicated Servers, Colocation
    UmbraHosting.com

  5. #5
    Thanks... and if using MySQL 4.x, with PHP, will I just open two connections to the two databases?

  6. #6
    Join Date
    Jan 2002
    Location
    Kuwait
    Posts
    679
    Originally posted by ffeingol
    Ahmad,

    I do not believe that MySQL 3.x supports that (but it's perfect valid SQL). I seem to remember reading that MySQL 4.x does support that.

    Frank
    You mean the database notation?

    The functionality itself is already available in MySQL, but the database notation in this context is not.

    You can select from one table and insert into the other, only they must be in the same database.

    http://www.mysql.com/doc/en/ANSI_dif...NTO_TABLE.html
    http://www.mysql.com/doc/en/INSERT_SELECT.html
    Ahmad Alhashemi
    PHP, Apache, C, Python, Perl, SQL
    18 related BrainBench certificates

  7. #7
    Join Date
    Jun 2001
    Location
    Earth
    Posts
    1,258
    Ahmad,

    You are correct that you can do an insert with a select. I was just having a brain cramp.

    Frank
    Umbra Hosting
    cPanel | Softaculous | CloudLinux | R1Soft | Ksplice
    Web Hosting, Reseller Hosting, VPS, Dedicated Servers, Colocation
    UmbraHosting.com

  8. #8
    I have the same question, using MySql from Php.
    I want to insert from one database to another, something like:
    INSERT INTO db1.table1 SELECT * FROM db2.table2 WHERE a="b" ;

    In php it would be:
    $query="INSERT INTO db1.table1 SELECT * FROM db2.table2 WHERE a=1" ;
    mysql_query($query,$connection);
    but $connection is for db1 or db2, not both.

    I am using mysql 4.3
    What should I do ?

  9. #9
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    make sure the user that you are accessing with has rights to both db1 and db2 -- it should work then.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  10. #10
    I am basically trying to create a table that every image in my site will be stored in and then different parts of the site which are on different DBs will use the image table which is located in the main DB.
    Sorry for not answering your question, but I would challenge you to rethink your backend design. Specifically - focusing on the following two things:

    1) Why store images in a database at all? Seriously consider keeping them in a folder on the filesystem and store only the url's to the images in the DB.

    2) Is it necessary to put things in separate databases at all? Keep in mind, mysql will let you be as fine-grained as possible with user permissions (in case you want to have separate users for different apps), and there is no probable architectural reason (or maybe limitations) why it cant be done. Having it all in one DB would certainly simplify administration, backing up, and most importantly - getting data in and out!
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  11. #11
    Join Date
    Nov 2005
    Posts
    268
    I can answer 1 inno, There are several reasons why you might want to stick files into a database instead of writing them to disk.

    1) Security, the data in the database will have to be accessed through a query (that hopefully you screen), so there will be no worries about having to store the files below the webroot or even messing with directory permissions. If the file is stored above the webroot then you have to worry about them getting 'stolen' or accessed without authorization.
    2) Moving, it is far simpler to move the web structure if there is nothing dynamic about it. If you were previously storing files one directory below the webroot and accessing them through a script and the new site does not allow you to do that, what do you do? A massive update query?
    3) searchability, there are more things you can search about the file if the entire file is loaded into the database right?

    As far as 2 goes, I cant think of any reason to have the images in a seperate database, a seperate table for sure, but not a different database.

  12. #12
    Join Date
    Dec 2004
    Location
    New York City, NY, USA
    Posts
    735
    My thoughts on the points mentioned, arguing in favor of innova...
    1. Security: Storing files in a DB opposed to the filesystem does ease security headaches, but you need to weigh that benefit against the side effects. Databases are generally made for storing TEXTUAL data; storing large binary pieces of data slows them down considerably. There are some benchmarks about what happens with MySQL once you start having many rows containing binary BLOBs.
    2. Movability: You can fix the movability problem in your code by storing a path relative to some location (a constant in your program) rather than storing a hard-coded URL.
    3. Searchability: err... what's a database provide (that you would not have to add extra columns for anyway) that storing in a filesystem does not?

    I agree with innova as well about rethinking your design: why does data need to be in two separate databases? If this is something you have control over, I'd recommend switching...

  13. #13
    For the record, I dont believe I recommened storing 'fixed' paths, but rather relative URL's to the image as tamusrepus mentioned.

    As for the search portion.. you can search/parse a url 'record' just as easily as the 'name' of an image stored in a database. This is a wash either way, except that searching relative URL's by name would be faster than sifting through tons of BLOB data.
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  14. Newsletters

    Subscribe Now & Get The WHT Quick Start Guide!

Posting Permissions

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