Looking to Build/Redesign a Website or Blog ?

To speak to Rakshit, call +91 968 704 2303

MYSQL Query Optimization – nLevel categories(Parent-Child,recursive) with single query

Hello Friends,

I was working on site optimization for one of my client. My client wants to reduce the loading time of the page and i found that most of the time is taken by database queries. At many places i need to fire mulitple queries like parent child relation. For parent child relationship, first i was taking parent records and than from each parent record, it will take child records. So for that it will fire many MYSQL queries which takes so much loading time.

If i fire only one mysql query and make an array using recursive function than it reduce a lot of time, Below is the recursive array function which built recursive array with parent child relationship. This may help you in one or other way. I have written this function (checkboxes) with selected options also. If you are using this for edit time than it will show selected options. You just need to pass database stored value in $selectedarr.  Have a look at it.

$platform_data = $platform->getallplatforms(); // Take all records from tables
for ($i = 0; $i < count($platform_data); $i++) {
if ($platform_data[$i][‘parent_id’] == 0) {
$viewstr .= ‘<li><input type=”checkbox” name=”” id=”” value=”‘ . $platform_data[$i][“platform_id”] . ‘” />’ . $platform_data[$i][“varname”];
$viewstr .= $this->recursive_array_platform($platform_data[$i][‘platform_id’], $platform_data, $selectedarr);
}
$viewstr .= ‘</li>’;
}

$viewstr = str_replace(‘<ul></ul>’, ”, $viewstr);

function recursive_array_platform($id, $array, $selectedarr)
{
$child_str .= “<ul>”;
if (!empty($array)) {
foreach ($array as $single) {
foreach ($array as $single2) {
if ($single2[‘parent_id’] == $id) {
if ($single[‘platform_id’] == $single2[‘parent_id’]) {
$checked = “”;
if (in_array($single2[“platform_id”], $selectedarr)) {
$checked = ‘checked’;
}
$child_str .= ‘<li><input type=”checkbox” name=”chk_platform[]” id=”chk_platform[]” value=”‘ . $single2[“platform_id”] . ‘” ‘ . $checked . ‘ />’ . $single2[“varname”];
$child_str .= $this->recursive_array_platform($single2[‘platform_id’], $array, $selectedarr);
$child_str .= “</li>”;
}
}
}
}
}
$child_str .= “</ul>”;
return $child_str;
}

echo “<ul>”.$viewstr.”</ul>”; // This will show ul-li with parent child format.

Let me know if you have any questions.

Share:

1 Comment

  • Hi there! Thank you for the awesome post. Perfect information
    and facts exactly like what I am looking for a very long time.
    Seriously happy to find this write up and I
    had shared on a number of social networks platforms! Going to bookmark this too
    for reference in the future as well. Sustain the excellent work.

Leave a Reply

Your email address will not be published.