Mysqli join tables from 2 different databases

Viewed 98 times

How Can I use this 2 connections to run a query which joins 2 tables? yesno.table1 and sushi.table1 ? lets say we join by id they both have the same id. how can I do that?

// Connect to Yesno Database
$this->yesno_db = new mysqli("red", "yesno", "***", "yesnotmp");

if ($this->yesno_db->connect_errno) {
    throw new Exception('Connection failed: '.$this->yesno_db->connect_error);
}   

// Connect to Sushi Database
$this->sushi_db = new mysqli("red", "sushi", "***", "sushi");

if ($this->sushi_db->connect_errno) {
    throw new Exception('Connection failed: '.$this->sushi_db->connect_error);
}

Answers

This question has nothing to do with mysqli (or any other API).

To do a join with a table from different database, a user which connects to mysql, have to have SELECT rights for both databases.

Having this done, just select one of databases in your connection statement and address another using usual dot syntax:

SELECT * FROM t1 JOIN db2.t2

To answer your question literally,

Can I use this 2 connections to run a query which joins 2 tables?

You can't

Tuesday, December 20, 2022

mitya.podgornov.92-8220b47d7142

While the main problem is that you missed the closing bracket after bookamandheading, still I would like to advise you to refactor this request for example like this:

$keys = array("bookabandheading", "bookaband", "contactus", "aboutuslisten",
              "contactusheading", "nightclubsheading", "acousticheading",
              "schoolsheading", "privateheading", "concertsheading",
              "festivalsheading", "submissions", "interns", "managementbio",
              "latestnews", "artistofthemonth", "artistofthemonthphoto",
              "artistofthemonthid", "listentoourartists", "musicianswanted",
              "aboutus", "bshowcases", "bandavails");
$set = array();
foreach ($keys as $key) {
    $set[] = sprintf(" %s = '%s' ", $key, mysql_escape_string($_POST[$key]));
}
$sql = mysql_query("UPDATE general SET " . implode(", ", $set));

It is much easier to maintain and also a bit more secure by escaping the input.

Update: add where statement example

$where = array();
$where[] = sprintf(" some_string = '%s' ", mysql_escape_string($some_string));
$where[] = sprintf(" some_integer = %d ", $some_integer);
$where = " WHERE " . implode(" AND ", $where);
$sql = mysql_query("UPDATE general SET " . implode(", ", $set) . " " . $where);

Friday, August 5, 2022

freesoft

I believe you are looking for the following syntax:

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement

Monday, September 26, 2022

eric_watt

You can use IN:

SELECT t1.*, t2.* 
FROM T1 
INNER JOIN 
(SELECT * FROM atable 
IN 'C:DocsDB2.mdb') t2
ON t1.ID=t2.ID

EDIT:

sc = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:docsother.mdb"
cn.open sc

s="SELECT * FROM t1 INNER JOIN " _
& "[MS Access;PWD=databasePWD;DATABASE=C:docsdb.mdb].t2 ON t1.ID=t2.ID"

rs.Open s, cn

EDIT 2:

You can use the aliases to identify which database a field is from:

s="SELECT * FROM table1 t INNER JOIN " _
& "[MS Access;PWD=databasePWD;DATABASE=C:docsdb.mdb].m ON t.ID=m.ID"

 msgbox rs.fields("m.code") & " " & rs.fields("t.code")

EDIT 3

Or you can add a virtual field:

 SELECT 1 AS "DB", Field, Field FROM  ...
 UNION ALL
 SELECT 2 AS "DB", Field, Field FROM

UNION ALL is usually faster.

Saturday, November 12, 2022

ashatte

You could call it just style, but I prefer aliasing to improve readability.

UPDATE A    
  SET ControllingSalesRep = RA.SalesRepCode   
from DHE.dbo.tblAccounts A
  INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA
    ON A.AccountCode = RA.AccountCode

For MySQL

UPDATE DHE.dbo.tblAccounts A 
  INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA 
      ON A.AccountCode = RA.AccountCode 
SET A.ControllingSalesRep = RA.SalesRepCode

Tuesday, November 22, 2022

amw_5g

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

sql

Mysqli join tables from 2 different databases

Answers

php,mysql,sql