php - LEFT JOIN 打印 Table1 中的第一条记录,然后打印 Table2 中的任何/所有连接记录,然后打印 Table1 中的第二条记录,依此类推
问题描述
此代码为 table1 中的每条记录返回 table2 中的一行。我是否需要创建两个 SELECT 语句来打印 table2 中的所有连接行?
<?php
$sql = new MySQLi(localhost, x, y, z);
$result = $sql->query("SELECT ARTICLES.ID,ARTICLES.ARTICLE,ARTICLES.TYPE,ARTICLES.YEARLABEL,ARTICLES.ZERO,ARTICLES.TXYZ,ARTICLES.EDGES,ARTICLES.ABSTRACT,ARTICLES.DEVELOPMENTS,ARTICLES.CHERRYPICKER,ARTICLES.CONTRACTIONS,ARTICLES.WORKS,ARTICLES.RESOURCE,ASPECTS.CLASS,ASPECTS.ASPECT,ASPECTS.ARESOURCE
FROM ARTICLES
LEFT JOIN ASPECTS ON ARTICLES.ID =
ASPECTS.LID
WHERE ARTICLES.TYPE LIKE '%Composite%'
ORDER BY ZERO ASC, ARTICLE ASC");
for ($set = array (); $row = $result->fetch_assoc(); $set[] = $row);
print nl2br(print_r($set, true));
?>
如果 table2 中有多个条目,则输出应如下所示:
1 =>
(
[ID] => 223
[ARTICLE] => Hero of Alexandria (Heron)
[TYPE] => Composite
[YEARLABEL] => AD
[ZERO] => 10
[TXYZ] => 10-70 circa Roman Egypt
[ELEMENTS] =>
[EDGES] => invention, math, mensuration, optics
[ABSTRACT] => Greek scientist whose interests included optics..
[DEVELOPMENTS] => aeolipile (may have been described a century earlier)
[CHERRYPICKER] => The aeolipile Hero described is considered to be the first recorded steam engine or reaction steam turbine.
[CONTRACTIONS] =>
[WORKS] =>
[RESOURCE] =>
Array (table2:ASPECTS)
0=>
'AID' => 101
'CLASS' => Hero's method
'ASPECT' => An iterative method of approximating the square root of a number.
'AREOURCE' => null
1=>
'AID' => 102
'CLASS' => Hero's formula
'ASPECT' => In geometry, formula gives the area of a triangle by requiring no arbitrary choice of side as base or vertex as origin, contrary to other formulas for the area of a triangle, such as half the base times the height or half the norm of a cross product of two sides.
'AREOURCE' => null
SHOW CREATE TABLE `ARTICLES` //Table1
ARTICLES CREATE TABLE `ARTICLES` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ARTICLE` longtext COLLATE utf8mb4_unicode_ci,
`TYPE` longtext COLLATE utf8mb4_unicode_ci,
`YEARLABEL` longtext COLLATE utf8mb4_unicode_ci,
`ZERO` float DEFAULT NULL,
`TXYZ` longtext COLLATE utf8mb4_unicode_ci,
`ELEMENTS` longtext COLLATE utf8mb4_unicode_ci,
`EDGES` longtext COLLATE utf8mb4_unicode_ci,
`ABSTRACT` longtext COLLATE utf8mb4_unicode_ci,
`DEVELOPMENTS` longtext COLLATE utf8mb4_unicode_ci,
`CHERRYPICKER` longtext COLLATE utf8mb4_unicode_ci,
`CONTRACTIONS` longtext COLLATE utf8mb4_unicode_ci,
`WORKS` longtext COLLATE utf8mb4_unicode_ci,
`RESOURCE` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=9198 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SHOW CREATE TABLE `ASPECTS`/Table2
ASPECTS CREATE TABLE `ASPECTS` (
`AID` int(11) NOT NULL AUTO_INCREMENT,
`LID` int(11) DEFAULT NULL,
`CLASS` longtext COLLATE utf8mb4_unicode_ci,
`ASPECT` longtext COLLATE utf8mb4_unicode_ci,
`ARESOURCE` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`AID`)
) ENGINE=MyISAM AUTO_INCREMENT=5355 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
这是我搜索后能找到的最接近的代码。SQL 执行。PHP没有。
<?php
include('mysqliconfig.php');
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Error " . mysqli_error($conn));
$result = mysqli_query($conn, SELECT ARTICLES.ID,ARTICLES.ARTICLE,ARTICLES.TYPE,ARTICLES.YEARLABEL,ARTICLES.ZERO,ARTICLES.TXYZ,ARTICLES.ELEMENTS,ARTICLES.EDGES,ARTICLES.ABSTRACT,ARTICLES.DEVELOPMENTS,ARTICLES.CHERRYPICKER,ARTICLES.CONTRACTIONS,ARTICLES.WORKS,ARTICLES.RESOURCE, ASPECTS.CLASS,ASPECTS.ASPECT,ASPECTS.ARESOURCE,ASPECTS.AID as NID FROM ARTICLES
LEFT JOIN ASPECTS ON ARTICLES.ID = ASPECTS.LID
WHERE TYPE = 'Composite'
OR TYPE = 'Composite[d]'
OR TYPE = 'Composite[t]'
ORDER BY ARTICLES.ZERO, ASPECTS.AID");
$array = array('ASPECTS'=>array());
$i=0;
$lastid = null;
while($row=mysqli_fetch_object($result))
{
if($lastid!==$row->ID)
{
$array['ARTICLES'][++$i]=array('ARTICLE'=>$row->time,'TYPE'=>$row->name,'LINKED'=>array());
$lastid=$row->ID;
}
if($row->AID!==null)
{
$array['ASPECTS'][$i]['LINKED'][]=array('CLASS'=>$row->title,'ASPECT'=>$row->short_desc,'ARESOURCE'=>$row->desc);
}
}
?>
解决方案
推荐阅读
- android - Kotlin 协程 - 如果一段时间后第一个任务没有完成,则启动另一个任务
- reactjs - 从多个后端加载翻译
- php - PHP SQL On update 从同名的其他行中删除以前的列数据
- vbscript - VBS 将文本复制到剪贴板,不带引号
- java - java.lang.NoClassDefFoundError:解析失败:Lcom/google/android/gms/common/util/zzt;
- javascript - TypeError:无法读取 Redux Todo 应用程序中未定义的属性“映射”
- dart - Dart:我如何获得 Stream 的长度?
- html - 更改引导模式消息正文中的字体大小
- azure - Azure AD 未重定向到 MDM 使用条款 URL
- google-cloud-platform - 获取 Terraform google_monitoring_notification_channel 资源的 Slack auth_token