Jump to content

Export data from multiple databases with one single select statement


michaeljdornan
Go to solution Solved by Barand,

Recommended Posts

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?

Link to comment
Share on other sites

It should be possible. Use a loop. Pseudocode:

$perm_conn=mysqli_connect(...combined_data);
truncate combined data // make sure you start with an empty table
$databases=Array('db0', 'db1);
foreach ($databases as dbname){
connection=mysqli_connect....$dbname);
$select=//your select goes here
$result=mysqli_query($select);
while $row=mysqli_fetch_assoc($result){
//clean up here and/or at end
//write $row to perm_conn here;
}
close connection to $dbname and free $results here (don't want 100 open connections) 
}
//clean up, dedupe and send here

Actually I like PravinS approach better, which you can also do in a loop.

Edited by davidannis
Link to comment
Share on other sites

try

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE );

$sql = "SHOW DATABASES";
$res = $db->query($sql);
while (list($dbname) = $res->fetch_row()) {
    switch ($dbname) {
        case 'information_schema':
        case 'mysql':
            continue;
        default :
            $queries[] = "SELECT `NickName`,`FirstName`,`LastName`,`Email` FROM `$dbname`.`Profiles` \n";
            break;;
    }
}

$sql = join(" UNION\n", $queries) . "ORDER BY Email";

echo '<pre>' . $sql . '</pre>';  // $sql IS THE QUERY TO BE EXECUTED

You can check for duplicate emails as you process results

Edited by Barand
Link to comment
Share on other sites

 

try using DATABASENAME.TABLENAME in you select query like this

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

Hi, thanks for the help, PravinS is probably the go for me, quick and easy... I built the query, one issue, some of the databases have a - in them so 'database-name.Profiles;' and SQL is complaining about the - .... I've tried removing and leaving a space, no joy, can I wild card it?

 

Thanks

Link to comment
Share on other sites

Also, I just removed the ones with the dash in - just to test it, the query runs but only displays the results on screen from the last database in the list, and when I click export it jumps to that last database and only executes and pulls the data from the last table, not all database tables...?

 

Thanks

Link to comment
Share on other sites

i think it will not work through phpmyadmin, you can use sqlyog (MySQL GUI Tools), download it from here https://www.webyog.com/

 

now when you will execute multiple queries in sqlyog query editor, it will give you multiple result in different tabs below the editor, now you will need to export this result one by one in which ever format you want (i.e. csv,sql etc.)

 

may this will help you

Link to comment
Share on other sites

i think it will not work through phpmyadmin, you can use sqlyog (MySQL GUI Tools), download it from here https://www.webyog.com/

 

now when you will execute multiple queries in sqlyog query editor, it will give you multiple result in different tabs below the editor, now you will need to export this result one by one in which ever format you want (i.e. csv,sql etc.)

 

may this will help you

 

 

Hi PravinS that worked great thanks although still issues with any database with a - in it, half the databases didn't return any data and I got this in the log:

 

Query: SELECT `NickName`,`FirstName`,`LastName`,`Email` FROM ziguana_cystic-fibrosis.Profiles LIMIT 0, 1000
 
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-fibrosis.Profiles LIMIT 0, 1000' at line 1
 
Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.558 sec
 
Thanks
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.