I have this code(provided below) that it takes too slow to get the specific data of the following dates
last 7 days, 10 days, 15 days, 30 days, 3 months, 6 months, year to date, 1 year, 2 yrs, 3 yrs, 4 yrs, 5yrs, 10 yrs, 15 yrs, 20 yrs and the first entry of the record.
Can you help me to optimize it?
Any help is highly appreciated.
$d7 = array('from' => date("Ymd",strtotime("-7 days")), 'to' => date('Ymd')); #7days
$d10 = array('from' => date("Ymd",strtotime("-10 days")), 'to' => date('Ymd')); #10 days
$d15 = array('from' => date("Ymd",strtotime("-15 days")), 'to' => date('Ymd')); #15 days
$d20 = array('from' => date("Ymd",strtotime("-20 days")), 'to' => date('Ymd')); #20 days
$day30 = array('from' => date("Ymd",strtotime("-30 days")), 'to' => date('Ymd')); #30 days
$m3 = array('from' => date("Ymd",strtotime("-3 months")), 'to' => date("Ymd",strtotime("-2 months"))); #3 months
$m6 = array('from' => date("Ymd",strtotime("-6 months")), 'to' => date("Ymd",strtotime("-5 months"))); #6 months
$ytd = array('from' => date("Y").'0101', 'to' => date('Ymd')); #year to date
$y1 = array('from' => date("Ymd",strtotime("-12 months")), 'to' => date("Ymd",strtotime("-11 months"))); #1 year
$y2 = array('from' => date("Ymd",strtotime("-24 months")), 'to' => date("Ymd",strtotime("-23 months"))); #2 years
$y3 = array('from' => date("Ymd",strtotime("-36 months")), 'to' => date("Ymd",strtotime("-35 months"))); #3 years
$y4 = array('from' => date("Ymd",strtotime("-48 months")), 'to' => date("Ymd",strtotime("-47 months"))); #4 years
$y5 = array('from' => date("Ymd",strtotime("-60 months")), 'to' => date("Ymd",strtotime("-59 months"))); #5 years
$y10 = array('from' => date("Ymd",strtotime("-120 months")), 'to' => date("Ymd",strtotime("-119 months"))); #10 years
$y15 = array('from' => date("Ymd",strtotime("-180 months")), 'to' => date("Ymd",strtotime("-179 months"))); #15 years
$y20 = array('from' => date("Ymd",strtotime("-240 months")), 'to' => date("Ymd",strtotime("-239 months"))); #20 years
$sql = "(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #7 days
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #10 days
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #15 days
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #20 days
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #30 days
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #3 months
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #6 months
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #ytd
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y1
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y2
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y3
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y4
" union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y5
"union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y10
"union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y15
"union all
(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y20
"union all
(select date, close from stocks_history where symbol=? order by date asc limit 1) "; #all records
$sql = $this->db->prepare($sql);
$sql->bindParam(1, $code, PDO::PARAM_STR);
$sql->bindParam(2, $d7['from'], PDO::PARAM_INT);
$sql->bindParam(3, $d7['to'], PDO::PARAM_STR); #7 days
$sql->bindParam(4, $code, PDO::PARAM_STR);
$sql->bindParam(5, $d10['from'], PDO::PARAM_INT);
$sql->bindParam(6, $d10['to'], PDO::PARAM_STR); #10 days
$sql->bindParam(7, $code, PDO::PARAM_STR);
$sql->bindParam(8, $d15['from'], PDO::PARAM_INT);
$sql->bindParam(9, $d15['to'], PDO::PARAM_STR); #15 days
$sql->bindParam(10, $code, PDO::PARAM_STR);
$sql->bindParam(11, $d20['from'], PDO::PARAM_INT);
$sql->bindParam(12, $d20['to'], PDO::PARAM_STR); #20 days
$sql->bindParam(13, $code, PDO::PARAM_STR);
$sql->bindParam(14, $day30['from'], PDO::PARAM_INT);
$sql->bindParam(15, $day30['to'], PDO::PARAM_STR); #30 days
$sql->bindParam(16, $code, PDO::PARAM_STR);
$sql->bindParam(17, $m3['from'], PDO::PARAM_INT);
$sql->bindParam(18, $m3['to'], PDO::PARAM_STR); #60 days
$sql->bindParam(19, $code, PDO::PARAM_STR);
$sql->bindParam(20, $m6['from'], PDO::PARAM_INT);
$sql->bindParam(21, $m6['to'], PDO::PARAM_STR); #90 days
$sql->bindParam(22, $code, PDO::PARAM_STR);
$sql->bindParam(23, $ytd['from'], PDO::PARAM_INT);
$sql->bindParam(24, $ytd['to'], PDO::PARAM_STR); #ytd
$sql->bindParam(25, $code, PDO::PARAM_STR);
$sql->bindParam(26, $y1['from'], PDO::PARAM_INT);
$sql->bindParam(27, $y1['to'], PDO::PARAM_STR); #y1
$sql->bindParam(28, $code, PDO::PARAM_STR);
$sql->bindParam(29, $y2['from'], PDO::PARAM_INT);
$sql->bindParam(30, $y2['to'], PDO::PARAM_STR); #y2
$sql->bindParam(31, $code, PDO::PARAM_STR);
$sql->bindParam(32, $y3['from'], PDO::PARAM_INT);
$sql->bindParam(33, $y3['to'], PDO::PARAM_STR); #y3
$sql->bindParam(34, $code, PDO::PARAM_STR);
$sql->bindParam(35, $y4['from'], PDO::PARAM_INT);
$sql->bindParam(36, $y4['to'], PDO::PARAM_STR); #y4
$sql->bindParam(37, $code, PDO::PARAM_STR);
$sql->bindParam(38, $y5['from'], PDO::PARAM_INT);
$sql->bindParam(39, $y5['to'], PDO::PARAM_STR); #y5
$sql->bindParam(40, $code, PDO::PARAM_STR);
$sql->bindParam(41, $y10['from'], PDO::PARAM_INT);
$sql->bindParam(42, $y10['to'], PDO::PARAM_STR); #y10
$sql->bindParam(43, $code, PDO::PARAM_STR);
$sql->bindParam(44, $y15['from'], PDO::PARAM_INT);
$sql->bindParam(45, $y15['to'], PDO::PARAM_STR); #y15
$sql->bindParam(46, $code, PDO::PARAM_STR);
$sql->bindParam(47, $y15['from'], PDO::PARAM_INT);
$sql->bindParam(48, $y15['to'], PDO::PARAM_STR); #y20
$sql->bindParam(49, $code, PDO::PARAM_STR); #all
$sql->execute();
$row = $sql->fetchAll(PDO::FETCH_ASSOC);
$last = count($row)-1;
$data_orig = array('N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A');
$percgain = 0;
foreach($row as $k=>$v){
$percgain = (($lprice - $v['close']) / $v['close'] ) * 100;
if($k == $last){
$data['all'] = moneyformatVal2($percgain).'%';
}else{
$data[] = moneyformatVal2($percgain).'%';
}
}
$data = array_replace($data_orig, $data);
return $data;