I need to create a list of products, grouped by a category field. I am using PHP and MySQL. I have fields called ProdName, ProdCat etc.

I need the list to appear as follows:

  • Category A

    • Product 1
    • Product 2
  • Category B

    • Product 3
    • Product 4
    • Product 5
  • Category C

    • Product 6
    • etc...

I want to create a product listing which lists ALL products but groups by category, with each Category to appear in it's own styled div.

I would also like to have a drop down list at the top of the page which would give the user an option to view products which just appear in particular section (i.e. only show products from Category B)

Any help would be greatly appreciated.

Member Avatar

diafol

So you have 2 related tables? products and categories?

Yes, with a ProdCatID field in the Products table which links to a CatID field in the Category table.

Member Avatar

diafol

OK, well you need to use a JOIN and then ORDER BY...

Something like...

SELECT p.product_id, p.productname, c.categoryname FROM products AS p INNER JOIN categories AS c ON p.ProdCatID = c.CatID ORDER BY c.categoryname, p.productname

When you loop through the resulting dataset, just look out for changes in categoryname.

$title = '';
$output = '';
while($data = mysqli_fetch_assoc($result))
{
    if($data['categoryname'] != $title)
    {
        $output .= "<h3>{$data['categoryname']}</h3>";
        $title = $data['categoryname'];
    }
    $output .= "<p>{$data['productname']}</p>";
}

Change the html containers as you see fit. Not tested.

Thanks for your reply. A little confused with the join. Could I ask you to clarify using the field names please?

Download.DownloadID
Download.DownloadName
Download.DownloadCategory

Category.CategoryID
Category.CategoryName

Member Avatar

diafol

SELECT d.DownloadID, d.DownloadName, c.CategoryName FROM Download AS d INNER JOIN Category AS c ON d.DownloadCategory = c.CategoryID ORDER BY c.CategoryName, d.DownloadName

If you're wondering about d and c - they're ALIASes of Download and Category - these are defined with the AS keyword - it just saves a lot of typing and you're less likely to make a typo.

Thanks for your help. The query works perfectly, but I cannot get an output. Below is what I have so far, but not sure what is wrong!.

<?php require_once('Connections/Connection.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_gftech, $gftech);
$query_rsList = "SELECT d.DownloadID, d.DownloadName, c.CategoryName FROM Download AS d INNER JOIN Category AS c ON d.DownloadCategory = c.CategoryID ORDER BY c.CategoryName, d.DownloadName";
$rsList = mysql_query($query_rsList, $gftech) or die(mysql_error());
$row_rsList = mysql_fetch_assoc($rsList);
$totalRows_rsList = mysql_num_rows($rsList);
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Test Loop</title>
</head>

<body>
<?php
$title = '';
$output = '';
while($data = mysqli_fetch_assoc($rsList))
{
    if($data['CategoryName'] != $title)
    {
        $output .= "<h3>{$data['CategoryName']}</h3>";
        $title = $data['CategoryName'];
    }
    $output .= "<p>{$data['DownloadName']}</p>";
}
?>
</body>
</html>
<?php
mysql_free_result($rsList);
?>
Member Avatar

diafol

Well, obviously. You haven't echoed it.

echo $output;

Wherever in the page you need it.

Member Avatar

diafol

$totalRows_rsList = mysql_num_rows($rsList);
echo $totalRows_rsList;

see how many rows you have.

Member Avatar

diafol

$rsList = mysql_query($query_rsList, $gftech) or die(mysql_error());
$totalRows_rsList = mysql_num_rows($rsList);
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Test Loop</title>
</head>
<body>
<?php
$title = '';
$output = '';
while($data = mysqli_fetch_assoc($rsList))
{
    print_r($data);
}

Try that to see each record as an array output.