#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Smile Export data from multiple databases with one single select statement


    I have 100+ websites all sitting on a single host and accessable via phpMyAdmin. Each one of these websites uses the same database structure.

    I want to pull out all the profile specific information to do a mass mail to all my registered users. I'd then need to combine, sort, remove duplicates and upload to my mail client, I have the following query:

    SELECT `NickName`,`FirstName`,`LastName`,`Email` FROM `Profiles`;

    What I want to know is can I build a query where I can execute a similar statement yet pull the data from every database and just do one export? Then I can clean the data and upload and send everyone an email? Is this possible?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,486
    Rep Power
    1752
    My initial suggestion would be to use UNION, with one SELECT for each database, using the database.table.column 'full syntax'.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Need a little more


    Originally Posted by SimonJM
    My initial suggestion would be to use UNION, with one SELECT for each database, using the database.table.column 'full syntax'.
    Hi Simon, thanks... I'm no MySQL guru, could you elaborate? who a full example of a query?

    Many thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,486
    Rep Power
    1752
    I am far from a guru too, but ...
    something like:
    Code:
    SELECT db1.table1.column1 AS `NickName`
        , db1.table1.column2 AS `FirstName`
        , db1.table1.column3 AS `LastName`
        , db1.table1.column4 AS `Email`
      FROM db1.table1
    UNION
    SELECT db2.table1.column1
        , db2.table1.column2
        , db2.table1.column3
        , db2.table1.column4
      FROM db2.table1
    UNION
    SELECT db3.tableX.column1
        , db3.tableX.column2
        , db3.tableX.column3
        , db3.tableX.column4
      FROM db3.tableX
    Where db1, db2 and db3 would be your various databases, table1 and tableX would be the name of the tables in the relevant databases and column1 to column4 would be the columns names.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo