issue with parent and child data

[davids_media]

I have a major problem;

 

I have two tables - Category and Product

 

The catID is the primary key for Category and acts as the foreign key for Product. I have some example data below;

 

CATEGORY

catID: 3

cat: Pink

 

PRODUCT

prodID: 1

product: Fuchsia

catID: 3

 

prodID: 2

product: Dark Pink

catID: 3

 

what I want is the page to display the data like this?

 

Header: Pink

 

Content:

Fuchsia

Dark Pink

 

Below is the code thus far;

 

 

 

 

<?php

error_reporting(E_ALL ^ E_NOTICE);
ini_set("display_errors", 1);

require ('includes/config.inc.php');

include ('./includes/header.html');

require (MYSQL);

include ('./includes/main.html');

if($id = isset($_GET['catID'])) 
{
$q = ('SELECT `category`.`catID` , `category`.`cat` , `product`.`product`
FROM `product`
LEFT JOIN `hairext`.`category` ON `product`.`catID` = `category`.`catID`
WHERE `product`.`catID`="'.$_GET['catID'].'"');

$r = mysqli_query($dbc, $q);

echo '<div id="right">';

$num = mysqli_num_rows($r);

for ($j = 0 ; $j < $num ; ++$j)
{
$row = mysqli_fetch_row($r);
echo '<h1>' . $row[1] . '</h1>';
}

while($row = mysqli_fetch_array($r))
{
echo '<p>';
echo $row[2];
echo "<br />";
echo '</p>';
}

echo '</div>';

}

include ('./includes/footer.html');

?>

 

At the moment, this is not doing what I want, I apologise if this is very long winded, but how do I solve this problem please?

[Psycho]

OK, so what is the current code doing and how do you want it changed? What have you already tried and what were the results? Are you expecting someone to simply modify the code for you?

[Psycho]

You are trying to loop through the results two times. That is not necessary, simply create a flag variable to determine if the header needs to be displayed.

 

    //Create and run query to get product category names for the selected cat ID
    $query = "SELECT `product`.`product`, `category`.`cat`
              FROM `product`
              LEFT JOIN `category` ON `product`.`catID` = `category`.`catID`
              WHERE `product`.`catID`='{$_GET['catID']}'
              ORDER BY `product`.`product`";
    $result = mysqli_query($dbc, $query);

    //Create flag variable to determine if header needs to be displayed
    $showHeader = true;
    echo "<div id='right'>\n";
    while($row = mysqli_fetch_array($r))
    {
        if($showHeader)
        {
            //Display category header
            echo "<h1>{$row['cat']}</h1>\n";
            $showHeader = false;
        }
        //Display product name
        echo "<p>{$row[2]}<br /></p>\n";
    }
    echo "</div>\n";

}

 

Alternatively, since you will have only one header, you could use mysql_result() to pull the header name out of the result set without moving the pointer ahead

    //Create and run query to get product category names for the selected cat ID
    $query = "SELECT `product`.`product`, `category`.`cat`
              FROM `product`
              LEFT JOIN `category` ON `product`.`catID` = `category`.`catID`
              WHERE `product`.`catID`='{$_GET['catID']}'
              ORDER BY `product`.`product`";
    $result = mysqli_query($dbc, $query);

    $cat_header = mysql_result($result, 0, 'cat');

    echo "<div id='right'>\n";
    echo "<h1>{$cat_header}</h1>\n";
    while($row = mysqli_fetch_array($r))
    {
        //Display product name
        echo "<p>{$row[2]}<br /></p>\n";
    }
    echo "</div>\n";

[davids_media]

Thanks for your help, the first example code was the one I used, however I had to make a few very minor changes to prevent undefined offsets.