Link to home
Start Free TrialLog in
Avatar of vrmetro
vrmetroFlag for United States of America

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
/*** 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>";
}

Open in new window

SOLUTION
Avatar of Iced-evil
Iced-evil
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You also need to add additional formatting for the category, something like :
echo "<TR class='category'><TD colspan=12>".$tempcat."</TD></TR>";

Not shure about the colspan 12, you will need to count yourself to make shure it is correct.
Avatar of vrmetro

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);

Open in new window

Avatar of vrmetro

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
Avatar of vrmetro

ASKER

shoot, i changed the $v's to $vv
Avatar of vrmetro

ASKER

Going to post again more clearly in another message.  Thanks.