hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Query from multiple MySQL DB
Reply

Forum Jump

Query from multiple MySQL DB

Reply Post New Thread In Programming Discussion Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old
Newbie
 
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.



Sponsored Links
  #2  
Old
Community Guide
 
Join Date: Jun 2001
Location: Earth
Posts: 1,256
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  
Old
Web 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. (?)

__________________
Ahmad Alhashemi
PHP, Apache, C, Python, Perl, SQL
18 related BrainBench certificates

Sponsored Links
  #4  
Old
Community Guide
 
Join Date: Jun 2001
Location: Earth
Posts: 1,256
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  
Old
Newbie
 
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?

  #6  
Old
Web Hosting Master
 
Join Date: Jan 2002
Location: Kuwait
Posts: 679
Quote:
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  
Old
Community Guide
 
Join Date: Jun 2001
Location: Earth
Posts: 1,256
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  
Old
New 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 ?

  #9  
Old
Community Guide
 
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  
Old
Web Hosting Master
 
Join Date: Dec 2002
Posts: 1,300
Quote:
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  
Old
Web Hosting Guru
 
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  
Old
Web 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...
  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...

__________________
Samat Jain | Rhombic Networks, LLC - Partner, CTO

  #13  
Old
Web Hosting Master
 
Join Date: Dec 2002
Posts: 1,300
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"

Reply

Related posts from TheWhir.com
Title Type Date Posted
New Metadata Service Lets DigitalOcean Users Automate ‘Droplet’ Provisioning Web Hosting News 2014-10-15 16:48:51
Tesora's OpenStack DBaaS Supports MongoDB, Cassandra, Redis, and MySQL Web Hosting News 2014-05-23 14:53:11
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
PHP And MySQL Scaling: Preparing A Startup For Growth Blog 2014-04-24 13:27:35


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
WHT Host Brief Email:

We respect your privacy. We will never sell, rent, or give away your address to any outside party, ever.

Advertisement:
Web Hosting News:
WHT Membership
WHT Membership



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?