-2

I am trying to connect to 2 databases on the same query of MySQL. but query doesn't work... I do not know where I'm doing wrong.. Any ideas? 

$link1 = mysqli_connect("localhost","username1","pass1","db1");
$link2 = mysqli_connect("localhost","username2","pass2","db2");

@$sql = mysqli_query("SELECT db1.* FROM db1.videos WHERE db1.VID NOT IN (SELECT VID FROM db2.videos)");
gokhan
  • 665
  • 2
  • 9
  • 16
  • 2
    Start by looking at the manual for the [mysqli_query() command](http://php.net/manual/en/mysqli.query.php) Thats what the manual is for. Start by counting the number of parameters used in the manual examples and then count yours – RiggsFolly Jul 02 '18 at 18:07
  • 5
    If you weren't suppressing errors with `@`, you might get one about a missing argument to mysqli_query. Don't suppress errors! – Devon Bessemer Jul 02 '18 at 18:08
  • 1
    You're missing the first argument to `mysqli_query()`, which should be either `$link1` or `$link2`. – Barmar Jul 02 '18 at 18:08
  • 2
    And checking for error's is **always a good idea** Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. – RiggsFolly Jul 02 '18 at 18:09
  • 2
    1. Don't suppress errors using `@` since that _hides_ error messages that you need when you debug your code. 2. Two different connections can't be used together like that. 3. The call to `mysqli_query()` is wrong. (read the manual). – M. Eriksson Jul 02 '18 at 18:09
  • You don't need to make two different connections. The DB in the connection is just the default DB, but you can override it by writing `db2.` in the query. – Barmar Jul 02 '18 at 18:09
  • You need to provide a connection when using mysqli_query function, which lets you connect to that DB only. You can get results from 2 different queries ran for each DB and use the both resultsets to get actually what you want. – Erhan Jul 02 '18 at 18:10
  • @RiggsFolly Yeah, didn't notice that he was using different accounts until after I commented. – Barmar Jul 02 '18 at 18:10

1 Answers1

1

You can only use one connection for a particular query, and it has to be provided as the first argument to mysqli_query(). If you want to access both databases in the same query, you need to use a connection with a username that has access to both databases. You don't need to use a different connection just because you're accessing different databases -- the database in the connection is just the initial default, but you can change it with mysqli_select_db(), and override it temporarily in a query by using the database prefix on the table name.

You can't refer to db1.* in a query, you have to specify the table name db1.videos.*

So if username1 has permission to read both db1 and db2, you can do:

$sql = mysqli_query($link1, "SELECT db1.videos.* FROM db1.videos WHERE db1.VID NOT IN (SELECT VID FROM db2.videos)");
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks, That cleared it up, I was having issues putting it in to sImple words :) – RiggsFolly Jul 02 '18 at 18:22
  • Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'SELECT command denied to user 'database1'@'localhost' username1 not permission to read both databases. :( – gokhan Jul 02 '18 at 18:39
  • [code]$sql = mysqli_query($link1, "SELECT * FROM video LIMIT 5");[code] like this work but i need WHERE VID NOT IN (SELECT VID FROM db2.video) filter.... – gokhan Jul 02 '18 at 18:48
  • Like I said, you need a single user that has access to both databases. – Barmar Jul 02 '18 at 18:52
  • [code] doesn't work in comments, use backticks. – Barmar Jul 02 '18 at 18:52