MYSQL - SELECT from different databases

Question

How can I select data in the same query from two different databases into the same server? This is what I'm doing, but my query doesn't works:

$sqlquery = "SELECT * FROM database_2.table_2 WHERE database_1.table_1.data_1 LIKE database_2.table_2.data_2";

$result = mysql_query($sqlquery);
$number = mysql_numrows($result);

$i = 0;
if ($number < 1) {
  print "DOH";

}else{
  while ($number > $i) {
    $content = mysql_result($result,$i,"database_2.table_2.data_3");
    print "$content";
    $i++;
  }
}

define " doesn't work"? errors? you don't actully check if your SELECT query works or produces an error — user557846
– user557846, Commented Aug 27, 2013 at 23:11
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /web/htdocs/...etc on line 88 [$number = mysql_numrows($result);] — user1472878
– user1472878, Commented Aug 27, 2013 at 23:18
why the like condition? can't you simply use = (it's cheaper) — Barranka
– Barranka, Commented Aug 27, 2013 at 23:23
And running the same query in any MySQL shell (or phpMyAdmin) would work? — Nico Haase
– Nico Haase, Commented Sep 28, 2023 at 13:49

mkf · Accepted Answer · 2013-08-27 23:35:11Z

5

The problem is not about different databases.

Your WHERE clause references the field database_1.table_1.data_1 which was not supplied in the FROM clause.

Didn't you mean something like

SELECT * 
FROM database_2.table_2 
JOIN database_1.table_1 
     ON (database_2.table_2.some_field = database_1.table_1.some_other_field) 
WHERE database_1.table_1.data_1 LIKE database_2.table_2.data_2

?

Also,

echo mysql_error();

after your failed query - this will give you a clue about what's wrong.

edited Aug 27, 2013 at 23:35

answered Aug 27, 2013 at 23:28

mkf

7103 silver badges12 bronze badges

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

2 Comments

user1472878 Over a year ago

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

mkf Over a year ago

SELECT * FROM database_2.table_2 JOIN database_1.table_1 ON (database_2.table_2.some_field = database_1.table_1.some_other_field) WHERE database_1.table_1.data_1 LIKE database_2.table_2.data_2 COLLATE utf8_general_ci

echo_Me · Accepted Answer · 2013-08-27 23:28:33Z

0

try this

    SELECT * FROM database_2.table_2 t2 INNER JOIN database_1.table_1 t1 
    ON  t1.data_1 = t2.data_2

answered Aug 27, 2013 at 23:28

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

MYSQL - SELECT from different databases

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related