[RESOLVED] 1 query 2 databases

[fnp]

I have been splitting up a database with several tables in it. I am curious if it is possible to have a MySQL statement that will query 2 different tables within 2 different databases. I think you can use something like:

FROM tblListings AUC database1, tblSubmissions SBX database2

Obviously the databases would be predefined to be database 1 or 2.

Any help would be great.

Thanks

[bradgrafelman]

You can access tables from databases other than the current active database by using the database.table identifier syntax.

[fnp]

So the code would read something like this.

FROM $connection2.tblListings AUC, $connection1.tblSubmissions SBX

and the database connection would look something like this:

$connection1 = new mysqli($h['DOST'],$u['DOST'],$p['DOST'], $d['DOST']);
    $connection1->select_db($d['DOST']);
$connection2 = new mysqli($h['DAUC'],$u['DAUC'],$p['DAUC'], $d['DAUC']);
    $connectio2n->select_db($d['DAUC']);

Also how to get the '$result' statement to work I think would be something like this:

$result = $connection1, $connection2->query($query);

I need this to work right out of the box, I will be doing to switch to the new database and scripts in one night so I need to be sure. I also have no way to test it until that night.

Thanks for the help

[sneakyimp]

No you would not make two connections. You would make one database connection using a user/pass that has privileges to access both databases and then do something like this:

$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'], $d['DOST']);
$connection->select_db($d['DOST']); // this may not even be necessary

$sql = "SELECT t1.*, t2.*,  FROM DOST.someTable t1, DAUC.someOtherTable t2
WHERE t2.id=t1.id";

[fnp]

Sorry about reviving this post but I lost a database and have been fixing it, but that is another story.

Anyway this is the code I have:

            $query = "SELECT AUC.ListingID, AUC.SubmissionID, AUC.AuctionSiteID, AUC.AuctionTitle, AUC.AuctionNumber, DATE_FORMAT(AUC.DateStarted, '%m/%d/%Y') AS DateStarted, SBX.StoreID ";
            $query .= "FROM $connection1.tblListings AUC, $connection.tblSubmissions SBX ";
            $query .= $wherestore.$wheredates."AND AUC.SubmissionID=SBX.SubmissionID ";
            $query .= "ORDER BY AUC.DateStarted DESC";
			$listTitle="All Auctions";

$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'], $d['DOST']);
$connection->select_db($d['DOST']);

$connection1 = new mysqli($h['DAUC'],$u['DAUC'],$p['DAUC'], $d['DAUC']);
$connection1->select_db($d['DAUC']);

$result = mysql_query($query);

The problem I think I am having now is with the result section:

$result = mysql_query($query);

I get the error:

Warning: mysql_query() [function.mysql-query]: Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2) in /homepages/7/d170018324/htdocs/test/auclist.php5 on line 226

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /homepages/7/d170018324/htdocs/test/auclist.php5 on line 226

Line 226 is the $result line.

Any help is appreciated.

[dagon]

your mixing up mysql and mysqli

if you make a connection with one, you use that for the query.

[sneakyimp]

You should read the docs on functions like [man]mysql_query[/man] and [man]mysqli_query[/man]. You'll see that the mysqli functions generally require an extra argument -- the $db_link object created when you connected.

[fnp]

Now I get this fault:

Parse error: syntax error, unexpected T_OBJECT_OPERATOR in /homepages/7/d170018324/htdocs/test/auclist.php5 on line 226

I know this problem is with the $result section, but I can't pin it down.

That section use to read:

$result = $connection->query($query);

and now reads:

$result = mysqli->query($query);

which I know is wrong.
In the previous result section it passed the connection details to the query, but with the new format I need it to pass 2 connections to the query.

I may be barking up the wrong tree with what I am trying to do here.
Any help is appreciated.

[sneakyimp]

You can't do this:

$result = mysqli->query($query); 

mysqli has not been defined. In order to use an object's -> operator, you first have to define an object. Why don't you try reading the docs? There are plenty of examples in there.

[fnp]

I have read plenty of docs, but none have really explain what I am trying to do!

I now have this:

$query = "SELECT AUC.ListingID, AUC.SubmissionID, AUC.AuctionSiteID, AUC.AuctionTitle, AUC.AuctionNumber, DATE_FORMAT(AUC.DateStarted, '%m/%d/%Y') AS DateStarted, SBX.StoreID ";
            $query .= "FROM $connection1.tblListings AUC, tblSubmissions SBX ";
            $query .= $wherestore.$wheredates."AND AUC.SubmissionID=SBX.SubmissionID ";
            $query .= "ORDER BY AUC.DateStarted DESC";
			$listTitle="All Auctions";

$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'], $d['DOST']);
    $connection->select_db($d['DOST']);

$connection1 = new mysqli($h['DAUC'],$u['DAUC'],$p['DAUC'], $d['DAUC']);
//$connection1->select_db($d['DAUC']);

$result = $connection->query($query);

But with that I am pulling information from the old database and not the new one.

[sneakyimp]

In your query, you can refer to a database name as well as a table name.

Something like:

SELECT * FROM `database_name`.`table_name`

I don't think this will work if the two databases are on entirely different hosts. If $h['DOST'] is not the same as $h['DAUC'] then you will probably have to try something else.

If you have two distinct connections, one to each database, you will not be able to create a single query for two db's.

I would try modifying your query to refer to the db name in as well as the table.

[fnp]

Thank you for letting me know that I am barking up the wrong tree.

The two databases are on separate hosts but still hosted by the same company.

I suppose the only thing left to do is create separate queries, unless anybody has any other suggestions.

Thanks

[sneakyimp]

Unless both of these databases are live, you could probably dump a backup file of one db and load it onto the other host.

[fnp]

Yeah which I can do but, I am moving one table out of the old and into the new because it fills up to fast. And the old database still contains all the other tables. That is why I am doing this.

[sneakyimp]

So is the old table under constant usage or is it out of commission?

If it's being used, you could shut it down for a day and make the move. the mysqldump command is very handy.

[fnp]

I have already dumped the table over, but still using the old until the scripts are re-written to point to the new table.

[sneakyimp]

well if by 'dump the table over' you mean that it exists on the new server (and therefore the same host) then you'd be able to run the 2dbs/1query thing.

[fnp]

Now you got me all confused, one host is this dbxxxx.perfora.net and the other host is dbxxxx.perfora.net.

[sneakyimp]

If the hosts are the same, you might be in luck. Remember, we're talking about host here, no the db.

Are the bits you've hidden with the x's different or the same? Do they point to the same IP address?

[fnp]

They are different.