0

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
38
Views
3 Years
Discussion Span
Last Post by afaaro
0

So you have 2 related tables? products and categories?

0

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

0

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

0

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

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

0

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: amend

0

Well, obviously. You haven't echoed it.

echo $output;

Wherever in the page you need it.

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

see how many rows you have.

0
$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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.