Member Avatar for Enigma360

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.

  • 3 Contributors
  • 12 Replies
  • 304 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by afaaro
Member Avatar for diafol

So you have 2 related tables? products and categories?

Member Avatar for Enigma360

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

Member Avatar for 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.

Edited by diafol
Member Avatar for Enigma360

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 for 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.

Member Avatar for Enigma360

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);
?>
Edited by Enigma360 because: amend
Member Avatar for diafol

Well, obviously. You haven't echoed it.

echo $output;

Wherever in the page you need it.

Member Avatar for Enigma360

Still doesn't display anything?

Member Avatar for diafol 
$totalRows_rsList = mysql_num_rows($rsList);
echo $totalRows_rsList;

see how many rows you have.

Member Avatar for Enigma360

That comes back with '190'

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.