MySQL:左连接与左连接

Question

我有以下sql查询

SELECT * FROM jos_jcalpro_events AS e 
  LEFT JOIN jos_jcalpro_events_seats AS s ON e.extid = s.extid
  LEFT JOIN jos_jcalpro_events_types_xref AS t ON e.extid = t.extid

我通过 PHP 中的 PDO 执行。我从中创建了另一个查询以重新插入数据库。但它不起作用。当我将 LEFT 从大写更改为小写时:

SELECT * FROM jos_jcalpro_events AS e 
  left JOIN jos_jcalpro_events_seats AS s ON e.extid = s.extid
  left JOIN jos_jcalpro_events_types_xref AS t ON e.extid = t.extid

在我的创建例程中没有区别，但创建的 INSERT 实际上少了 2 行。怎么可能？

这是代码的重要部分:

    $resource = JcalproDataImport::getInstance();

    // IMPORT
    $pdo = $resource->getConnection('import', 'pdo');
    $events = $pdo->query(
        'SELECT * FROM jos_jcalpro_events AS e '
        .'left JOIN jos_jcalpro_events_seats AS s ON e.extid = s.extid '
        .'left JOIN jos_jcalpro_events_types_xref AS t ON e.extid = t.extid;')->fetchAll(PDO::FETCH_ASSOC);

    // MAPPING
    $map = array(
        'picture'=>null, 'cat'=>null, 'day'=>null, 'month'=>null, 'year'=>null, 'recur_type'=>null, 'checked_out'=>null,
      'checked_out_time'=>null, 'payment_type'=>null, 'id'=>null,
      'extid'=>'id', 'contact'=>'contact_info', 'start_date'=>'start', 'end_date'=>'end', 'recur_val'=>'repeat_period',
      'recur_end_type'=>'repeat_end_type_id', 'recur_count'=>'repeat_end_after_occurrences',
      'recur_until'=>'repeat_end_after_date', 'wp_only'=>'only_in_wp', 'eticket'=>'prevent_sending_eticket',
      'region'=>'region_id', 'onlinebooking'=>'online_booking_form', 'organiser'=>'organiser_name'
    );

    // OUTPUT
    $pdo = $resource->switchConnection('export', 'pdo');
    $pdo->query('SET foreign_key_checks = 0');

    foreach ($events as $values)
    {
        $values['chargeable_status'] = (strtolower($values['payment_type']) == 'p') ? 1 : 0;
        $values['repeat_period_type_id'] = $repeat_period_type_map[$values['recur_type']];
        foreach($map as $from=>$to)
        {
            if ($to!== null){ $values[$to] = $values[$from]; }
            unset( $values[$from] );
        }
        $values['created_at'] = '2008-01-01';
        $values['updated_at'] = $values['created_at'];

          if (!$event) {
              foreach ($values as $field=>$value)
              {
                  $sql .= ', '.$field;
                  $sqlValues .= ', "'.mysql_real_escape_string($value).'"';
              }
              $sql = 'INSERT INTO event ( '.substr($sql, 2).' ) VALUES '.PHP_EOL.'( '.substr($sqlValues, 2).' )';
          }else{
              foreach ($values as $value)
              {
                  $sqlValues .= ', "'.mysql_real_escape_string($value).'"';
              }
              $sql .= ', ( '.substr($sqlValues, 2).' )';
          }
        $event[] = $values;
    }
    $pdo->query($sql)

但它对其他查询工作正常，所以我不敢相信那里有问题。

Answer 1

1. 没有理由 left JOIN 的工作方式与 LEFT JOIN 不同。这不太可能与您的问题有关。

2. 在 SELECT 中使用列别名映射列名。

3. 通过命名不需要的列来省略不需要的列，而不是使用 SELECT *。

4. 使用与列别名匹配的命名参数准备一次 INSERT。然后使用 execute($values) 插入从 SELECT 获取的每一行。

我大概是这样写的:

$map = array(
  'extid'=>'id', 
  'contact'=>'contact_info', 
  'start_date'=>'start', 
  'end_date'=>'end',
  'recur_val'=>'repeat_period', 
  'recur_end_type'=>'repeat_end_type_id', 
  'recur_count'=>'repeat_end_after_occurrences',
  'recur_until'=>'repeat_end_after_date',
  'wp_only'=>'only_in_wp',
  'eticket'=>'prevent_sending_eticket',
  'region'=>'region_id',
  'onlinebooking'=>'online_booking_form',
  'organiser'=>'organiser_name',
  'CURDATE()'=>'created_at',
  'CURDATE()'=>'updated_at',
);

$aliasize = function($alias, $column) { return "$column AS $alias"; }
$select_list = join(",", array_walk($aliasize, $map));

$events = $pdo->query("SELECT $select_list FROM ...")
    ->fetchAll(PDO::FETCH_ASSOC);

$column_list = join(",", array_values($map));
$parameterize = function($alias) { return ":$alias"; }
$param_list = join(",", array_map($parameterize, $map));

$insert = $pdo->prepare("INSERT INTO event ($column_list) VALUES ($param_list)");

foreach ($events as $values) {
    $insert->execute($values);
}