Multiple Categories on one page

craigeves · March 18, 2010

Not sure how easy this is to do...

I have 2 tables... 'categories' & 'products'.

Products are assigned to categories.

I want to show all categories on a page with their associated products below them. It's easy for me to do this on seperate pages - but I want to add it all to one page. How do I achieve this?

ie.

Category 1

product 1

product 2

Category 2

product 3

product 4

product 5

Please help... this is where i'm at at the moment:

<?php require_once('Connections/hairstation.php'); ?>
<?php
mysql_select_db($database_hairstation, $hairstation);
$query_prices = "SELECT * FROM categories, products WHERE categories.categoryID = products.categoryID";
$prices = mysql_query($query_prices, $hairstation) or die(mysql_error());
$row_prices = mysql_fetch_assoc($prices);
$totalRows_prices = mysql_num_rows($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php do { ?>
  <p><?php echo $row_prices['category']; ?>  </p>
  <p><?php echo $row_prices['product']; ?><?php echo $row_prices['price']; ?></p>
  <?php } while ($row_prices = mysql_fetch_assoc($prices)); ?>
</body>
</html>
<?php
mysql_free_result($prices);
?>

Psycho · March 19, 2010

<?php

require_once('Connections/hairstation.php');
mysql_select_db($database_hairstation, $hairstation);

$query = "SELECT *
          FROM products p
          JOIN categories c ON p.categoryID = c.categoryID
          ORDER BY c.catName, p.prodName";
$results = mysql_query($query, $hairstation) or die(mysql_error());

$currentCategory = '';
$outputHTML = '';
while($product = mysql_fetch_assoc($results))
{
    if($currentCategory!=$product['catName'])
    {
        $currentCategory = $product['catName'];
        $outputHTML .= "<b>{$currentCategory}</b><br />\n";
    }
        $outputHTML .= "{$product['prodName']}<br />\n";
}
mysql_free_result($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php echo $outputHTML; ?>
</body>
</html>

craigeves · March 19, 2010

Thanks for helping. That does the trick.... I did wonder though if it was possible to put the results in a table instead? Like the code below:

<?php

require_once('Connections/hairstation.php');
mysql_select_db($database_hairstation, $hairstation);
$query_prices = "SELECT *           FROM products            JOIN categories ON products.categoryID = categories.categoryID           ORDER BY categories.category, products.product ";
$prices = mysql_query($query_prices, $hairstation) or die(mysql_error());
$row_prices = mysql_fetch_assoc($prices);
$totalRows_prices = mysql_num_rows($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>

<table width="500" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td>Category 1</td>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td>Product 1</td>
    <td>Price</td>
    <td><a href="#">Edit</a></td>
    <td><a href="#">Delete</a></td>
  </tr>
  <tr>
    <td>Product2</td>
    <td>Price</td>
    <td><a href="#">Edit</a></td>
    <td><a href="#">Delete</a></td>
  </tr>
  <tr>
    <td>Product3</td>
    <td>Price</td>
    <td><a href="#">Edit</a></td>
    <td><a href="#">Delete</a></td>
  </tr>
  <tr>
    <td>Category 2</td>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td>Product4</td>
    <td>Price</td>
    <td><a href="#">Edit</a></td>
    <td><a href="#">Delete</a></td>
  </tr>
  <tr>
    <td>Product5</td>
    <td>Price</td>
    <td><a href="#">Edit</a></td>
    <td><a href="#">Delete</a></td>
  </tr>
  <tr>
    <td>Product6</td>
    <td>Price</td>
    <td><a href="#">Edit</a></td>
    <td><a href="#">Delete</a></td>
  </tr>
</table>

</body>
</html>
<?php
mysql_free_result($prices);
?>

craigeves · March 19, 2010

or even out of tables but written like this instead?

At the moment - the way below will put the correct products under the correct category, but will repeat the category if more than one product exists for that category. I want them all under the one category heading.

<?php require_once('Connections/hairstation.php'); 

mysql_select_db($database_hairstation, $hairstation);
$query_prices = "SELECT * FROM products            JOIN categories ON products.categoryID = categories.categoryID ORDER BY categories.category, products.product ";
$prices = mysql_query($query_prices, $hairstation) or die(mysql_error());
$row_prices = mysql_fetch_assoc($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<p>
  <?php do { ?>
    <?php echo $row_prices['category']; ?><br />
    <?php echo $row_prices['product']; ?> - <?php echo $row_prices['price']; ?><br />
    <br />
    <?php } while ($row_prices = mysql_fetch_assoc($prices)); ?>
</p>
<p> </p>
</body>
</html>
<?php
mysql_free_result($prices);
?>

sasa · March 19, 2010

before echo category just check is it diferent thet last one

look mjdamato's example

Psycho · March 19, 2010

or even out of tables but written like this instead?

At the moment - the way below will put the correct products under the correct category, but will repeat the category if more than one product exists for that category. I want them all under the one category heading.

As Sasa just stated, I gave you the logic to do exactly what you want. Just modify the code I provided to generate a table. Personally I feel I provided a much more logical, better constructed page, but you decide to flip back to your method. Makes no sense to me why you would grab the first record and then do a do/while loop. Also, since you are only using three fields, change the query to only get those three.

Without table:

<?php
  
require_once('Connections/hairstation.php');
mysql_select_db($database_hairstation, $hairstation);
  
$query = "SELECT c.category, p.product, p.price
          FROM products p
          JOIN categories c ON p.categoryID = c.categoryID
          ORDER BY c.category, p.product";
$results = mysql_query($query, $hairstation) or die(mysql_error());
  
$currentCategory = '';
$outputHTML = '';
while($product = mysql_fetch_assoc($results))
{
    if($currentCategory!=$product['category'])
    {
        $currentCategory = $product['category'];
        $outputHTML .= "{$currentCategory}<br />\n";
    }
    $outputHTML .= "{$product['product']} - {$product['price']}<br />\n";
}
mysql_free_result($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php echo $outputHTML; ?>
</body>
</html>

Or with table

<?php

require_once('Connections/hairstation.php');
mysql_select_db($database_hairstation, $hairstation);

$query = "SELECT c.category, p.product, p.price, p.id
          FROM products p
          JOIN categories c ON p.categoryID = c.categoryID
          ORDER BY c.category, p.product";
$results = mysql_query($query, $hairstation) or die(mysql_error());

$currentCategory = '';
$outputHTML  = "<table width=\"500\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\n";
while($product = mysql_fetch_assoc($results))
{
    if($currentCategory!=$product['catName'])
    {
        $currentCategory = $product['category'];
        $outputHTML .= "  <tr><th colspan=\"4\">{$currentCategory}</th></tr>\n";
    }
        $outputHTML .= "{$product['product']} - {$product['price']}<br />\n";
        $outputHTML .= "  <tr>\n";
        $outputHTML .= "    <td>{$product['product']}</td>\n";
        $outputHTML .= "    <td>{$product['price']}</td>\n";
        $outputHTML .= "    <td><a href=\"editProduct?id={$product['id']}\">Edit</a></td>\n";
        $outputHTML .= "    <td><a href=\"deleteProduct?id={$product['id']}\">Delete</a></td>\n";
        $outputHTML .= "  </tr>\n";
}
$outputHTML  .= "<table>\n";
mysql_free_result($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php echo $outputHTML; ?>
</body>
</html>

craigeves · March 19, 2010

Thanks for this. I'm sure I'll work it out.

craigeves · March 25, 2010

I'm actually stumped.

Yes one of the replies does work - but I don't want to do it like that for a few reasons.

How do I make this work without it being a 'do/while loop'?

This is what I have:

SELECT * FROM categories, products WHERE categories.categoryID = products.categoryID

<?php do { ?>
    <?php echo $row_prices['category']; ?><br />
    <?php echo $row_prices['product']; ?> - <?php echo $row_prices['price']; ?><br />
    <br />
    <?php } while ($row_prices = mysql_fetch_assoc($prices)); ?>

Thanks in advance.

Psycho · March 25, 2010

Um, what?

Yes one of the replies does work - but I don't want to do it like that for a few reasons

Care to elaborate? I've taken the time to provide three different solutions and you can't even explain why it doesn't meet your needs. Then you post that crap code aboveand ask how to not make it a do/while loop

Why on earth would you go in and out of PHP code so many times when you could simply do this:

<?php

do {
    echo "{$row_prices['category']}<br />\n";
    echo "{$row_prices['product']} - {$row_prices['price']}<br />\n";
    echo "<br />\n";
} while ($row_prices = mysql_fetch_assoc($prices));

?>

So, what exactly is the problem with the do/while loop? Do you want to change it to a while loop, a foreach loop, what??? I really have no idea what you are wanting.

craigeves · March 25, 2010

I am brand new to Php & Sql - I am using Dreamweaver's built in PHP tools and Sam's Teach Yourself in 10 Minutes PHP and SQL books to help me learn along the way.

I want to group all products under each category as stated before and display it all on the same page - but I want it as simple as possible so I understand what is happening a bit more. That's why I keep referring back to my original code - but my original code does not group everything under it's category - it shows them seperate.

In response to your 'going in and out of php' comment - that's actually Dreamweaver writing it like that... and whilst I know it's the wrong way, it has also helped me learn alot already - so thanks for pointing out the correct way of writing it.

Would you be able to comment this version that you wrote so I can understand what's going on?

<?php
  
require_once('Connections/hairstation.php');
mysql_select_db($database_hairstation, $hairstation);
  
$query = "SELECT c.category, p.product, p.price
          FROM products p
          JOIN categories c ON p.categoryID = c.categoryID
          ORDER BY c.category, p.product";
$results = mysql_query($query, $hairstation) or die(mysql_error());
  
$currentCategory = '';
$outputHTML = '';
while($product = mysql_fetch_assoc($results))
{
    if($currentCategory!=$product['category'])
    {
        $currentCategory = $product['category'];
        $outputHTML .= "{$currentCategory}<br />\n";
    }
    $outputHTML .= "{$product['product']} - {$product['price']}<br />\n";
}
mysql_free_result($prices);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php echo $outputHTML; ?>
</body>
</html>

Psycho · March 26, 2010

Would you be able to comment this version that you wrote so I can understand what's going on?

THAT is what you should have asked. I'm more than happy to provide an explanation. And, trying to change code because you don't understand it is not a good way to learn. The best way to learn is to walk through unfamiliar code and figure out what it is doing. If you don't understand a command/funciton, look it up.

First off, you should always try to separate your logic (the PHP code that builds the content) from the presentation (the actual HTML code that is output to the browser. The only PHP code I typically have in my code after the HTML tag is just PHP echo statements of the content I created before that. In fact, I typically have the logic and the presentation as two separate files. there are many reasons why this is the preferred approach which is too much to go into here.

<?php
  
require_once('Connections/hairstation.php');
mysql_select_db($database_hairstation, $hairstation);
  
//Create a query to join the tables and ONLY retrieve
//the fields you need - no need to use *
//This query creates aliases for the table names of 'p' and 'c'
//So fields can be referenced like "p.category" instead of
//"products.category"
//Although you can "join" tables using the WHERE clause, the preferred
//method is to use proper JOINS that give you MUCH more flexibility 
$query = "SELECT c.category, p.product, p.price
          FROM products p
          JOIN categories c ON p.categoryID = c.categoryID
          ORDER BY c.category, p.product";
$results = mysql_query($query, $hairstation) or die(mysql_error());
  
//Create variables to use as a flag for tracking
//new categories and to store the output
$currentCategory = '';
$outputHTML = '';
//Get one record at a time from the query results
//until there are no more
while($product = mysql_fetch_assoc($results))
{
    //Test if the current record's category is a NEW category
    if($currentCategory!=$product['category'])
    {
        //It is a new category, set the flag and output the category
        //This block is only run for records where the current record's
        //category is different from the last record.
        $currentCategory = $product['category'];
        $outputHTML .= "{$currentCategory}<br />\n";
    }
    //Output the product name and price
    //This line is executed for every record
    $outputHTML .= "{$product['product']} - {$product['price']}<br />\n";
}
mysql_free_result($prices);
//The LOGIC is complete, now generate the actual page
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php echo $outputHTML; ?>
</body>
</html>

craigeves · March 26, 2010

Thanks so much for your help. Hopefully one day my code will be as good as yours.

Craig

Psycho · March 26, 2010

Thanks so much for your help. Hopefully one day my code will be as good as yours.

I'm glad you got it working. That's the whole point of the forum. I don't hold up my code as a correct standard for others to follow. But, when I do see someone who has code that is obviously following no standards (because they are learning) I will provide at least advice on my standards as a place to start. Good luck to you.

Sign In

Multiple Categories on one page

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information