php - MySQL 加入不适用于可选字段

Question

我在使用内部联接和左联接的 MySQL 查询时遇到问题。问题在于，在允许 NULL 字段的情况下，主表中的外键为空，查询不会读取主表中的所有字段。

我的任务是用 MySQL 组织一个录音列表，其中一些是现场录音，而另一些是录音室录音。为了这个问题的目的，我将表结构简化如下:

table name: recordings
fields:
recording_id INT AUTO-INCREMENT
recording_name VARCHAR NOT NULL
artist_id INT NOT NULL FOREIGN KEY
event_id INT NULL FOREIGN KEY

table name: artists
fields:
artist_id INT AUTO-INCREMENT
artist_name VARCHAR NOT NULL

table name: events
fields:
event_id INT AUTO-INCREMENT
event VARCHAR NOT NULL
venue_id INT NOT NULL FOREIGN KEY

table_name: venues
fields:
venue_id INT AUTO-INCREMENT
venue_name VARCHAR NOT NULL
address VARCHAR NOT NULL

如果是现场录制，我希望可以选择提供录制完成的事件的详细信息，如果是录音室录制，我将事件字段留空。换句话说，在 recordings 表中，event_id 是一个可选字段，但 artist_id 始终是必需的。

要编辑 recordings 表中的现有记录，我有一个包含三个字段的表单(再次简化):

<form>
  <input name="recording_name" type="text" value="<?php $recording_name ?>" />
  <select name="artist_id">
     <option>Select option</option>
     <option <?php $selected ?> value="1">Artist 1</option>
     etc.
  </select>
  <select name="event_id">
     <option>Select option</option>
     <option <?php $selected ?> value="1">Venue 1</option>
     etc.
  </select>  
</form>

我使用 $selected 变量在表单的下拉列表中显示与从数据库中提取的现有值相对应的选项，如下所示:

$selected = ($existing_value == $option_id ? 'selected="selected" : '');

现在，要获取表单的现有值，我有以下 SQL 查询:

$recording_sql = 
'SELECT * FROM recordings 
INNER JOIN artists ON recordings.artist_id = artists.artist_id 
LEFT JOIN events ON recordings.event_id = events.event_id
LEFT JOIN venues ON events.venue_id = venues.venue_id'

然后，填充两个下拉列表:

$artist_sql = 
'SELECT * FROM artists'

$event_sql = 
SELECT * FROM events 
INNER JOIN venues ON events.venue_id = venues.venue_id

我的 PHP 代码看起来像这样:

function buildForm($result){
  $data = $result->fetch_array($MYSQLI_ASSOC))
  $form = '<input name="recording_id" type="hidden" value="'.$data['recording_id'].'" />';
  $form .='<input name="recording_name" type="text" value="'.$data['recording_name'].'" />';
  $form .= buildSelectBox('artists', $data['artist_id']);
  $form .= buildSelectBox('events', $data['event_id']);
  return $form;
}

function buildSelectBox($table, $existing_id = NULL){
  //run SQL to pull data from relevant table (i.e. $artist_sql, or $event_sql which includes join to 'venues')
  //Loop through $mysqli_result to build each option
  while(etc....){
    $selected = ($existing_id == $option_id ? 'selected="selected" : '');
    $options_list .= '<option'.$selected.'value="'.$id.'">'.$artist_name.'</option>';
  }
  return $options_list;
}

如果两个外键都有值，这就可以正常工作。但是，当 recordings 中的 event_id 字段为空时，它也不会读取另一个外键。它读取文本字段 recording_name，但没问题。换句话说，我为 $recordings_sql 获得的结果集仅包含 recording_name 字段的值，而两个外键都返回空白，即使其中一个不是空白。我已经尝试了不同组合的所有连接排列(左、内、右)，但没有一个给出预期的结果。

我被难住了!提前感谢您的帮助!

Answer 1

我昨天大部分时间都在尝试使用我的应用程序的简化版本重现该问题，最终我做到了。事实证明，第一个外键(在我的简化示例中为 artist_id)在以第二个外键(在我的简化示例中为 event_id)开始的一系列连接中的另一个表中重复出现。所以，我的猜测是，如果 'event_id' 为空，'artist_id' 将被空值覆盖，因为如果 'event_id' 为空，查询将为 'event_id' 之后的所有内容返回空值。因此，问题不在于 LEFT JOIN 的工作方式，而在于子表中第一个外键的重复，该子表依赖于允许为空的第二个外键。我显然需要重新审视我的表结构...

非常感谢所有的建议!