I am doing some reporting over the structure of access for a given set of Pages in an Application.
These Pages are grouped together into Abilities. If an ID has any one of the Pages for this Ability I want to record it. Think of it like an upside down funnel, as we go down the hierarchy the data increases. However, with the code below it is writing out each User thousands of times, overloading the site and filling up the table - rather than once. I only need the id recorded once with the ability, even though they might be picked up multiple times.
I know the logic is flawed, but I was hoping that by doing a select in the loop that I could eliminate duplicate entries into the table, but it is not working and I am sure this can be more streamlined:
$ability_page_details1 = $DB_con2->prepare("SELECT PGD FROM PGD_DETAILS WHERE PGD = :pgd_ability1 AND SELECT_FLAG = 'Y'");
$ability_page_details1->bindParam(':pgd_ability1', $pgd_ability1);
$ability_page_details1->execute();
while($ability_content_page_row = $ability_page_details1->fetch(PDO::FETCH_ASSOC)) {
$pgd_page1 = $ability_content_page_row['PGD'];
//this is the page
//now lets work out all the Abilities that have this page
$ability_page_compare1 = $DB_con2->prepare("SELECT PGD.PNLNAME, AUTH.PNLITEMNAME,
PGD.SELECT_FLAG, AUTH.ACTIONS, AUTH.ID FROM PPGD INNER JOIN AUTH ON
PGD.PGDNAME = AUTH.PGDNAME WHERE PGD.PGDNAME = :pgdname_page1 AND AUTH.ACTIONS >0");
$ability_page_compare1->bindValue(':pgdname_page1', $pgdname_page1);
$ability_page_compare1->execute(array(':pgdname_page1'=>$pgdname_page1));
while($ability_page_compare_row1 = $ability_page_compare1->fetch(PDO::FETCH_ASSOC)) {
$id_ability1 = $ability_page_compare_row1['ID'];
//lets check if the ID has an entry already, if not then insert
$check_pm_for_abilities = $DB_con2->prepare("SELECT id, ability_id FROM validation_abilities WHERE id =:id");
$check_pm_for_abilities->execute(array(":id" => $id_ability1));
$check_pm_for_abilities_row=$check_pm_for_abilities->fetch(PDO::FETCH_ASSOC);
if($check_pm_for_abilities_row['classid']==$id_ability1 && $check_pm_for_abilities_row['ability_id']==$ability_id1) {
}
else{
//now for the insert
$validation_abilities = $DB_con2->prepare("INSERT INTO validation_abilities (id, validation_id, ability_id)
VALUES(:id, :validation_id, :ability_id)");
$validation_abilities->execute(array(":id" => $id_ability1, ":validation_id"=>$validation_id, ":ability_id"=>$ability_id1));
}
}
}