public static function getSectors()
{
$dbc = new DBConnection();
$sectors = array();
$selected = array();
$query = $dbc->getDb()->prepare("SELECT * FROM sectors ORDER BY level, name");
$query->execute();
if ($query->rowCount() > 0) {
$root_sectors = array();
$parent_sectors = array();
$res = $query->fetchAll(PDO::FETCH_ASSOC);
foreach ($res as $row) {
if ($row['level'] == 0) {
$root_sectors[$row['id']] = $row;
} else {
$parent_sectors[$row['parent_id']][$row['id']] = $row;
}
}
self::constructSectors($root_sectors, $parent_sectors, $sectors);
}
return $sectors;
}
<?php
ob_start();
session_start();
require_once 'modules/DBConnection.php';
//save data
if ($_REQUEST['save']) {
if ($_REQUEST['name'] && $_REQUEST['terms'] && $_REQUEST['sectors'] && is_array($_REQUEST['sectors'])) {
$dbc = new DBConnection();
$name = $_REQUEST['name'];
//update user and data
if (isset($_SESSION['user_id']) && intval($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
$query = $dbc->getDb()->prepare("UPDATE users SET name = :name WHERE id = :user_id");
$query->bindValue(':name', $name);
$query->bindValue(':user_id', $user_id);
$query->execute();
//delete from user_sectors and add new ones
$query = $dbc->getDb()->prepare("DELETE FROM user_sectors WHERE user_id = :user_id");
$query->bindValue(':user_id', $user_id);
$query->execute();
foreach ($_REQUEST['sectors'] as $sector_id) {
$query = $dbc->getDb()->prepare("INSERT INTO user_sectors VALUES(:user_id, :sector_id)");
$query->bindValue(':user_id', $user_id);
$query->bindValue(':sector_id', $sector_id);
$query->execute();
}
$_SESSION['user_name'] = $name;
$_SESSION['sectors'] = $_REQUEST['sectors'];
} else {
$query = $dbc->getDb()->prepare("INSERT INTO users(name) VALUES(:name)");
<?php
chdir('../');
require_once 'modules/DBConnection.php';
$dbc = new DBConnection();
print "*********** CREATE USERS TABLE ****************\n";
$sql = "CREATE TABLE users ( \n id INT NOT NULL AUTO_INCREMENT,\n name VARCHAR(255) NOT NULL,\n PRIMARY KEY (id))";
$dbc->getDb()->exec($sql);
print "Users table created \n\n";
print "*********** CREATE SECTORS TABLE ****************\n";
$sql = "CREATE TABLE sectors ( \n id INT NOT NULL AUTO_INCREMENT,\n parent_id INT,\n name VARCHAR(255) NOT NULL,\n level INT NOT NULL,\n PRIMARY KEY (id))";
$dbc->getDb()->exec($sql);
print "Sectors table created \n\n";
print "*********** CREATE USER_SECTORS TABLE ****************\n";
$sql = "CREATE TABLE user_sectors ( \n user_id INT NOT NULL,\n sector_id INT NOT NULL,\n FOREIGN KEY (user_id) REFERENCES users(id),\n FOREIGN KEY (sector_id) REFERENCES sectors(id))";
$dbc->getDb()->exec($sql);
print "User_sectors table created \n\n";
print "*********** INSERT DATA TO SECTOR TABLE ****************\n\n";
$doc = new DOMDocument();
libxml_use_internal_errors(true);
$doc->loadHTMLFile('scripts/index.html');
$parent_id = null;
$prev_lvl = 0;
$parents = array();
foreach ($doc->getElementsByTagName('option') as $opt) {
$id = intval($opt->getAttribute('value'));
$value = mb_convert_encoding($opt->nodeValue, 'HTML-ENTITIES', 'UTF-8');
$level = intval(substr_count($value, ' ') / 4);
$name = trim(str_replace(' ', '', $value));
if ($level == 0) {
$parent_id = $id;
