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;