2

Having a bit of a struggle here with adding JOINs to a query. I am connecting to two separate databases (on the same server). For this reason, I am writing this mysqli simply and will convert to a prepared statement once it's working.

// REMOVED: DB VARIABLES

$conn = new mysqli($servername, $username, $password, $db_connective_data);
if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }
$conn2 = new mysqli($servername, $username, $password, $db_resources);
if ($conn2->connect_error) { die("Connection failed: " . $conn2->connect_error); }

$sql = "SELECT * FROM downloads LEFT JOIN resource_data ON downloads.resource_id_REF=resource_data.resource_id WHERE downloads.user_basics_id_REF='$user_id'";
$result = $conn->query($sql);

$number_of_download_rows_returned = mysqli_num_rows($result) -1;

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $resource_id_REF[] = $row['resource_id_REF'];
        $download_date[] = date('Y-m-d', strtotime($row['download_date']));
        $resource_title[] = $row['resource_title'];
        $resource_title_link[] = str_replace(" ", "-", $row['resource_title']);
    }
}

$conn->close();

A query without a JOIN works fine (albeit without returning the resource_title):

$sql = "SELECT * FROM downloads WHERE downloads.user_basics_id_REF='$user_id' ORDER BY downloads.download_date DESC";

What am I missing here? The first code sample will return no results. The second one will return three.

Any assistance is greatly appreciated.

Here is a list of the different database names that I reference. As I stated, some data is in the "connective_data" db and some is in the "resources" db.

$db_connective_data = "connective_data";
$db_lists = "lists";
$db_messaging = "messaging";
$db_resources = "resources";
$db_users = "users";

I can't seem to get two of them connected. Am I missing something strikingly obvious here?

Sheldon Scott
  • 741
  • 1
  • 7
  • 21
  • Why are you making `$conn2` you aren't using it. – Daan Oct 29 '15 at 14:35
  • I wondered the same thing. Is there a way to use it? (I figured I'd need to connect to both databases, no?) lol – Sheldon Scott Oct 29 '15 at 14:37
  • I don't know do you need to use it? – Daan Oct 29 '15 at 14:38
  • 1
    put this after you execute your query: `if(!$result){ echo $conn->error;}` and see what he problem is. Error handling is always important! – Tanuel Mategi Oct 29 '15 at 14:40
  • Possible duplicate of [MySQL -- join between tables in 2 different databases?](http://stackoverflow.com/questions/5698378/mysql-join-between-tables-in-2-different-databases) – Erwin Moller Oct 29 '15 at 14:41
  • if you run your statement with the join directly against the database (using phpmyadmin, for example), do you get results? – devlin carnate Oct 29 '15 at 14:41
  • The resource_id_REF and download_date are in the db_connective_data database. The resource_title is in the db_resources database. I want to grab both using a JOIN. – Sheldon Scott Oct 29 '15 at 14:41
  • Running if(!$result){ echo $conn->error;} gives me an error that the db doesn't exist. But that's the second db which I am trying to connect to (both DO exist). So how do I connect to the two databases? – Sheldon Scott Oct 29 '15 at 14:49
  • What do you mean "directly against the database", @devlincarnate? – Sheldon Scott Oct 29 '15 at 15:00
  • 1
    I mean, using phpmyadmin (or something similar). This would eliminate php and connection issues, and allow you to test the query itself. – devlin carnate Oct 29 '15 at 15:05

1 Answers1

1

  1. There is no need to create 2 connections if the databases are located on the same mysql server. You can simply reference tables from another database as databasename.tablename.

  2. As a result, you can join 2 tables from 2 different databases as:

    $sql = "SELECT * FROM yourdatabase1.downloads LEFT JOIN yourdatabase2.resource_data ON yourdatabase1.downloads.resource_id_REF=yourdatabase2.resource_data.resource_id WHERE yourdatabase1.downloads.user_basics_id_REF='$user_id'";

Obviously, you need to substitute your real database names for yourdatabase1 and yourdatabase2 in the above query.

Update: Are you sure you need so many databases? These seem to be tables to me, not databases.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • It was more of a visual/logical separation for my own brain. Within each of the databases I have somewhere around 20 tables (depending on the db, of course). I could stick all the tables together into one big db but that will fry my already crispy brain! lol Thanks for the answer. I'll try it and get back to you. – Sheldon Scott Oct 29 '15 at 15:30
  • This was exactly what I needed. I was not putting db_name.table_name and was mistakingly putting table_name.row_name instead. Thanks for the help, @Shadow! – Sheldon Scott Oct 29 '15 at 17:07