שליפה איטית מאוד של נתונים ממסד נתונים - מה ניתן לעשות?
שלום,
ראשית,
אומר שהמערכת אותה אני מתכנת מבוססת PHP.
המערכת אמורה לנהל טפסי רישום של ילדים לרשת צהרונים.
נכון לרגע זה ישנן 11,470 רשומות בטבלה שנקראת rishum.
כך אני רוצה לשלוף את סטטיסטיקות הטפסים:
https://imagizer.imageshack.us/v2/13...674/OEQv5a.jpg
לוקחות כמה דק' עד שעמוד זה עולה.
הקוד לשליפת הנתונים הינו:
PHP קוד:
<center>
<br />
<table border=0 cellspacing=1 cellpadding=0 width="1000px">
<tr>
<td align='right' valign='middle' style='width: 40%;text-align: right;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>שם המוסד</td>
<td align='right' valign='middle' style='width: 15%;text-align: right;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רישום לקייטנה</td>
<td align='right' valign='middle' style='width: 15%;text-align: right;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>משלמים לקייטנה</td>
<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רישום לצהרון</td>
<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>משלמים לצהרון</td>
<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רשומים לבית ספר</td>
<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רשומים לגן</td>
</tr>
</table>
<?
?>
<?php
$res1 = mysql_query("SELECT * FROM `places` ORDER BY `name` desc");
while($row1 = mysql_fetch_array($res1)) {
$res_ci = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_ci = mysql_num_rows($res_ci);
$res_ci_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `city`='".$row1["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_ci_PAY = mysql_num_rows($res_ci_PAY);
$res2_ci = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row2_ci = mysql_num_rows($res2_ci);
$res2_ci_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `city`='".$row1["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row2_ci_PAY = mysql_num_rows($res2_ci_PAY);
$res_countPerCity_school = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `soog_mosad`='school' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_countPerCity_school = mysql_num_rows($res_countPerCity_school);
$res_countPerCity_gan = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `soog_mosad`='gan' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_countPerCity_gan = mysql_num_rows($res_countPerCity_gan);
if(($row_ci+$row2_ci) > 0) {
?>
<div class="module" style="width: 1000px"><div class="module_name" align='center' style='cursor: hand;cursor: pointer;'>
<table border=0 cellspacing=1 cellpadding=0 width="1000px">
<tr class="try">
<td align="right" style="text-align: right;width: 40%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row1["name"];?></td>
<td align="center" style="text-align: center;width: 15%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_ci;?></td>
<td align="center" style="text-align: center;width: 15%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_ci_PAY;?></td>
<td align="center" style="text-align: center;width: 10%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row2_ci;?></td>
<td align="center" style="text-align: center;width: 10%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row2_ci_PAY;?></td>
<td align="center" style="text-align: center;width: 5%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_countPerCity_school;?></td>
<td align="center" style="text-align: center;width: 5%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_countPerCity_gan;?></td>
</tr>
</table>
</div><div class="module_links2" align='center'>
<table border=0 cellspacing=1 cellpadding=0 width="1000px">
<?
$resCITY = mysql_query("SELECT * FROM `schools` WHERE `city`='".$row1["id"]."' ORDER BY `type` desc,`name`");
while($rowCITY = mysql_fetch_array($resCITY)) {
$res = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row = mysql_num_rows($res);
$res_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_PAY = mysql_num_rows($res_PAY);
$res2 = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row2 = mysql_num_rows($res2);
$res2_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row2_PAY = mysql_num_rows($res2_PAY);
if(($row+$row2) > 0) {
?>
<tr>
<td align="rigth" style="width: 40%;text-align: right;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><?=$rowCITY["name"];?></td>
<td align="center" style="width: 15%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><a href="index.php?action=rishum&sa=statistics&year=<?=$filterByYear;?>&city=<?=$row1["id"];?>&school=<?=$rowCITY["id"];?>&tofesType=1&typeee=<?=$rowCITY["type"];?><? if($_GET["aougust_form"]) { echo "&aougust_form=1"; } ?>"><?=$row;?></a></td>
<td align="center" style="width: 15%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><?=$row_PAY;?></td>
<td align="center" style="width:10%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><a href="index.php?action=rishum&sa=statistics&year=<?=$filterByYear;?>&city=<?=$row1["id"];?>&school=<?=$rowCITY["id"];?>&tofesType=2&typeee=<?=$rowCITY["type"];?><? if($_GET["aougust_form"]) { echo "&aougust_form=1"; } ?>"><?=$row2;?></a></td>
<td align="center" style="width:10%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><?=$row2_PAY;?></td>
<td align="center" style="width:5%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"> --- </td>
<td align="center" style="width:5%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"> --- </td>
</tr>
<?
}
}
?>
</table>
</div></div>
<?
}
}
$res_all = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all = mysql_num_rows($res_all);
$res_all_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all_PAY = mysql_num_rows($res_all_PAY);
$res_all2 = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all2 = mysql_num_rows($res_all2);
$res_all2_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all2_PAY = mysql_num_rows($res_all2_PAY);
?>
<table border=0 cellspacing=1 cellpadding=0 width="1000px">
<tr>
<td align='right' valign='middle' style='width: 40%;text-align: right;padding: 10px;background: #000;color: #FFF;font-weight: bold;'>סה"כ טפסי רישום</td>
<td align='center' valign='middle' style='width: 15%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all;?></td>
<td align='center' valign='middle' style='width: 15%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all_PAY;?></td>
<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all2;?></td>
<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all2_PAY;?></td>
<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'> --- </td>
<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'> --- </td>
</tr>
</table>
</center>
|
