query multiple databases..

[RazorRamon]

Ok I need help. I have a couple hundred databases, which are different cities, that I am trying to connect with one query. each city has the same Sports table in it. I want to put the 100 most recent results on one page . The code below gets the following error. SELECT command denied to user On godaddy I have the highest hosting you can have without having to handle the server myself. My nightmare would be that I would have to upgrade and take full control over the server. Please help me!!!
If I take off the mysql_error() then the warning shows Warning: mysql_fetch_array() expects parameter 1 to be resource... PS the code did work on my local WAMP. Not sure if i have added privilages on my local server that I do not have with Godaddy.

$sql= 'select * from city ORDER BY id DESC';
$result=mysql_query($sql);
$counter=mysql_num_rows($result);
$i=1;
$all="";
while($rows = mysql_fetch_array($result)){
        $city =$rows['city'];
        $state=$rows['state'];
        $citytrimmed = str_replace(' ','',$city);
        $statetrimmed = str_replace(' ','',$state); 



    $cityst2= $citytrimmed . $statetrimmed;
    $cityst = strtolower($cityst2);
    $citydbhost =  $cityst;
    $citydbuser = $cityst;
    $citydbpass = 'XXXXXX';
    $citydbname = $cityst;

    }

    include 'cityopendb.php';

        $all.= 'Select * From '.$citydbname.'.Sports ' ;
    if($i < $counter){
        $all.= ' UNION ALL ';
        $i=$i+1;
    }else{;
        $all.= ' ORDER BY timestamp DESC LIMIT 100; ';
    }
}

$query=mysql_query($all) or die(mysql_error());

    while($row=mysql_fetch_array($query)){

    }

[cereal]

Something is not clear to me: your databases are all in the same server? If yes then this kind of select can be done, but you need to perform only one connection to a MySQL SERVER, actually it seems you are going to connect 100 different remote servers but your first while loop is going to overwrite those connection variables and at the end of the loop, you have only one value for each variable, the last one. So can you perform only one connection. Also in order to query all databases you need to change this a bit:

<?php

$sql= 'select * from city ORDER BY id DESC';
$result=mysql_query($sql);
$counter=mysql_num_rows($result);
$i=1;
$all="";
while($rows = mysql_fetch_array($result)){
    $city =$rows['city'];
    $state=$rows['state'];
    $citytrimmed = str_replace(' ','',$city);
    $statetrimmed = str_replace(' ','',$state); 
    $cityst= strotolower($citytrimmed . $statetrimmed);
    $all.= 'SELECT * FROM '.$cityst.'.Sports';

    if($i < $counter){
        $all.= ' UNION ALL ';
        $i=$i+1;
    }else{
        $all.= ' ORDER BY timestamp DESC LIMIT 100';
    }

}

$citydbhost = 'localhost';
$citydbuser = 'user_allowed_to_access_and_select_from_all_db_involved';
$citydbpass = 'XXXXXX';

include 'cityopendb.php';
$query=mysql_query($all) or die(mysql_error());
while($row=mysql_fetch_array($query)){

}

?>

Otherwise you need to loop through each database, select, save result to an array and merge it with other results, or you need to use Federated tables or you need to change the configuration of your servers, read this answer: http://stackoverflow.com/a/508114/963510

[RazorRamon]

The username changes with the name of the DB. I'm new to union all so I'm not sure what you mean by all on the same server. the "citydbhost" names are all different if thats what your asking. its $citydb.xx3872.1981.. something weird like that.

I think your probably as far as it over writing the connections. Maybe i'll use the counter to make each separate. I'll look into federated tables and saving results in arrays to see if that can solve my issues.

[cereal]

Each MySQL server can handle more than one database, so you can have 200 databases in the same computer, if so then you need just a connection to this server and run your queries as in the script above.

Besides previous suggestions: if your databases are located in different remote locations then you can create a page/file, in each server, which displays query results via web, you can use a function like json_encode() or serialize() to create a portable array:

<?php
# remote server: remote.dba1
# ... connection to database ...

$q = mysql_query("select * from table limit 0,30");
$a = array();
while($r = mysql_fetch_object($q))
{
    $a[] = array(
        'city' => $r->city,
        'something' => $r->something,
        'something_else' => $r->something_else
        );
}
echo json_encode($a);
?>

Then in your main server you can get data with a function like file_get_contents() and use json_decode/unserialize to get back an array:

<?php
# main
$dba1 = json_decode(file_get_contents('http://remote.dba1/page'));
print_r($dba1);
?>

You will need a loop here for each server. You can also use gz to reduce data transfer and igbinary_serialize which is faster than json and serialize. But you can also use curl or ftp functions instead of file_get_contents() so you do not expose remote data.. there are many possible solutions. Hope it's clear, English is not my main language.. bye! :)

[mschroeder]

Assuming you are not confusing a table and a database, and that the databases all reside on one server, this is relatively easy to achieve.

Here is a quick example to illustrate the technique:

Database1 - Table people

DROP TABLE IF EXISTS `people`;

CREATE TABLE `people` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOCK TABLES `people` WRITE;
/*!40000 ALTER TABLE `people` DISABLE KEYS */;

INSERT INTO `people` (`id`, `name`, `age`)
VALUES
    (1,'jim smith',18),
    (2,'john doe',21),
    (3,'bill johnson',37);

/*!40000 ALTER TABLE `people` ENABLE KEYS */;
UNLOCK TABLES;

Database2 - Table people

DROP TABLE IF EXISTS `people`;

CREATE TABLE `people` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOCK TABLES `people` WRITE;
/*!40000 ALTER TABLE `people` DISABLE KEYS */;

INSERT INTO `people` (`id`, `name`, `age`)
VALUES
    (1,'mary smith',22),
    (2,'linda doe',40),
    (3,'rachel lynn',15);

/*!40000 ALTER TABLE `people` ENABLE KEYS */;
UNLOCK TABLES;

PHP

<?php
$mysqli = new mysqli("localhost", "user", "password");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT * FROM database1.people;";
$query  .= "SELECT * FROM database2.people";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                print_r($row);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();

How it works

First we establish a connection to the server, which contains all of the databases. There is NOT a database specified in the connection details.
Then, we setup our queries.
Finally we use mysqli_multi_query to run them in parallel. (Also where the example php was taken from)

And that is as difficult as it needs to be. Our results look like:

Array
(
    [0] => 1
    [1] => jim smith
    [2] => 18
)
Array
(
    [0] => 2
    [1] => john doe
    [2] => 21
)
Array
(
    [0] => 3
    [1] => bill johnson
    [2] => 37
)
-----------------
Array
(
    [0] => 1
    [1] => mary smith
    [2] => 22
)
Array
(
    [0] => 2
    [1] => linda doe
    [2] => 40
)
Array
(
    [0] => 3
    [1] => rachel lynn
    [2] => 15
)

If your databases are spread out across multiple servers, then you will need a connection for each server and to run the queries for every database on each individual server. This could become a performance issue ultimately.

[Biiim]

I recently improved a database we had in a similar setup, we had a table for every client which held all the relevant data for the company, this was setup over 2 years ago now and we were up to over 500 tables! the lack of scalability starting to show itself. I planned out a new table with all data that was wanted and what wasnt needed anymore, literally the only main difference is the new table had an extra field "client" and all 500 tables just moved into one table with that field set to thier client id - this sounds like what you need to do with all your cities.

So rather than 200 databases with a sports table it becomes 1 sports table with an extra city column, then its just "select * from sports where city = '{$city}'";

Not sure how your system is set up but that is a very server intesive workaround to get that data, 200 connections and queries then merging them in a php array - you wont be able to union on new connections.

You need 1 user with access to all 200 databases (if they are all on the same server), your main hosting mysql user should have this access use that or create a user with access to the lot - this will cut it down to 1 connection and 200 queries, with mysql joining them all together in 1 result set.

eg. my root mysql connection can view and access all 21 databases on our server, however a user made for a website only has access to the 1 database that it needs to access - for security as you dont want random developers looking through all the other webmasters data.

If they are on different servers i would just export all the data into a new merged table.

Personally i'd just do the hardwork of merging them into one table, making sure you add an index on city, it will save lots of time in the future.

[RazorRamon]

Well see my site is a forum type of site where users can input their own sports topics depending on the city. So the data will always be changing so I dont know If exporting it will work.

I feel somewhat dumb when it comes to the server/ connections/ databases. How can i tell if my databases are in the same server?

Also... I'm going to add many many more databases... right now i have about 400.. i'm wanting to get to 5000...

[Biiim]

The host part in the mysql_connect() will tell you if its the same server, they should all resolve to the same ip address. if you use a domain as the host use a tool like http://network-tools.com/ to see what ip address the domain resolves to.

I cant really think of any case that you would need more than 1 database to store data for one thing, you should just have a database for the whole forum with tables inside the database for the topics, replies, users, categories and any other data on the forum in it. The only reason you want another database is if its got nothing to do with the forum eg. a completely separate forum or another website

I get the idea you have had this setup and are now in a hole trying to get things working, i would make a copy of 2/3 sports tables(from different cities) and make a new combined table with all 2/3 tables merged into 1 table with the extra column "city", index city.

Once you got the table, make some copies of live pages that users are using on the website that use the old databases and recode them to run on the new table.

When you got all you need to working(to go live). You need to take the site down to prevent anymore database updates happening, merge all the data into 1 table the way you have already tested, overwrite the old web pages to the new ones that operate off the single table then you are back live on the new single table.

Of course this depends on just how much work needs to be done to convert your system over to operating off a single table, the longer you hold it off though the harder this is going to get to do.

Your original error is pretty simple:

SELECT command denied to user

That is just saying the current user mysql is connected with doesn't have permission to use SELECT commands on a database in the query. You can probably just fix it by getting a user with access to all the databases.

What mysql program do you use to connect to mysql, phpmyadmin?

I use a program called sqlyog its free and makes mysql management very easy, put in your connection details that your mysql_connect() uses and you will see all the databases the user has access to, you need to get a user that has access to all databases - you might even have to ask godaddy to make one for you - im not sure how your hosting is setup

[RazorRamon]

Yea i'm actually thinking that i'm going redesign the site due to these issues. The one thing that boggles my mind a little is the whole site is dynamic and drawn from database/tables. So i know that user does have access to select commands. Yes my databases are managed through a phpmyadmin system. I know i have limited privilages when it comes to my hosting even tho I have the "deluxe".. I had an issue earlier this week where I wanted to create all the databases using a script rather than phpmyadmin. Sadly the Godaddy rep explained that I can not do that and that I would have to create each database one by one manually through phpmyadmin.

I adjusted my script but it is still not working.

$sql= 'select * from city ORDER BY id DESC';
$result=mysql_query($sql);
$counter=mysql_num_rows($result);
$i=1;
$all="";
while($rows = mysql_fetch_array($result)){

    $city =$rows['city'];
    $state=$rows['state'];
    $citytrimmed = str_replace(' ','',$city);
    $statetrimmed = str_replace(' ','',$state); 
$cityst2= $citytrimmed . $statetrimmed;
$cityst = strtolower($cityst2);
$citydbhost =  $cityst.877292k.938.92;
$citydbuser = $cityst;
$citydbpass = 'XXXXXX';
$citydbname = $cityst;

        include 'cityopendb.php';




        if (!$cityconn){
            die('Could not connect: ' . mysql_error());
        }


    $all.= 'Select * From '.$cityst.'.Sports ' ;
    if($i < $counter){
        $all.= ' UNION ALL ';
        $i=$i+1;
    }else{;
        $all.= ' ORDER BY timestamp DESC LIMIT 100; ';
    }
}

I'm still getting the same error.

[RazorRamon]

Ok after very little research with arrays this is what i have come up with. I havent tested it cause im at work, let me know yalls thoughts.. will this work? am i close?

        $a=array();


            $sql= 'select * from city ORDER BY id DESC';
        $result=mysql_query($sql);
        $counter=mysql_num_rows($result);
        $i=1;
        $all="";
        while($rows = mysql_fetch_array($result)){
                $city =$rows['city'];
                $state=$rows['state'];
                $citytrimmed = str_replace(' ','',$city);
                $statetrimmed = str_replace(' ','',$state); 
            $cityst2= $citytrimmed . $statetrimmed;
            $cityst = strtolower($cityst2);
            $citydbhost =  $cityst;
            $citydbuser = $cityst;
            $citydbpass = 'XXXXXX';
            $citydbname = $cityst;

            include 'cityopendb.php';


             $all.= 'Select * From Sports ' ;

        $query=mysql_query($all) or die(mysql_error());

       while ($row = mysql_fetch_array($query)){
                    array_push($a, $row);
        }
    }


      print_r($a);

[Biiim]

I can only see 1 error here

$all.= 'Select * From Sports ' ;

you could just define $all = 'Select * From Sports'; outside the while loop, just make sure it's not concatenating within the while loop ".="

apart from that looks like it will run.

you may want to add an extra id in, in case id's duplicate in different tables?

    while ($row = mysql_fetch_array($query)){
        $a[$cityst][] = $row;
        //array_push($a, $row);
    }

that way when you print out you will be able to tell which database the data came from