mulitiple database select php mysql

Question

I have 2 databases on the same server with 2 identical tables.

What I want to do is select all records from both tables and join them in one array. I've been messing around with the script below. For some reason it returns the records of db2.tbl twice and doesn't return the db1.tbl records at all. When I try to select the data from a single database is works fine for both of them. Does any one see the problem?

<?PHP
 require_once("config.php");

$conn = @mysql_connect($dbhost, $dbuser, $dbpass)or die ('Error connecting to mysql server'.mysql_error());
$q = mysql_query("SELECT * FROM db1.tbl JOIN db2.tbl");
var_dump(mysql_num_rows($q));
while($arr = mysql_fetch_assoc($q)){
    var_dump($arr);
}

?>

pritaeas · Accepted Answer · 2012-07-30 12:28:15Z

1

Is this what you want ? All records from database1 followed by all records from database2:

$q = mysql_query("SELECT * FROM db1.tbl UNION SELECT * FROM db2.tbl");

I assume the user you are connecting with has access to both databases.

answered Jul 30, 2012 at 12:28

pritaeas

2,0935 gold badges37 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Berkowski Over a year ago

Never SELECT * in a UNION, particularly if you don't know the tables have equivalent columns (since the OP didn't say they do)

pritaeas Over a year ago

He said so in the first line ;) But I agree you should be explicit.

user1446279 Over a year ago

Thanx that is exaclty what i needed. I will keep in mind that for future projects a SELECT * is a no no ;), but for this project it works fine like this.

ARIF MAHMUD RANA · Accepted Answer · 2012-07-30 12:37:08Z

0

Your query should work. However add tilt(`) to your database name table name. Execute the query first in mysql see whether it is ok than execute with php.

answered Jul 30, 2012 at 12:37

ARIF MAHMUD RANA

5,2063 gold badges39 silver badges58 bronze badges

Collectives™ on Stack Overflow

mulitiple database select php mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related