PolSciG.E.D Posted September 3, 2010 Share Posted September 3, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.