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.
在这里有一些斗争,将JOIN添加到查询中。我连接到两个单独的数据库(在同一台服务器上)。出于这个原因,我正在简单地编写这个mysqli,并且一旦它工作就会转换为准备好的语句。
// 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):
没有JOIN的查询工作正常(尽管没有返回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.
以下是我引用的不同数据库名称的列表。正如我所说,一些数据位于“connective_data”数据库中,一些数据位于“资源”数据库中。
$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?
我似乎无法将其中两个连接起来。我错过了一些非常明显的东西吗?
1 个解决方案
#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.
如果数据库位于同一个mysql服务器上,则无需创建2个连接。您可以简单地将另一个数据库中的表作为databasename.tablename引用。
-
As a result, you can join 2 tables from 2 different databases as:
因此,您可以将来自2个不同数据库的2个表连接起来:
$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'";
$ 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.
显然,您需要在上面的查询中用yourdatabase1和yourdatabase2替换真实的数据库名称。
Update: Are you sure you need so many databases? These seem to be tables to me, not databases.
更新:您确定需要这么多数据库吗?这些似乎是我的表,而不是数据库。
#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.
如果数据库位于同一个mysql服务器上,则无需创建2个连接。您可以简单地将另一个数据库中的表作为databasename.tablename引用。
-
As a result, you can join 2 tables from 2 different databases as:
因此,您可以将来自2个不同数据库的2个表连接起来:
$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'";
$ 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.
显然,您需要在上面的查询中用yourdatabase1和yourdatabase2替换真实的数据库名称。
Update: Are you sure you need so many databases? These seem to be tables to me, not databases.
更新:您确定需要这么多数据库吗?这些似乎是我的表,而不是数据库。