Jump to content

0xfo7d

New Members
 View Profile  See their activity

  • Posts

    1

  • Joined

  • Last visited

 Content Type 

Posts posted by 0xfo7d

    Very slow retrieval of data from a database - what can you do?

    in PHP Coding Help

    Posted

    Hello, 

    First, 

    Says the system that I'm a programmer based in PHP. 

    The system is intended to manage children's registration forms, daycare network. 

    At the moment there are 11,470 records in a table called rishum. 

    I want to pull out the statistics form:

    OEQv5a.jpg

    Take a few minutes to this page costs. 

    Code for retrieving data is:



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


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.