grouping output from DB

[scrupul0us]

I'm trying to reconcile a bunch of documents/images that were deleted on a client server

I'm outputting a list of files that a number of clients have uploaded to a website and where records for the files were created in a DB. Some files still exist, the other have been unfortunately deleted and we need to contact the clients to get the images/documents back

right now everything outputs one record at a time and is a pain to work with

what I'd like todo is alter the output such that i can group all the files and related information by the client information to reduce the redundant info and to finally be able to automate generating emails with all the info in them (this i can do)

heres what I'm working with (sensitive info has been *** out):

<?php
// Report all PHP errors
error_reporting(E_ALL);

function array_remove_empty($arr)
{
	$narr = array();
	while(list($key, $val) = each($arr))
	{
		if (is_array($val))
		{
			$val = array_remove_empty($val);
			if (count($val)!=0)
			{
			$narr[$key] = $val;
			}
		}
		else 
		{
			if (trim($val) != "")
			{
			$narr[$key] = $val;
			}
		}
	}
	unset($arr);
	return $narr;
}

function status($image,$ext,$file,$location,$brokerEmail,$brokerName,$broker,$brokerPhone,$cnt)
{
	if ($cnt % 2 == 0)
	{
	return "<div style=\"background-color:#FFFFCC\"><br><img src=\"".$ext.".png\" alt=\"".$ext."\" title=\"".$ext."\" />&nbsp;<img src=\"".$image."\" alt=\"\" /> <b>".$file."</b><br/>[$location] [<a href=\"mailto:".$brokerEmail."\">$brokerName @ $broker</a>] [$brokerPhone]<br><br></div>\n\n";
	}
	else
	{
	return "<div style=\"background-color:#DBEEF3\"><br><img src=\"".$ext.".png\" alt=\"".$ext."\" title=\"".$ext."\" />&nbsp;<img src=\"".$image."\" alt=\"\" /> <b>".$file."</b><br/>[$location] [<a href=\"mailto:".$brokerEmail."\">$brokerName @ $broker</a>] [$brokerPhone]<br><br></div>\n\n";
	}

}

$dbusername='***';
$dbpassword='***';
$servername='***';
$link = mssql_connect ($servername,$dbusername,$dbpassword);

$query = 'select fileName, OfficeParkName, Broker, BrokerFirstName, BrokerLastName, BrokerEmail, BrokerPhone from cms_cpl c inner join CMS_cpl_files cf on c.itemId=cf.itemid order by Broker asc';

$result = mssql_query($query,$link);

$n = 0;
$y = 0;

$cnt = 0;
$doc = '***';
$img = '***';
$typ = array();

?>
<a href="?show_all=1" alt="Show All">Show All</a> | <a href="?show_all=0" alt="Show Missing">Show Missing</a>
<hr>

<?php
while ($row = mssql_fetch_array($result))
{

$file = $row['fileName'];
$location = $row['OfficeParkName'];
$broker = $row['Broker'];
$brokerName = $row['BrokerFirstName']." ".$row['BrokerLastName'];
$brokerPhone = $row['BrokerPhone'];
$brokerEmail = $row['BrokerEmail'];

if(strrpos($file,'.')) 
{ 
$dot = strrpos($file,'.') +1;
$ext = substr($file,$dot);
}



if (file_exists($img.$file) && isset($_GET['show_all']) && $_GET['show_all']=='1')
{
echo status("check.png",$ext,$file,$location,$brokerEmail,$brokerName,$broker,$brokerPhone,$cnt);
$y++;
$cnt++;
}

elseif (file_exists($doc.$file) && isset($_GET['show_all']) && $_GET['show_all']=='1')
{
echo status("check.png",$ext,$file,$location,$brokerEmail,$brokerName,$broker,$brokerPhone,$cnt);
$y++;
$cnt++;
}

elseif (!file_exists($img.$file))
{
echo status("error.png",$ext,$file,$location,$brokerEmail,$brokerName,$broker,$brokerPhone,$cnt);
$n++;
$cnt++;
}

elseif (!file_exists($doc.$file))
{
echo status("error.png",$ext,$file,$location,$brokerEmail,$brokerName,$broker,$brokerPhone,$cnt);
$n++;
$cnt++;
}

else
{
echo "Error<br/>";
}

if (!in_array(strtolower($ext),$typ))
{
$typ[] = strtolower($ext);
}
}

$t = $n + $y;
echo "<hr>";
echo "<img src=\"error.png\" alt=\"\" />: $n <br/>";
echo "<img src=\"check.png\" alt=\"\" />: $y <br/>";
echo "<img src=\"document.png\" alt=\"\" />: $t <br/>";

?>

so basically I'm looking for output to be like this:

-----------------------------one client------------------------------------
location - email link - phone <--- group output based on this
<typ><ERROR><filename> <-- loop out this info

location - email link - phone <--- group output based on this

<typ><CHECK><filename> <-- loop out this info

you can see that I also provide the ability to show both ALL files and just missing... the missing should be grouped separately... so each client could inevitably have two containers of output

i can handle all the fancy output formatting... i really just need to know HOW to do this kind of grouping

hopefully this makes sense to someone... im happy provide any information that i havent included and thank everyone who helps

[leatherback]

Are you looking for something like:

$query = 'select fileName, OfficeParkName, Broker, BrokerFirstName, BrokerLastName, BrokerEmail, BrokerPhone from cms_cpl c inner join CMS_cpl_files cf on c.itemId=cf.itemid group by cf.itemid order by Broker asc'; 

Not: I am not sure whether you should order first and then group, or reverse

[scrupul0us]

i dont think this is achievable via the query... i tried what you suggested tho and it didnt work, it blew up... not sure if it being MSSQL has anything todo with that

[scrupul0us]

any ideas? anyone trying anything?

only thing i can think of is to maybe parse everything into another multi-dim keyed array

[scrupul0us]

OK, nested queries seemed to get me MUCH closer to my goal:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>***</title>
<style type="text/css">
html, body {
font-family: verdana;
background-color: #E5E4DD;
margin: 0px 15px 15px 15px;
padding-top: 15px;
}
#stats {
position: absolute;
top:0px;
right:0px;
}
hr {
padding:0;
margin:0;
background-color: #ACA899;
}
.row1 {
padding-left: 5px; 
background-color:#FFFFFF; 
border: 1px solid #ACA899; 
border-bottom: none;
}

.row2 {
padding-left: 5px;
background-color:#ECE9D8; 
border: 1px solid #ACA899; 
border-bottom: none;
}
</style>
</head>

<body>
<?php
// Report all PHP errors
error_reporting(E_ALL);

function array_remove_empty($arr)
{
	$narr = array();
	while(list($key, $val) = each($arr))
	{
		if (is_array($val))
		{
			$val = array_remove_empty($val);
			if (count($val)!=0)
			{
			$narr[$key] = $val;
			}
		}
		else 
		{
			if (trim($val) != "")
			{
			$narr[$key] = $val;
			}
		}
	}
	unset($arr);
	return $narr;
}

function status($image,$ext,$file,$cnt)
{
	return "<img src=\"".$ext.".png\" alt=\"[".$ext."]\" title=\"[".$ext."]\" />&nbsp;<img src=\"".$image.".png\" alt=\"[".$image."]\" title=\"[".$image."]\" /> ".$file."<br/>\n\n";
}

$dbusername='***';
$dbpassword='***';
$servername='***';
$link = mssql_connect ($servername,$dbusername,$dbpassword);



$n = 0;
$y = 0;

$cnt = 1;
$alt = 0;
$doc = '***';
$img = '***';
$typ = array();
?>
<a href="?show_all=1" alt="Show All">Show All</a> | <a href="?show_all=0" alt="Show Missing">Show Missing</a>
<?php

$query = 'select distinct Broker, OfficeParkName, BrokerFirstName, BrokerLastName, BrokerEmail, BrokerPhone from cms_cpl c inner join CMS_cpl_files cf on c.itemId=cf.itemid order by Broker asc';
$result = mssql_query($query,$link);
while ($row = mssql_fetch_array($result))
{

if (isset($_GET['debug']) && $_GET['debug']==1)
{
echo "<pre>";
print_r($row);
echo "</pre>";
}
else
{

	$location = $row['OfficeParkName'];
	$broker = $row['Broker'];
	$brokerName = $row['BrokerFirstName']." ".$row['BrokerLastName'];
	$brokerPhone = $row['BrokerPhone'];
	$brokerEmail = $row['BrokerEmail'];

	if ($alt % 2 == 0)
	{
		echo "<div class=\"row1\"><br/>";
	}
	else
	{
		echo "<div class=\"row2\"><br/>";
	}

	echo "<b>[$broker] [<a href=\"mailto:".$brokerEmail."\">$brokerName</a>] [$brokerPhone]<br/>[$location]</b><br/><br/>";

	$query2 = 'select fileName from cms_cpl c inner join CMS_cpl_files cf on c.itemId=cf.itemid where OfficeParkName=\''.$location.'\' order by OfficeParkName asc';
	$result2 = mssql_query($query2,$link);

	while ($row2 = mssql_fetch_array($result2))
	{

		$file = $row2['fileName'];

		if(strrpos($file,'.')) 
		{ 
			$dot = strrpos($file,'.') + 1;
			$ext = strtolower(substr($file,$dot));
		}	

		if (isset($_GET['show_all']) && $_GET['show_all']==1)
		{
			if (file_exists($img.$file))
			{
				echo status("check",$ext,$file,$cnt);
				$y++;
				$cnt++;
			}
			elseif (file_exists($doc.$file))
			{
				echo status("check",$ext,$file,$cnt);
				$y++;
				$cnt++;
			}
			elseif (!file_exists($img.$file))
			{
				echo status("error",$ext,$file,$cnt);
				$n++;
				$cnt++;

			}
			elseif (!file_exists($doc.$file))
			{
				echo status("error",$ext,$file,$cnt);
				$n++;
				$cnt++;
			}
		}
		elseif ((isset($_GET['show_all']) && $_GET['show_all']==0) || (!isset($_GET['show_all'])))
		{
			if (file_exists($img.$file))
			{
				continue; //Move to the next file in the loop
			}
			elseif (file_exists($doc.$file))
			{
				continue; //Move to the next file in the loop
			}			
			elseif (!file_exists($img.$file))
			{
				echo status("error",$ext,$file,$cnt);
				$n++;
				$cnt++;
			}
			elseif (!file_exists($doc.$file))
			{
				echo status("error",$ext,$file,$cnt);
				$n++;
				$cnt++;
			}
		}

		if (!in_array(strtolower($ext),$typ))
		{
			$typ[] = $ext; //Build document type array
		}


	}
	$alt++;	
	echo "<br/></div>\n\n";
}
}
mssql_free_result($result);
mssql_free_result($result2);

$t = $n + $y;

echo "<hr>";
echo "<div id=\"stats\">[ <img src=\"error.png\" alt=\"[error]\" title=\"[error]\" />: $n | <img src=\"check.png\" alt=\"[check]\" title=\"[check]\" />: $y | <img src=\"document.png\" alt=\"[total]\" title=\"[total]\" />: $t ]</div>";

?>
</body>
</html>

only thing that kinda sucks is that if the PM decides to see just missing images, there are empty containers outputted if an entire group has no missing images... I guess i would have to make another nested query?

[leatherback]

Hi Scrupulous,

Not sure what you are trying to group and how.

Basically: If you do a join between the tables from which you need info, query the database. Then you get all the records right? If they are sorted by the 'grouping' field, you can then test whether the grouping field is the same as last. If it is, you do not output it. If it isn't, you output a new header, and set $last_grouping = $row['MyGroupingField'].

Sorry, I am not completely sure on what you are trying to achieve from which tables and fields...

[scrupul0us]

no apologies necessary... your idea worked well... i used it in conjunction with metaphone (thanks laserlight) to compare the previous to the current (As there are small descrepancies in some of the names) and viola its working rather well

looking back i would have written this better having known what the client wanted in more depth (got the finite details as i went along, isnt that the way it always goes)... i would have rather processed each locations assets against the filesystem and then went from there... that way I could determine if:
-the property has no images, all images, mix
-the realtor has properties which match the above conditions

and then make available viewing that matches the conditions... instead of my kitbashed setup...

i guess i could just rewrite it... i love scripting anyways but for now, this will work well

thanks for everyones help and ideas!!!