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"; } ?> PHP:
a dirty way would be to used keyword and break it into 2 sql statement SELECT tcat.cat, tcat.caturl, COUNT(tproduct.id) AS prod_count FROM tcat LEFT JOIN tproduct tcat.catON tcat.catid = tproduct.catid where tcat.cat like '%Apparel%' GROUP BY tcat.catid ORDER BY tcat.cat ASC Code (markup): SELECT tcat.cat, tcat.caturl, COUNT(tproduct.id) AS prod_count FROM tcat LEFT JOIN tproduct tcat.catON tcat.catid = tproduct.catid where tcat.cat not like '%Apparel%' GROUP BY tcat.catid ORDER BY tcat.cat ASC Code (markup):
Thanks for the effort, man. I appreciate it. But I can't use that method. I've got loads of categories, and I can't have all those commands on one page.
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"; } ?> PHP:
I will try to explain my problem in a more simplified way, since it seems that nobody knows how 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> Code (markup): <?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"; } ?> PHP: </div> <div id="right"></div> <div class="clear"></div> <div id="footer"></div> </div> </body> </html> Code (markup): 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.
I am too lazy to type up detailed code, but here's the basic jist of what you need to do: Your query should produce a column containing the heading, and a column containing the item's name (and whatever else you want to display, but at least those two). It should be sorted first by heading, and then by item. Then you can just do this: $st = mysql_query($sql) or die(mysql_error()); $last_heading = ''; while ($row = mysql_fetch_assoc($st)) { if ($row['heading'] != $last_heading) { if ($last_heading) echo "</p>"; $last_heading = $row['heading']; echo "<h1>{$last_heading}</h1><p>"; } else echo "<br>"; echo $row['name']; } echo "</p>"; Code (markup):
Hi, smallPotatoes. Thanks for the help. Let's say that this is my 2-table database: Category_Table: catid cat_name cat_url cat_group Product_Table: id product Are you saying that I should add another column called cat_heading to Category_Table and give every category to appear beneath a particular heading the same cat_heading? If you can explain what you mean in a bit more detail, I will appreciate it very much. And if you can explain the code that you gave, I will appreciate that, too.
Thanks, SmallPotatoes! Your code worked. One rep point for you. I was wondering if you could please explain the piece of code that you gave. I have been trying to figure it out, but I'm new to PHP, so it's hard to understand.
Here's a commented version that displays the output in two columns: // execute SQL query $st = mysql_query($sql) or die(mysql_error()); // a counter to keep track of how many items have been displayed, so // we can tell when to switch to the next column $items_displayed = 0; // and when to switch to the next column? Take the total number of results, // divide by two to get the halfway mark, and switch at the next heading // after reaching that point. $when_to_switch = mysql_num_rows($st) / 2; // keep track of the heading of the previous item displayed, in order to // compare it with the heading of the current item. If they are different, // then it's time to display a new heading. If they're the same, then we're // still under the same heading so no need to display anything. $last_heading = ''; // start the table echo "<table><tr><td>"; // loop through all the results from the query while ($row = mysql_fetch_assoc($st)) { // did the heading change? If so, time to do some work if ($row['heading'] != $last_heading) { // if there is a value for $last_heading, then we know at least one // heading has been displayed, which means at least one item has // been displayed, which means there is an open paragraph tag to // close, and maybe it's time to start a new column as well. if ($last_heading) { echo "</p>"; // time for a new column? if ($items_displayed > $when_to_switch) { echo "</td><td>"; // we only want two columns total, so make sure we don't // switch again, by setting $when_to_switch to the largest // possible number, theoretically ensuring it is almost never // going to be reached $when_to_switch = PHP_INT_MAX; } } // out with the old heading, in with the new $last_heading = $row['heading']; echo "<h1>{$last_heading}</h1><p>"; } // heading did not change, so this is just another item in the same // category as the last item. All we need is a <br> tag. else echo "<br>"; // and finally display the item itself echo $row['name']; // keep track of how many items have been displayed thus far, so // we know when to switch columns $items_displayed++; } // close up all the tags echo "</p></td></tr></table>"; Code (markup): I haven't actually tried running the code but it should be fine unless I typo'ed somewhere.