MySQL：离开JOIN vs. LEFT JOIN

I have the following sql query

我有以下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

which I execute via PDO from PHP. I create out of this another query to reinsert in the database. But it doesn't work. When I change LEFT from upper to lower case it does:

我通过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

In my creating routine there is no difference but the created INSERT has actually 2 lines less. How can that be?

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

Here is the important part of the code:

这是代码的重要部分:

    $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)

But it works fine for other queries, so that I can't believe in a problem there.

但它适用于其他查询,所以我不能相信那里的问题。

1 个解决方案

#1

1. There is no reason left JOIN would work differently than LEFT JOIN. That's very unlikely to be related to your problem.

   没有理由让JOIN与LEFT JOIN的工作方式不同。这不太可能与你的问题有关。

2. Do your column name mapping with column aliases in your SELECT.

   在SELECT中使用列别名进行列名映射。

3. Omit columns you don't want by naming those columns you do want, instead of using SELECT *.

   通过命名您想要的列而不是使用SELECT *来省略您不想要的列。

4. Prepare the INSERT once, with named parameters matching the column alias names. Then use execute($values) to insert each row fetched from the SELECT.

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

Here's approximately how I'd write it:

这是我写的方式:

$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);
}