• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

How to call to look in to different databases?

I have differents databases with similiar structure(in the same place) I would like to perform search on all of them together throw phpmyadmin at the same time.
for example to retrieve all the the columns name x from those databases.
is there anyway to do it?
0
Nura111
Asked:
Nura111
  • 3
  • 3
3 Solutions
 
Mark BradyCommented:
I found this explanation on stackoverflow.com. I did not think it was possible to load more than 1 database at a time but read this.

You can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the '$new_link' (fourth) parameter, otherwise the same connection is reused.

so then you have

$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);

mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);
Then to query database 1, do

mysql_query('select * from tablename', $dbh1);
and for database 2

mysql_query('select * from tablename', $dbh2);
0
 
Walter RitzelSenior Software EngineerCommented:
First let me ask this: you have many databases in the same server, is that correct?
So, this means that on phpmyadmin, when you log in, you can see the many databases on the left side of the screen right?
If this is the case, to solve that, you should do the following:
1) have a user that can select on all databases;
2) log in into phpmyadmin with this user;
3) on the SQL panel, run a query like this:

select a.name from <dbname>.<tablename> a
union
select a.name from <dbname>.<tablename> a
union
....
0
 
Nura111Author Commented:
yes that's the case ill give it a try.
is there also a way to repair/optimize them all at once instead of going one by one throw the process?
(in phpmyadmin)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nura111Author Commented:
ok how can i add the WHERE to apply to all of them if im looking for a specific column that exist only in one of them?
0
 
Walter RitzelSenior Software EngineerCommented:
each query in a union will have their own WHERE clause, so you can filter the specific query by a field that exist in only one of them.
To use a union, the only thing that is required is that all queries have the same columns being listed.

select a.name from <dbname>.<tablename> a
union
select a.columnname as name from <dbname>.<tablename> a where a.column2 = '1'
0
 
Nura111Author Commented:
so is there any way to find x.name ="y" without writing such a long query? i have 59 of those so i don't want or write 59 different WHERE
0
 
Walter RitzelSenior Software EngineerCommented:
Create a view with all the tables and then apply the where.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now