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.

rax rss MYSQL Query Optimization nLevel categories(Parent Child,recursive) with single query  rax twitter MYSQL Query Optimization nLevel categories(Parent Child,recursive) with single query  rax facebook MYSQL Query Optimization nLevel categories(Parent Child,recursive) with single query  rax myspace MYSQL Query Optimization nLevel categories(Parent Child,recursive) with single query