Results 1 to 13 of 13
Thread: Query from multiple MySQL DB
-
08-27-2002, 03:06 PM #1Newbie
- Join Date
- Jul 2002
- Posts
- 13
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.
-
08-27-2002, 04:10 PM #2Web Hosting Master
- Join Date
- Jun 2001
- Location
- Earth
- Posts
- 1,259
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
-
08-28-2002, 09:55 AM #3Web Hosting Master
- 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. (?)
-
08-28-2002, 10:02 AM #4Web Hosting Master
- Join Date
- Jun 2001
- Location
- Earth
- Posts
- 1,259
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
-
08-28-2002, 11:56 PM #5Newbie
- Join Date
- Jul 2002
- Posts
- 13
Thanks... and if using MySQL 4.x, with PHP, will I just open two connections to the two databases?
-
08-29-2002, 02:52 AM #6Web Hosting Master
- 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
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
-
08-29-2002, 11:26 AM #7Web Hosting Master
- Join Date
- Jun 2001
- Location
- Earth
- Posts
- 1,259
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
-
11-23-2005, 08:27 AM #8New Member
- Join Date
- Nov 2005
- Posts
- 1
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 ?
-
11-23-2005, 10:30 AM #9Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
make sure the user that you are accessing with has rights to both db1 and db2 -- it should work then.
-
11-23-2005, 08:55 PM #10Web Hosting Master
- Join Date
- Dec 2002
- Posts
- 1,304
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.
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-23-2005, 09:09 PM #11Web Hosting Guru
- Join Date
- Nov 2005
- Posts
- 282
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.
-
11-23-2005, 10:23 PM #12Web Hosting Master
- Join Date
- Dec 2004
- Location
- New York City, NY, USA
- Posts
- 735
My thoughts on the points mentioned, arguing in favor of innova...
- 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.
- 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.
- 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...Samat Jain | Rhombic Networks, LLC - Partner, CTO
-
11-24-2005, 12:49 AM #13Web Hosting Master
- Join Date
- Dec 2002
- Posts
- 1,304
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"