0

Sql Query inside while loop making my application very slow.

<ul>    
<?php 
$sql1 = "SELECT cat_id,cat_name FROM all_services where uni_id='$uni_id'";
$sql_query = mysqli_query($conn,$sql1);
while($display_class = mysqli_fetch_assoc($sql_query))
{
$class_id = $display_class['cat_id'];
?>
<li>
<i class="fa fa-plus"></i>
<label>
<input  id="1" data-id="1" type="checkbox" value="<?php echo "$class_id";   ?>"/><?php echo $display_class['cat_name']; ?>
</label>
<ul>
<?php  $sql2 = mysqli_query($conn,"SELECT roll_no,st_name,st_mobile,st_dob FROM student_info where school_uni_id='$uni_id' and st_class='$class_id' order by roll_no + 0 ASC, st_name ASC"); 
while($display_stu = mysqli_fetch_assoc($sql2))
{
?>
<li>
<label>
<input class="treenode" id="xnode-0-1-1" data-id="custom-0-1-1" type="checkbox" value="<?php echo $display_stu['st_mobile']; ?>,<?php echo $display_stu['st_dob']; ?>" /><?php echo $display_stu['roll_no']; ?>&nbsp;&nbsp;<?php echo $display_stu['st_name']; ?>
</label>
</li>
<?php } ?>
</ul>
</li>
<?php } ?>
</ul>

Both sql query is linked to each other, I need $class_id from first query $sql1 to put in second query i.e $sql2.

While loop inside while loop making it very slow.

It works fine if query result is upto 1000rows but above 2000rows application become very slow. How to make my application fast?

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Metu007
  • 65
  • 7
  • Possible duplicate of [What is the N+1 SELECT query issue?](https://stackoverflow.com/questions/97197/what-is-the-n1-select-query-issue) – Progman Jul 29 '18 at 08:31

2 Answers2

0

//You didn't inform about MySQL database size & server configuration but following steps will faster your while loop

// Please add MySQL index uni_id at all_services table.

// I have make some changes at $sql2

$sql2 = mysqli_query($conn,"SELECT roll_no,st_name,st_mobile,st_dob FROM student_info where school_uni_id='$uni_id' order by roll_no ASC");

//Please add MySQL index school_uni_id & roll_no at student_info table.

Maruf Hasan
  • 21
  • 4
-1

You need to paginate your page using query parameters.
Your while is useless: you can directly fetch your result is the same way.

Coco Jr
  • 117
  • 1
  • 7