• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

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

0
vrmetro
Asked:
vrmetro
  • 4
  • 2
2 Solutions
 
Iced-evilCommented:
Try the below code to craete the output
$tempcat = ""
foreach($data as $v){
   if $tempcat = $v['cat']{
      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>";
   } else {
      $tempcat = $v['cat'];
      echo $tempcat;
      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

0
 
MMDeveloperCommented:

/*** Set rotation of row colors, first a counter ***/
                $i=0;
		$catsData = array();
                /*** 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
			}
		}

Open in new window

0
 
Iced-evilCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
vrmetroAuthor Commented:
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

0
 
vrmetroAuthor Commented:
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
0
 
vrmetroAuthor Commented:
shoot, i changed the $v's to $vv
0
 
vrmetroAuthor Commented:
Going to post again more clearly in another message.  Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now