vrmetro
asked on
php sql, pull data out group by category, print category name once, then values - same for each cat
Hello,
I am using PHP to pull data from an SQL database. There are 3 categories, each category has 5 or so entries. I want to pull the data form sql, print the category name once then print the 5 entries under this category, then go to the next category. Below is what I have but will only group by category, and print all values ... what I need is like below:
CATEGORY NAME
VALUE
VALUE
VALUE
VALUE
CATEGORY NAME
VALUE
VALUE
VALUIE
I am using PHP to pull data from an SQL database. There are 3 categories, each category has 5 or so entries. I want to pull the data form sql, print the category name once then print the 5 entries under this category, then go to the next category. Below is what I have but will only group by category, and print all values ... what I need is like below:
CATEGORY NAME
VALUE
VALUE
VALUE
VALUE
CATEGORY NAME
VALUE
VALUE
VALUIE
/*** Set rotation of row colors, first a counter ***/
$i=0;
/*** the CSS class names ***/
$colors = array('dark', 'light');
// Connect to database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'intranet';
mysql_select_db($dbname, $conn);
$result = mysql_query("SELECT * FROM fw_sites GROUP BY cat ORDER BY cat");
$data=array();
if( mysql_num_rows($result) > 0 )
{
while($row = mysql_fetch_array($result))
{
$data[]=$row;
}
}
foreach($data as $v){
echo "// NEED CAT NAME ONCE HERE";
echo "<TR class='gridview_".$colors[$i++ % 2]."'><TD>".$v['country']."</TD><TD>".$v['firstname']."</TD><TD>".$v['lastname']."</TD><TD>".$v['company']."</td><td>".$v['street']."</TD><TD>".$v['city']."</TD><TD>".$v['state']."</TD><TD>".$v['zip']."</TD><TD><a class=\"normal\" href='mailto:".$v['email']."'>".$v['email']."</a></TD><TD>".$v['phone']."</TD><TD>".$v['optin']."</TD></TR>";
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I went with below and doesn't seem to work
<?php
/*** Set rotation of row colors, first a counter ***/
$i=0;
/*** the CSS class names ***/
$colors = array('dark', 'light');
// Connect to database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'intranet';
mysql_select_db($dbname, $conn);
$result = mysql_query("SELECT * FROM fw_sites GROUP BY cat ORDER BY cat");
$data=array();
if( mysql_num_rows($result) > 0 )
{
while($row = mysql_fetch_array($result))
{
$catsData[$row["cat"]][] = $row;
}
}
foreach($catsData as $k => $v){
//$k = category name
//$v = array of rows
//foreach ($v as $kk => $vv) {
//$vv = individual row
echo "<span class=\"header\">".$k['cat']."</span>";
echo "<TABLE class=\"gridview\"><THEAD>";
echo "<TR class=\"gridview_top\" align=\"center\"><TH colspan=\"2\"><span style=\"width:100%\"><label for=\"print\" class=\"title\">".$v['cat_desc']."</label></span></TH></tr>";
echo "<TR class=\"gridview_header\"><th>NAME</th><TH>SITE</TH></TR>";
echo "</thead><TBODY>";
echo "<TR class='gridview_".$colors[$i++ % 2]."'><TD>".$v['name']."</TD><TD><a href=\"".$v['site']."\" target=\"_new\">".$v['site']."</TD></TR>";
echo "</tbody></table>";
}
mysql_close($conn);
ASKER
More specifically...
Pulls out the categories but only prints the first letter of the category name. Breaks them up, but only prints out 1 for each cat.
Screen shot below.
First cat name is Staging (listed as "S"), second TEST (listed as T)
Thanks!
ss.jpg
Pulls out the categories but only prints the first letter of the category name. Breaks them up, but only prints out 1 for each cat.
Screen shot below.
First cat name is Staging (listed as "S"), second TEST (listed as T)
Thanks!
ss.jpg
ASKER
shoot, i changed the $v's to $vv
ASKER
Going to post again more clearly in another message. Thanks.
echo "<TR class='category'><TD colspan=12>".$tempcat."</T
Not shure about the colspan 12, you will need to count yourself to make shure it is correct.