PHP Mysql output issue probably a { Issue

[arch691]

Hi, I have this code below which groups all the SubHeading together and then queries the same table to find RiskConsequence which are grouped that match SubHeading then to query this table one more last time with all the Risk Mitigation that matches the grouped RiskConsequence.

 

Problem I get is it does the SubHeading, the RiskConsequences it only does one of them not all of them before it moves onto the RiskMitigation.

 

I know I have a php coding issue just cant see the wood from the tree's as the queries work.

 

<?php
include ("include.php");
$query = "SELECT * FROM tblriskassessmentdatabank GROUP BY SubHeading";
$results = mysql_query($query) or die("Error: " . mysql_error());
while($row1 = mysql_fetch_array($results)){
    

    echo'<a href="#" onClick="return(changeDisplay(';echo"'";echo($row1[subHeading]);echo"'))";echo'">';echo($row1[subHeading]);echo'</a><br /><br />';
echo'<div id="';echo($row1[subHeading]); echo'" class="HideText">';

$risksub = $row1[subHeading];

$query1 = "SELECT * FROM tblriskassessmentdatabank GROUP By RiskConsequence";
$results1 = mysql_query($query1) or die("Error: " . mysql_error());
while($row2 = mysql_fetch_array($results1)){


echo'<a href="#" onClick="return(changeDisplay(';echo"'";echo($row2[RiskConsequence]);echo"'))";echo'">';echo($row2[RiskConsequence]);echo'</a><br />';
echo'<div id="';echo($row2[RiskConsequence]); echo'" class="HideText">';
$risksub1 = $row2[RiskConsequence];
$query1 = "SELECT * FROM tblriskassessmentdatabank WHERE RiskConsequence = '$risksub1'";
$results1 = mysql_query($query1) or die("Error: " . mysql_error());
while($row3 = mysql_fetch_array($results1)){

echo'<input name="checkbox[]" type="checkbox" id="checkbox[]" value="';echo($row3[issueNumber]);echo'" />
    <label for="checkbox[]"></label>';echo($row3[RiskMitigation]);echo'<br /><br />';
}
echo'</div>';

}

echo'</div>';
  }
?>

[Barand]

Are you sure you mean "GROUP BY" and not "ORDER BY"?

[Muddy_Funster]

...so you're running a query - within a loop - within a query - within a loop - within a query?? are you mental?  That's so far from best practice it won't even be able to see it on the horizon!  You only need one single query to do what you are doing with potentialy thousands of them.

 

Please post up your table structures if you would be so kind.

[arch691]

Hi this is the Table structure.

 

I am trying to make a tree like explorer.

 

so you have the SubHeading's Grouped but they are repeated in the records. so if you can get it to only print it one in html then the RiskConsequence groups or only printed once then all the RiskMitigation linked to the one RiskConsequence that matches SubHeading etc.

 

so the HTML Output would be

 

<a href="#" onClick="return(changeDisplay('SubHeading'))">SubHeading</a><br /><br /> //this will be all the grouped SubHeading
<div id="SubHeading" class="HideText">
<a href="#" OnClick="return(changeDisplay('RiskConsequence'))">RiskConsequence</a><br /> // Then this is the Grouped Risk Consequences from the Grouped SubHeadings there will be several of these under each subheading.
<div id="RiskConsequence" class="HideText">
<input name="checkbox[]" type="checkbox" id="checkbox[]" value="IssueNumber" />
    <label for="checkbox[]"></label>RiskMitigation //then this is the RiskMitigation per the Matching RiskConsequence there will be serveral under each riskconsequence
</div>
</div>

 

This will then repeat per SubHeading.

 

 

CREATE TABLE `tblriskassessmentdatabank` (
  `IssueNumber` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
  `IssueCategory` varchar(150) NOT NULL,
  `Heading` varchar(150) NOT NULL,
  `SubHeading` varchar(150) NOT NULL,
  `AuditQuestion` varchar(1000) DEFAULT NULL,
  `ApplicableRiskAssessment` varchar(75) DEFAULT NULL,
  `RiskConsequence` varchar(1000) DEFAULT NULL,
  `RiskMitigation` varchar(1000) DEFAULT NULL,
  `EmergencyMeasures` varchar(1000) DEFAULT NULL,
  `MitigationActionBy` varchar(100) DEFAULT NULL,
  `ApplicableMethodStatement` varchar(200) DEFAULT NULL,
  `SafetySeverity` tinyint(1) unsigned NOT NULL,
  `SafetyLikelihood` tinyint(1) unsigned NOT NULL,
  `EnvSeverity` tinyint(1) unsigned NOT NULL,
  `EnvLikelihood` tinyint(1) unsigned NOT NULL,
  `QASeverity` tinyint(1) unsigned NOT NULL,
  `QALikelihood` tinyint(1) unsigned NOT NULL,
  `FinSeverity` tinyint(1) unsigned NOT NULL,
  `FinLikelihood` tinyint(1) unsigned NOT NULL,
  `SafetySeverityRemaining` tinyint(1) unsigned NOT NULL,
  `SafetyLikelihoodRemaining` tinyint(1) unsigned NOT NULL,
  `EnvSeverityRemaining` tinyint(1) unsigned NOT NULL,
  `EnvLikelihoodRemaining` tinyint(1) unsigned NOT NULL,
  `QASeverityRemaining` tinyint(1) unsigned NOT NULL,
  `QALikelihoodRemaining` tinyint(1) unsigned NOT NULL,
  `FinSeverityRemaining` tinyint(1) unsigned NOT NULL,
  `FinLikelihoodRemaining` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`IssueNumber`),
  KEY `IssueCategory` (`IssueCategory`),
  KEY `Heading` (`Heading`),
  KEY `SubHeading` (`SubHeading`),
  KEY `ApplicableRiskAssessment` (`ApplicableRiskAssessment`)
) ENGINE=InnoDB AUTO_INCREMENT=630 DEFAULT CHARSET=latin1;

[Muddy_Funster]

First thing you should do is properly normalise your data structure - you have used a single table where you should have used 4+ of them.

 

As it stands you're in for a world of hurt trying any advanced queries on that thing.  however, using

SELECT (only the field names that you need) FROM tblriskassessmentdatabank GROUP BY RiskMitigation, RiskConsiquence, SubHeading

 

should get you the data that you are looking for in a format that you can present as you want.

 

But I strongly recomend that you fix the tables before going any further.

[arch691]

What would you recommend?

 

I was thinking of splitting out the data into a SubHeadings Table with an ID and then the Risk Consequence as another with an ID with a LinkID to the SubHeading then The Risk Mitigation then that Linked to the to RiskConsequence with a LinkID.

 

then its just the php loop to get all subheadings then echo the subheading then all the risk consequences with the riskmitigations under each risk consequence then repeat with next SubHeading.

 

Any advice would be great. I never made the database either I was just handed it and trying to work with it.