Forum Moderators: coopster
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 display these results differently. I want to:
1) separate related groups of categories and cap them with 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 I want the results to be displayed in a table with two columns.
Can anyone help, please? I don't know how to do this at all.
--------------------
<?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";
}
?>
<body>
<table>
<?php
//do all of your php and mysql
//then to echo your data
while(you have rows in your query return){
echo("<tr><td>your row 1 column 1 here</td><td>your row 1 column 2 here</td></tr>");
}
?>
</table>
</body>
</html>
I don't really follow how you are getting your stuff back because you echo a link and then just another row and a line return. If you are a little more specific I can help more.
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.
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
What you need is
if(mysql_num_rows($result) >= 1) {
echo "<table>";
while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td><a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")<br />";
echo "</td>";
echo "</tr>";
} // end while
echo "</table>";
} // end if
That will put everything in a new row. You had your start table tag in the while so PHP was echoing the <table> tag everytime you have a result in your query. This should put it all on one line after the other. Like I asked earlier, is there something in the database that can tell you whether it is apparel or health and beauty. Let me know.
"Do the ones that go in apparel and the ones that go in health and beauty have some sort of unique identifier?"
Yes. I added a new column to my database category table, called it catgroupid, and gave all categories a group ID. The group to which each category belongs can now be identified.
"Also when you echo your table, your html is incorrect."
Sorry. That was a silly mistake. It should have been like this:
----------------------------------
if(mysql_num_rows($result) >= 1) {
echo "<table>";
echo "<tr>";
echo "<td>";
while($row = mysql_fetch_assoc($result)) {
echo "<a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")<br />";
} // end while
echo "</td>";
echo "</tr>";
echo "</table>";
} // end if
--------------------------------
So, how do I echo the categories in groups, capped by their headings, in a single, two-column table?
Only the where clause is whatever your storing in that column for catgroupid
then in your php echo it like this
echo("<table>");
while(you have results){
echo("<tr><td>your row</td></tr>");
}
echo("</table>");
then do the exact same thing for the other category
try some code and if you can't get it let me know.
tcat:
catid
cat
caturl
catorder
catgroupid
tproduct:
id
product
catid
And here's an idea . . .
What if I created an array containing all of the headings:
$headingarray = array('Apparel','Health and Beauty','etc.');
Could I echo a heading, followed by its categories based on catgroupid, before repeating the process?
That might sound stupid to you guys. But, unfortunately, that's the scope of my PHP know-how.
I am so confused right now! I will go off to think about what I need to do.
Thank you for your suggestions. I appreciate your contribution.
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
if(mysql_num_rows($result) >= 1) {
echo("table");
echo("<tr><td>Either Apparel or All Heath and Beauty depending on your sql query</td></tr>");
while($row = mysql_fetch_assoc($result)) {
echo "<tr><td><a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")</td></tr>";
}
echo("</table>");
Then do that again for the other category
there may be a bug but it's close
What if I created an array with all of the headings, like this:
$headings = array ('Apparel','Automotive','Babies and Kids','Books and Magazines','Consumer Electronics','Health and Beauty','Home and Garden','Jewelry and Gifts','Music and Movies','Pets and Animals','Sports and Fitness','Telecommunications','Virtual Malls');
How can I do a foreach loop which will know which categories must be echoed after each heading? And how can I then break the loop whenever a category doesn't match the current heading, before adding a space and re-running the loop for another heading?
Maybe that doesn't even make sense . . .
//create the array
$categoryArray = array();
$query = query = "SELECT catid, catorder, cat, catgroupid
FROM tcat;
GROUP BY tcat.catid
ORDER BY tcat.catorder ASC";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
//populate the array
while($row = mysql_fetch_assoc($result)) {
$tempid = $row['catgroupid'];
$tempCat = $row['cat'];
$categoryArray[$tempid] = $tempCat;
}//while
//then do this
foreach($categoryArray as $key => $i){
$query = query = "SELECT tcat.cat, tcat.caturl, COUNT(tproduct.id) AS prod_count
FROM tcat
WHERE tcat.catgroupid = '{$key}'
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);
echo("table");
echo("<tr><td>$i</td></tr>");
while($row = mysql_fetch_assoc($result)) {
echo "<tr><td><a href=\"" . $row['caturl'] . "\">" . $row['cat'] . "</a> (" . $row['prod_count'] . ")</td></tr>";
}
echo("</table><br><br>");
}//foreach
I didn't test it but im pretty close
Although your method was fine, in the end, I found a shorter way of doing it:
------------------------------------
<?php
$conn = mysql_connect('localhost','root') or trigger_error("SQL", E_USER_ERROR);
mysql_select_db('heru_ctyi', $conn) or trigger_error("SQL", E_USER_ERROR);
$query = "SELECT tcat.catheading, tcat.cat, tcat.category, COUNT(tcpncat.id) AS cpn_count
FROM tcat
LEFT JOIN tcpncat ON tcat.catid = tcpncat.catid
GROUP BY tcat.catid
ORDER BY tcat.cattype ASC";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
$last_heading = '';
while ($row = mysql_fetch_assoc($result))
{
if ($row['catheading'] != $last_heading)
{
if ($last_heading)
echo "</p>";
$last_heading = $row['catheading'];
echo "<h3>{$last_heading}</h3><p>";
}
else
echo "<br />";
echo "<a href=\"" . $row['category'] . "\">" . $row['cat'] . "</a> (" . $row['cpn_count'] . ")";
}
echo "</p>";
?>
------------------------------------
The code echoes my groups of categories like this:
Apparel
All Apparel/Clothing (11)
Women's Apparel (7)
Men's Apparel (2)
Children's Apparel (2)
Automotive
All Automotive (13)
Auto Insurance (7)
Auto Parts (6)
Babies and Kids
All Babies and Kids (9)
Games and Toys (3)
Baby Gear (6)
Altogether, there are 13 such category groups which run all the way down my page.
Can anyone tell me, please, how I can echo these results into two table columns?
