Jump to content

[SOLVED] Complicated SELECT and Echo Command

Fluoresce

By Fluoresce
in PHP Coding Help

 Share

Recommended Posts

Fluoresce Regular Member

Fluoresce

Posted
Posted

The code below selects all of the categories in my database, counts how many products are available for each category, and echoes the results in alphabetical order. It looks like this:

 

Accessories (5)

All Apparel (6)

All Health and Beauty (15)

Children's Apparel (2)

Cosmetics (5)

Fragrances (5)

Men's Apparel (2)

Women's Apparel (2)

 

I want all of the related categories to appear together, like this:

 

All Apparel (6)

Children's Apparel (2)

Men's Apparel (2)

Women's Apparel (2)

 

All Health and Beauty (15)

Accessories (5)

Cosmetics (5)

Fragrances (5)

 

And I want the categories to be displayed in two columns.

 

Note that the Accessories category sits beneath the All Health and Beauty category, despite being alphabetically higher. I must therefore echo the primary categories (i.e., All Apparel, and All Health and Beauty) first, before echoing their subcategories beneath them.

 

Anyone know how to even start this project?

 

I have created a new column on my category table for category rank, and designated all categories as either Primary or secondary. I have also created another new column for category group, so that I can group the categories together. I just don't know how to select and echo it all in the way that I explained, in two columns, with primary categories first, followed by their subcategories.

 

Any help at all will be appreciated.

 

<?php

$conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR);  
$db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR);

$query = "SELECT tcat.cat, tcat.caturl, COUNT(tproduct.id) AS prod_count
          FROM tcat
          LEFT JOIN tproduct ON tcat.catid = tproduct.catid
          GROUP BY tcat.catid
          ORDER BY tcat.cat ASC";

$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

if(mysql_num_rows($result) >= 1) {

while($row = mysql_fetch_assoc($result)) {

echo "<a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")<br />";

} // end while
} // end if

else {
echo "None";
}

?>

Fluoresce Regular Member

Fluoresce

Posted
  • Author
Posted

I have managed to find a way of getting the categories to appear in the order that I want by adding a column called catorder and giving the categories values which dictate the order in which to display:

 

All Apparel (6)

Children's Apparel (2)

Men's Apparel (2)

Women's Apparel (2)

All Health and Beauty (15)

Accessories (5)

Cosmetics (5)

Fragrances (5)

 

Now, all I need help on is learning how to do the following:

 

1) How can I get the categories to be displayed in their respective groups, capped by a heading, like this:

 

Apparel

 

All Apparel (6)

Children's Apparel (2)

Men's Apparel (2)

Women's Apparel (2)

 

Health and Beauty

 

All Health and Beauty (15)

Accessories (5)

Cosmetics (5)

Fragrances (5)

 

2) And how can I get the results to echo in a two-column table?

 

Any help will be much appreciated.

 

My code so far:

 

<?php

$conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR);  
$db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR);

$query = "SELECT tcat.cat, tcat.caturl, COUNT(tproduct.id) AS prod_count
          FROM tcat
          LEFT JOIN tproduct ON tcat.catid = tproduct.catid
          GROUP BY tcat.catid
          ORDER BY tcat.catorder ASC";


$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

if(mysql_num_rows($result) >= 1) {

while($row = mysql_fetch_assoc($result)) {

echo "<a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")<br />";


} // end while
} // end if

else {
echo "None";
}

?>

Fluoresce Regular Member

Fluoresce

Posted
  • Author
Posted

I will try to explain what I want in a more simplified way, since nobody seems to be able to help . . .

 

This is the markup and the PHP code on my page, simplified:

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="cs" lang="cs">

<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<meta http-equiv="content-language" content="cs" />
<meta name="robots" content="all, follow" />
<meta name="author" content="" />
<meta name="description" content="description" />
<meta name="keywords" content="keywords" />
<link rel="stylesheet" type="text/css" href="site.css" />
<title></title>
</head>

<body>

<div id="container">

<div id="main">

<h1>Categories</h1>

<?php

$conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR);

$query = "SELECT tcat.cat, tcat.caturl, COUNT(tproduct.id) AS prod_count
FROM tcat
LEFT JOIN tproduct ON tcat.catid = tproduct.catid
GROUP BY tcat.catid
ORDER BY tcat.catorder ASC";

$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

if(mysql_num_rows($result) >= 1) {

while($row = mysql_fetch_assoc($result)) {

echo "<table>";
echo "<tr>";
echo "<td><a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")<br />";

} // end while

echo "</td>";
echo "</tr>";
echo "</table>";

} // end if

else {
echo "None";
}

?>

</div>

<div id="right"></div>

<div class="clear"></div>

<div id="footer"></div>

</div>

</body>

</html>

 

The PHP code produces a list of categories in a table which looks like this:

 

All Apparel (6)

Children's Apparel (2)

Men's Apparel (2)

Women's Apparel (2)

All Health and Beauty (15)

Accessories (5)

Cosmetics (5)

Fragrances (5)

 

I would like to split these categories into two columns. More importantly, I would like to separate the related groups of categories and place a heading over each group, like this:

 

Apparel

 

All Apparel (6)

Children's Apparel (2)

Men's Apparel (2)

Women's Apparel (2)

 

Health and Beauty

 

All Health and Beauty (15)

Accessories (5)

Cosmetics (5)

Fragrances (5)

 

I know the XHTML/CSS part; that's easy. I just don't know much about PHP.

 

I have given group IDs to the categories. Will that help with the grouping? Even so, I don't know how to code the grouping and how to add a heading before each group.

 

Here's what I think the code should do:

 

A heading must be echoed, after which a while loop must find the right group of categories to go under the heading. Then, there must be a break to the while loop, before the process is restarted. This must happen in two columns of the table.

 

Can anyone help, please? I'm so stuck.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.