Loop Madness (I can't figure it out)

[PolSciG.E.D]

Hi there,

 

I signed up to this web site because I've run into a problem I just can't figure out. I'm not a programmer or anything like that, so maybe that explains my inability. Let's say I have a SQLite database with two columns (Department & Category). I want to figure out through PHP and SQL which departments have what category in common. There are about 14 categories and 70 departments. I need the output to look like this:

 

{"id":"AgricultureandFood", "text":"Agriculture and Food","connections":["DepartmentforCommunities","TreasuryandFinance","ChemistryCentre"]}]

 

Where connections are the categories the departments have in common. Currently, I have the following code that seems to get something done but not nearly what I need:

 

$dbh = new PDO("sqlite:kpi_database.db");

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$department = "SELECT DISTINCT Department FROM Efficiency";
$department_result = $dbh->query($department);

$category = "SELECT DISTINCT Category FROM Efficiency";
$category_result = $dbh->query($category);

$data = array();

foreach($category_result as $category) {
$link = "SELECT DISTINCT Department FROM Efficiency WHERE Category = '" . $category[0] . "'";
$link_result = $dbh->query($link);

foreach($department_result as $department) {
$item = array('id' => preg_replace('/\s+?/', '', $department[0]), 'text' => $department[0], 'connections' => array());

foreach($link_result as $link) {
	$item['connections'][] = ($_GET['multi'] == 1 ? : preg_replace('/\s+?/', '', $link[0]));	
}

$data[] = $item;
}
}

print json_encode($data);

 

I think the problem is that I can't think inside of the loops properly, and I'm missing something simple. If anyone is interested, I worked out this example from the Moowheel example code (that's what I'm trying to make out of my own database). I really appreciate any help with this.