How to call to look in to different databases?

Posted on 2011-05-11
Last Modified: 2012-05-11
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?
Question by:Nura111
    LVL 20

    Expert Comment

    by:Mark Brady
    I found this explanation on 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);
    LVL 15

    Accepted Solution

    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 from <dbname>.<tablename> a
    select from <dbname>.<tablename> a

    Author Comment

    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)

    Author Comment

    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?
    LVL 15

    Assisted Solution

    by:Walter Ritzel
    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 from <dbname>.<tablename> a
    select a.columnname as name from <dbname>.<tablename> a where a.column2 = '1'

    Author Comment

    so is there any way to find ="y" without writing such a long query? i have 59 of those so i don't want or write 59 different WHERE
    LVL 15

    Assisted Solution

    by:Walter Ritzel
    Create a view with all the tables and then apply the where.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
    This video teaches users how to migrate an existing Wordpress website to a new domain.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now