0

I have a mysql table which I'm trying to turn into jQuery listview widgets. The part that I'm having trouble with is how best to extract the info from the table without doing multiple queries on the db itself. I have the following code which works:

global $conn;
$sql = "SELECT `id` FROM `implantFamilies` WHERE `familySelector` = '" . $_POST['implantFamily'] . "'"; //need to sanitise this
$result = $conn->query($sql);
if ($result->num_rows == 1) {
    while($row = $result->fetch_assoc()) {
        $familyId = $row["id"];
    }
} else {
    die("Error: " . $result->num_rows . " family groups in result. Alert administrator.");
}
?>
<form class="ui-filterable">
<input id="filterBasic-input" data-type="search">
</form>
<?php
$sql = "SELECT DISTINCT `familySection` FROM `implants` WHERE `familyGroupId` = '" . $familyId . "'";
$sections = $conn->query($sql);
if ($sections->num_rows > 0) {
    while ($sectionRow = $sections->fetch_assoc()) {
        $output .= "<b>" . $sectionRow["familySection"] . "</b><br>";
        //DISPLAY COLLAPSIBLE DIV HERE
        $sql = "SELECT DISTINCT `sectionHeading` FROM `implants` WHERE `familyGroupId` = '" . $familyId . "' AND `familySection` = '" . $sectionRow["familySection"] . "'";
        $dividers = $conn->query($sql);
        if ($dividers->num_rows > 0) {
            while ($headingRow = $dividers->fetch_assoc()) {
                $output .= "<i>" . $headingRow["sectionHeading"] . "</i><br>";
                //DISPLAY list-divisers DIV HERE
                $sql = "SELECT `reference`, `description`, `filterText`, `requiresLot` FROM `implants` WHERE `familyGroupId` = '" . $familyId . "' AND `familySection` = '" . $sectionRow["familySection"] . "' AND `sectionHeading` = '" . $headingRow["sectionHeading"] . "'";
                $implants = $conn->query($sql);
                if ($implants->num_rows > 0) {
                    while ($implantRow = $implants->fetch_assoc()) {
                        $output .= $implantRow["description"] . "<br>";
                        //DISPLAY implants DIV HERE
                    }
                } else {
                    $output = "0 results";
                }
            }
        } else {
            $output = "0 results";
        }
    }
} else {
    $output = "0 results";
}
$conn->close();
echo $output;

On the first lot of (simple) data which I've put into the table, this code executes 23 mysql queries. There must be a simpler way!

Brad
  • 1,019
  • 1
  • 9
  • 22
  • Nested queries like this can generally be turned into joins. – Barmar Sep 18 '16 at 06:34
  • Why are you using a `while` loop when `$result->num_rows == 1`? – Barmar Sep 18 '16 at 06:35
  • Whenever I see a loop like that for a query that's known to return exactly 1 row, it makes me think you're just copying and pasting code snippets without actually understanding what they do. – Barmar Sep 18 '16 at 06:37
  • 1
    You aren't even querying from different tables in the nested queries, it's all the same table. Just do a single query for all the data, and order the results by the columns you're grouping the output by. Then print the headings whenever a column changes. See http://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 – Barmar Sep 18 '16 at 06:41
  • Thanks @Barmar. The test for ==1 is to ensure that I don't receive a result with either 0 or more than 1 rows. It needs to be exactly 1 or else something has gone wrong elsewhere in the code. You're right, it's a simple enough query, all from the same table so it should be easy to figure out. I'll give that solution you provided in the link a try, looks like it should be able to do the trick. – Brad Sep 18 '16 at 09:28
  • I understand why you test for `==1`. What I don't understand is why you have the call to `$row->fetch_assoc()` in a loop when you know there's only 1 row. You use a loop when you want to fetch more than 1 row. – Barmar Sep 18 '16 at 09:30

0 Answers0