php页面循环因数据过多造成溢出
从数据库中取出数据过多达到奖金8万条，以至于select抽出至页面循环之际造成溢出问题，
尝试了limit和offset等或许是方法错误等等依然无法解决，想请教如何解决这一问题，谢谢，部分关联代码如下
页面部分如下:
<?php
$search_cond = new stdClass();
foreach ($target_student->app_list as $app_info) {
$search_cond->app_id_list[] = $app_info->id;
}
$search_cond->end_date = date('Y-m-d', strtotime('-1 day'));
$search_cond->order = 'Ymd_desc';
$lessons = schedule::get_list($search_cond);
?>
<tbody>
<?php foreach ($lessons as $lesson_info) :
$info = schedule::get_info_by_id($lesson_info->id);
$t = strtotime($info->date);
$reserveDay = date("Y年m月d日", $t);
$weekjpn = date('D', $t);
?>
<tr>
<td>
<p class="reservedayandtime"><?php echo $reserveDay; ?>（<?php echo $weekjpn; ?>）<br><?php echo $info->start_time; ?>-<?php echo $info->end_time; ?></p>
<?php if ($info->student_canceled) : ?>
<span class='red'>ご欠席</span>
<?php if ($info->absent_student == LSA_ABSENT_WITHOUT_NOTICE) : ?>
<span class='red'>（ご連絡なし）</span>
<?php endif; ?>
<?php elseif ($info->tutor_canceled) : ?>
<span class='red2'>講師によるキャンセル</span>
<?php else :
$replaced_lesson_id = $info->replaced_lesson_id;
if (isset($replaced_lesson_id)) :
echo $word->get('msg_replaced_by'); ?>
<p><?php echo $word->get('ymd'); ?>: <?php echo $info->replaced_date; ?></p>
<p><?php echo $word->get('time'); ?>: <?php echo $info->replaced_start_time; ?>～<?php echo $info->replaced_end_time; ?></p>
<p><?php echo $word->get('tutor'); ?>: <?php echo $info->replaced_tutor_dispname; ?></p>
<?php endif ; ?>
<?php endif; ?>
</td>
<td><?php echo $info->tutor_dispname; ?></td>
<td><?php echo $info->report_for_student; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
数据库部分如下
static function get_list($search_condition, $only_schedule_ready = 1, $classfied = false, $bln_first_last_lesson = false, $open_status = false)
{
$sql_cond = self::get_condition_sql_prepared($search_condition, $open_status, $only_schedule_ready);
$list = array();
/** @var PDO pdo */
$pdo = null;
try {
database::Connect($pdo);
} catch (Exception $e) {
throw $e;
}
try {
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT LSN.id_c as id,
LSN.app_id_c as app_id,
LSN.date_c as date,
LSN.start_time_c as start_time,
LSN.end_time_c as end_time,
LSN.form_c as form, LSN.state_c as lesson_state, LSN.actual_minutes_c as actual_minutes, LSN.note_c as lesson_note,
LSN.hourly_wage_c, LSN.transportation_expenses_c, LSN.report_for_student_c, LSN.absent_student_c, LSN.note_for_tutors_c,
LSN.student_id_c as student_id, LSN.ready_datetime_c as ready_datetime, LSN.standby_c, LSN.standby_datetime_c,
LSN.ready_online_c as ready_online, LSN.ready_online_datetime_c as ready_online_datetime,
LSN.skill_type_c as skill_type,
LRR.status_c as request_status,
LRR.confirmed_lesson_id_c as confirmed_lesson_id,
TTR.dispname_c as tutor_dispname, TTR.name_c as tutor_name, TTR.id_c as tutor_id, TTR.code_c as tutor_code,
TTR.available_c as tutor_available, TTR_C.email_c as tutor_delivery_email, TTR_C.tel_c as tutor_tel, TTR_C.tel_mobile_c as tutor_tel_mobile,
TTR.user_id_c as user_id,
TTR_A.zipcode_c as tutor_zipcode, CONCAT(TTR_A.prefecture_c, TTR_A.city_c, TTR_A.address1_c) as tutor_address,
TTD.zoom_url_c as tutor_zoom_url,
CLI.id_c as client_id, CLI.name_c as client_name,
CLI.department_c as department,
CR.id_c classroom_id,
CR.name_c as classroom_name,
CR.area_c as area,
APP_C_emer.name_c as app_emer_contact_name,
APP_C_emer.relation_c as app_emer_contact_relation,
APP_C_emer.tel_c as app_emer_contact_tel,
APP_C_emer.email_c as app_emer_contact_email,
APP_C_emer2.name_c as app_emer2_contact_name,
APP_C_emer2.relation_c as app_emer2_contact_relation,
APP_C_emer2.tel_c as app_emer2_contact_tel,
APP_C_emer2.email_c as app_emer2_contact_email,
APP.name_c as lesson_name,
APP.name_romaji_c as lesson_name_romaji,
APP.type_c as lesson_type,
APP.lesson_language_c,
APP.lesson_category_c,
APP.lesson_unit_price_c,
APP.estimated_time_c lesson_estimated_time,
APP.number_of_people_c number_of_people,
ADR.prefecture_c, ADR.city_c, ADR.address1_c, ADR.other_c as location_other, ADR.transportation_c, ADR.nearest_station_c,
ADR.prefecture_en_c, ADR.city_en_c, ADR.address1_en_c, ADR.other_en_c as location_other_en, ADR.transportation_en_c, ADR.nearest_station_en_c,
(SELECT sum(actual_minutes_c) FROM lesson_t L
where L.app_id_c = APP.id_c and ( L.end_datetime_c < LSN.start_datetime_c) AND L.state_c & " . LSN_CANCELED . " = 0) total_minutes_so_far
,LSN.replaced_lesson_id_c as replaced_lesson_id,
TLSN.status_c as trial_lesson_status,
ZM.id_c as zoom_id,
ZM.client_url_c as zoom_client_url,
ZM.tutor_url_c as zoom_tutor_url,
(
SELECT LRR.id_c FROM lesson_reserve_request_t LRR
WHERE LRR.status_c = 1 AND FIND_IN_SET(LSN.id_c, LRR.lesson_ids_c)
) AS lesson_reserve_request_id
FROM " . self::$table_name . " LSN
LEFT JOIN tutor_t as TTR ON TTR.id_c = LSN.tutor_id_c
LEFT JOIN tutor_detail_t as TTD ON TTD.tutor_id_c = TTR.id_c
LEFT JOIN contact_t as TTR_C ON TTR_C.id_c = TTR.contact_id_c
LEFT JOIN address_t as TTR_A ON TTR_A.id_c = TTR_C.address_id_c
LEFT JOIN application_t as APP ON APP.id_c = LSN.app_id_c
LEFT JOIN contact_t as APP_C_emer ON APP.emergency_contact_id_c = APP_C_emer.id_c
LEFT JOIN contact_t as APP_C_emer2 ON APP.emergency2_contact_id_c = APP_C_emer2.id_c
LEFT JOIN address_t ADR ON ADR.id_c = LSN.location_address_id_c
LEFT JOIN client_t as CLI ON CLI.id_c = APP.client_id_c
LEFT JOIN classroom_t as CR ON CR.id_c = LSN.classroom_id_c
LEFT JOIN trial_lesson_t as TLSN ON LSN.trial_lesson_id_c = TLSN.id_c
LEFT JOIN zoom_meeting_t as ZM ON LSN.id_c = ZM.lesson_id_c
LEFT JOIN lesson_reserve_request_t as LRR ON LSN.id_c = LRR.confirmed_lesson_id_c
" . $sql_cond->sql;
$del = $pdo->prepare($sql);
foreach ($sql_cond->params as $value) {
$del->bindValue($value->parameter, $value->value, $value->data_type);
}
$del->execute();
$b_exists_app = false;
$str_where = null;
while ($row = $del->fetch(PDO::FETCH_ASSOC)) {
$b_exists_app = true;
$infos = new stdClass();
$infos->id = $row["id"];
$infos->app_id = $row["app_id"];
if ($bln_first_last_lesson) {
if ($str_where != null) {
$str_where .= "," . $row["app_id"];
} else {
$str_where = 'lesson_t.app_id_c IN (' . $row["app_id"];
}
}
$infos->client_id = $row["client_id"];
$infos->client_name = $row["client_name"];
$infos->department = $row["department"];
$infos->date = $row["date"];
$infos->day = date('w', strtotime($row["date"]));
$infos->start_time = $row["start_time"];
$infos->end_time = $row["end_time"];
$infos->form = $row["form"];
$infos->request_status = $row["request_status"];
$infos->confirmed_lesson_id = $row['confirmed_lesson_id'];
$infos->tutor_id = $row["tutor_id"];
$infos->tutor_code = $row["tutor_code"];
$infos->tutor_dispname = $row["tutor_dispname"];
$infos->tutor_name = $row["tutor_name"];
$infos->tutor_available = $row["tutor_available"];
$infos->tutor_available_str = $row["tutor_available"] == 1 ? 'Active' : 'Inactive';
$infos->tutor_assigned = empty($infos->tutor_dispname) ? 0 : 1;
$infos->tutor_delivery_email = $row["tutor_delivery_email"];
$infos->tutor_zipcode = $row["tutor_zipcode"];
$infos->tutor_address = $row["tutor_address"];
$infos->tutor_tel = $row["tutor_tel"];
$infos->tutor_tel_mobile = $row["tutor_tel_mobile"];
$infos->user_id = $row["user_id"];
if ($classfied) {
$infos->tutor_hourly_wage = $row["hourly_wage_c"];
$infos->transportation_expenses = $row["transportation_expenses_c"];
}
$infos->report_for_student = $row["report_for_student_c"];
$infos->absent_student = $row["absent_student_c"];
$infos->note_for_tutors = $row["note_for_tutors_c"];
$infos->classroom_id = $row["classroom_id"];
$infos->classroom_name = $row["classroom_name"];
$infos->area = $row["area"];
$infos->classroom_assigned = empty($infos->classroom_name) ? 0 : 1;
$infos->lesson_id = $row["id"];
$infos->lesson_name = $row["lesson_name"];
$infos->lesson_name_romaji = $row["lesson_name_romaji"];
$infos->lesson_type = $row["lesson_type"];
$infos->lesson_type_str = foundation::$lesson_type_list[$infos->lesson_type];
$infos->lesson_language = $row["lesson_language_c"];
$infos->lesson_category = $row["lesson_category_c"];
$infos->lesson_state = $row["lesson_state"];
$infos->lesson_state_str = self::get_lesson_state_str($row["lesson_state"]);
$infos->lesson_note = $row["lesson_note"];
$infos->lesson_estimated_time = $row["lesson_estimated_time"];
$infos->app_emer_contact_name = $row["app_emer_contact_name"];
$infos->app_emer_contact_relation = $row["app_emer_contact_relation"];
$infos->app_emer_contact_tel = $row["app_emer_contact_tel"];
$infos->app_emer_contact_email = $row["app_emer_contact_email"];
$infos->app_emer2_contact_name = $row["app_emer2_contact_name"];
$infos->app_emer2_contact_relation = $row["app_emer2_contact_relation"];
$infos->app_emer2_contact_tel = $row["app_emer2_contact_tel"];
$infos->app_emer2_contact_email = $row["app_emer2_contact_email"];
$infos->number_of_people = $row["number_of_people"];
$infos->total_minutes_so_far = $row["total_minutes_so_far"];
$infos->total_hours_so_far = $row["total_minutes_so_far"] / 60;
$infos->actual_minutes = $row["actual_minutes"];
$infos->actual_hours = $row["actual_minutes"] / 60;
$infos->total_hours = $infos->total_hours_so_far + $infos->actual_hours;
$infos->location =
$infos->form == 1
? $row["classroom_name"]
: $row["prefecture_c"] . $row["city_c"] . $row["address1_c"] . $row["location_other"]
. '(' . $row["transportation_c"] . ' ' . $row["nearest_station_c"] . ')';
$infos->location_en =
$infos->form == 1
? $row["classroom_name"]
: $row["prefecture_en_c"] . $row["city_en_c"] . $row["address1_en_c"] . $row["location_other_en"] .
(!$classfied || $row["transportation_en_c"] || $row["nearest_station_en_c"]
? '(' . $row["transportation_en_c"] . ' ' . $row["nearest_station_en_c"] . ')'
: '');
$infos->city = $row["city_c"];
$infos->transportation = $row["transportation_c"];
$infos->nearest_station = $row["nearest_station_c"];
$infos->student_id = $row["student_id"];
$infos->ready_datetime = $row["ready_datetime"];
$infos->standby = $row["standby_c"];
$infos->standby_datetime = $row["standby_datetime_c"];
$infos->ready_online = $row["ready_online"];
$infos->ready_online_datetime = $row["ready_online_datetime"];
$infos->skill_type = $row["skill_type"];
$infos->replaced_lesson_id = $row["replaced_lesson_id"];
$infos->first_lesson = false;
$infos->last_lesson = false;
$infos->transportation_expenses = $row["transportation_expenses_c"];
$infos->trial_lesson_status = $row["trial_lesson_status"];
$infos->zoom_id = $row["zoom_id"];
$infos->zoom_client_url = $row["zoom_client_url"];
$infos->zoom_tutor_url = $row["zoom_tutor_url"];
$infos->tutor_zoom_url = $row["tutor_zoom_url"];
$infos->lesson_reserve_request_id = $row["lesson_reserve_request_id"];
if ($classfied) {
if (empty($list[$row["client_id"]])) {
$list[$row["client_id"]] = new stdClass();
}
if (empty($list[$row["client_id"]]->class_info[$row["app_id"]])) {
$list[$row["client_id"]]->class_info[$row["app_id"]] = new stdClass();
}
$list[$row["client_id"]]->name = $row["client_name"];
$list[$row["client_id"]]->class_info[$row["app_id"]]->name = $row["lesson_name"];
$list[$row["client_id"]]->class_info[$row["app_id"]]->lessons[] = $infos;
$list[$row["client_id"]]->class_info[$row["app_id"]]->lesson_count += 1;
$list[$row["client_id"]]->class_info[$row["app_id"]]->total_actual_hours += $infos->actual_hours;
$list[$row["client_id"]]->lesson_count += 1;
$list[$row["client_id"]]->total_actual_hours += $infos->actual_hours;
} else {
$list[] = $infos;
}
}
$del = NULL;
if ($b_exists_app && $bln_first_last_lesson) {
$str_where .= ") and (lesson_t.state_c & " . (LSN_TTR_CANCELLED + LSN_CLI_CANCELLED + LSN_REPLACED + LSN_ADM_CANCELLED) . " = 0)";
$sql = "select lesson_t.app_id_c as app_id ,min(lesson_t.start_datetime_c) as begin_datetime";
if ($search_condition->target !== 'first_lesson') {
$sql .= ", max(lesson_t.end_datetime_c) as now_last_datetime, sum(lesson_t.actual_minutes_c) as total_actual_hours";
}
$sql .= " from lesson_t where " . $str_where . " group by lesson_t.app_id_c";
$del = $pdo->prepare($sql);
$del->execute();
while ($record = $del->fetch(PDO::FETCH_ASSOC)) {
foreach ($list as $e) {
$is_same_datetime = function ($date, $time, $datetime) {
$dt = strtotime($datetime);
$d = date("Y-m-d", $dt);
$t = date("H:i", $dt);
return ($date === $d) && ($time === $t);
};
$is_first_lesson = function ($e, $begin_datetime) use ($is_same_datetime) {
return $is_same_datetime($e->date, $e->start_time, $begin_datetime);
};
$is_last_lesson = function ($e, $now_last_datetime, $total_actual_hours) use ($is_same_datetime) {
return ($e->lesson_estimated_time <= ($total_actual_hours / 60)) && $is_same_datetime($e->date, $e->end_time, $now_last_datetime);
};
if (
(empty($search_condition->target) || $search_condition->target === 'first_lesson') &&
$is_first_lesson($e, $record["begin_datetime"]) &&
$e->app_id == $record["app_id"]
) {
$e->first_lesson = true;
break;
}
if (
(empty($search_condition->target) || $search_condition->target === 'last_lesson') &&
$is_last_lesson($e, $record["now_last_datetime"], $record["total_actual_hours"]) &&
$e->app_id == $record["app_id"]
) {
$e->last_lesson = true;
break;
}
}
}
$del = NULL;
}
} catch (Exception $e) {
database::Close($pdo);
throw $e;
}
database::Close($pdo);
return $list;
}
感谢大家~
在第5行的位置先var_dump($sql_cond->sql)一下，看看这个有没有值，然后最后一个left join中LRR.confirmed_lesson_id_c后面多打一个空格