ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Changeset 6182


Ignore:
Timestamp:
Mar 31, 2008, 9:20:35 AM (13 years ago)
Author:
toraneko
Message:

トピックコメント更新日時カラム追加 最新トピック書き込みSQLの変更

File:
1 edited

Legend:

Unmodified
Added
Removed
  • OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/lib/db/commu.php

    r5885 r6182  
    525525                " order by r_datetime desc ";
    526526    } else {
    527         $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id " .
    528                 " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 " .
    529                 " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" .
    530                 " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " .
    531                 " AND cct.event_flag = ?".
    532                 " AND cct.c_commu_id = ?".
    533                 " group by cct.c_commu_topic_id " .
     527        $sql = "SELECT c_commu_topic_id , name, r_datetime_comment as r_datetime , c_commu_id " .
     528                " FROM c_commu_topic" .
     529                " WHERE event_flag = ? AND c_commu_id = ?".
    534530                " order by r_datetime desc ";
    535531    }
     
    539535    foreach ($list as $key => $value) {
    540536        $list[$key]['count_comments'] = db_commu_get_max_number4topic($value['c_commu_topic_id']);
     537        $last_comment = db_commu_get_last_c_topic_comment($value['c_commu_topic_id']);
     538        $list[$key]['image_filename1'] = $last_comment['image_filename1'];
     539        $list[$key]['image_filename2'] = $last_comment['image_filename2'];
     540        $list[$key]['image_filename3'] = $last_comment['image_filename3'];
    541541    }
    542542    return $list;
     
    842842            ' ORDER BY r_datetime DESC';
    843843    } else {
    844         $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
    845             ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
    846             ' GROUP BY cctc.c_commu_topic_id'.
     844        $sql = 'SELECT c_commu_topic_id, c_commu_id, r_datetime_comment as r_datetime, c_member_id,'.
     845            ' name as c_commu_topic_name'.
     846            ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') '.
    847847            ' ORDER BY r_datetime DESC';
    848848    }
     
    853853        $c_commu_topic_list[$key]['nickname'] = $c_member['nickname'];
    854854
    855         $sql = 'SELECT number, image_filename1, image_filename2, image_filename3' .
    856                ' FROM c_commu_topic_comment' .
    857                ' WHERE c_commu_topic_id = ? AND r_datetime = ?';
    858         $params = array(intval($value['c_commu_topic_id']), $value['r_datetime']);
    859         $temp = db_get_row($sql, $params);
     855        $temp = db_commu_get_last_c_topic_comment($value['c_commu_topic_id']);
    860856
    861857        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
    862858        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
    863 
    864         $sql = 'SELECT name FROM c_commu_topic WHERE c_commu_topic_id = ?';
    865         $c_commu_topic_name = db_get_one($sql, $value['c_commu_topic_id']);
    866859
    867860        //最新の書き込み番号
     
    873866        $c_commu_topic_list[$key]['image_filename3'] = $temp['image_filename3'];
    874867        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    875         $c_commu_topic_list[$key]['c_commu_topic_name'] = $c_commu_topic_name;
    876868    }
    877869
     
    905897            ' ORDER BY r_datetime DESC';
    906898    } else {
    907         $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
    908             ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
    909             ' GROUP BY cctc.c_commu_topic_id'.
     899        $sql = 'SELECT c_commu_topic_id, c_commu_id, r_datetime_comment as r_datetime, c_member_id,'.
     900            ' name as c_commu_topic_name'.
     901            ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ')'.
    910902            ' ORDER BY r_datetime DESC';
    911903    }
     
    916908        $c_commu_topic_list[$key]['nickname'] = $c_member['nickname'];
    917909
    918         $sql = 'SELECT number, image_filename1, image_filename2, image_filename3' .
    919                ' FROM c_commu_topic_comment ' .
    920                ' WHERE c_commu_topic_id = ? AND r_datetime = ?';
    921         $params = array(intval($value['c_commu_topic_id']), $value['r_datetime']);
    922         $temp = db_get_row($sql, $params);
     910        $temp = db_commu_get_last_c_topic_comment($value['c_commu_topic_id']);
    923911
    924912        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
    925913        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
    926 
    927         $sql = 'SELECT name FROM c_commu_topic WHERE c_commu_topic_id = ?';
    928         $c_commu_topic_name = db_get_one($sql, $value['c_commu_topic_id']);
    929914
    930915        //最新の書き込み番号
     
    936921        $c_commu_topic_list[$key]['image_filename3'] = $temp['image_filename3'];
    937922        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    938         $c_commu_topic_list[$key]['c_commu_topic_name'] = $c_commu_topic_name;
    939923    }
    940924
     
    25042488        'r_datetime' => db_now(),
    25052489        'r_date' => db_now(),
     2490        'r_datetime_comment' => db_now(),
    25062491    );
    25072492
     
    25952580        'r_datetime'  => db_now(),
    25962581        'r_date'      => db_now(),
     2582        'r_datetime_comment'  => db_now(),
    25972583    );
    25982584
     
    26282614{
    26292615    cache_drop_c_commu_list4c_member_id($comment['c_member_id']);
     2616
     2617    db_commu_update_c_commu_topic_r_datetime_comment(intval($comment['c_commu_topic_id']));
    26302618
    26312619    $data = array(
     
    29442932    } else {
    29452933        $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image'
    2946                 . ', ct.*, MAX(ctc.r_datetime) AS max_datetime';
     2934                . ', ct.*, ct.r_datetime AS max_datetime';
    29472935    }
    29482936
     
    30042992        $sql = 'SELECT body FROM c_commu_topic_comment WHERE number = 0 AND c_commu_topic_id = ?';
    30052993        $list[$key]['body'] = db_get_one($sql, $p);
    3006         $sql = 'SELECT MAX(number) FROM c_commu_topic_comment WHERE c_commu_topic_id = ?';
    3007         $list[$key]['max_number'] = db_get_one($sql, $p);
     2994        $number = db_commu_get_max_number4topic($value['c_commu_topic_id']);
     2995        $list[$key]['max_number'] = $number;
    30082996    }
    30092997
     
    30233011}
    30243012
     3013/*
     3014 * トピック書き込み時間(r_datetime_comment)を更新する
     3015 */
     3016function db_commu_update_c_commu_topic_r_datetime_comment($c_commu_topic_id)
     3017{
     3018    $data = array(
     3019        'r_datetime_comment' => db_now(),
     3020    );
     3021    $where = array(
     3022        'c_commu_topic_id' => intval($c_commu_topic_id),
     3023    );
     3024    return db_update('c_commu_topic', $data, $where);
     3025}
     3026
     3027/*
     3028 * 最終書き込みコメントの情報取得
     3029 */
     3030function db_commu_get_last_c_topic_comment($c_commu_topic_id)
     3031{
     3032    $sql = 'SELECT * FROM c_commu_topic_comment WHERE c_commu_topic_id = ? ORDER BY r_datetime DESC';
     3033    $params = array(intval($c_commu_topic_id));
     3034    return db_get_row($sql,$params);
     3035}
     3036
    30253037?>
Note: See TracChangeset for help on using the changeset viewer.