File: setmenu/class.buildmenu.php

<?php
// Class to get and save categories - http://coursesweb.net/
class buildMenu {
  // properties
  static protected $conn = false;            // stores the connection to mysql
  public $affected_rows = 0;        // number of affected, or returned rows in SQL query
  public $eror = false;          // to store and check for errors
  public $ctgres;             // Table /or filename that stores the categories

  // constructor
  public function __construct() {
    if(isset($_POST['ctgres'])) $this->ctgres = $_POST['ctgres'];
  }

  // for connecting to mysql
  protected function setConn() {
    try {
      // Connect and create the PDO object
      self::$conn = new PDO("mysql:host=".DBHOST."; dbname=".DBNAME, DBUSER, DBPASS);

      // Sets to handle the errors in the ERRMODE_EXCEPTION mode
      self::$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      self::$conn->exec('SET character_set_client="utf8",character_set_connection="utf8",character_set_results="utf8";');      // Sets encoding UTF-8
      
    }
    catch(PDOException $e) {
      $this->eror = 'Unable to connect to MySQL: '. $e->getMessage();
    }
  }

  // Performs SQL queries
  public function sqlExecute($sql) {
    if(self::$conn===false OR self::$conn===NULL) $this->setConn();      // sets the connection to mysql
    $re = true;           // the value to be returned

    // if there is a connection set ($conn property not false)
    if(self::$conn !== false) {
      // gets the first word in $sql, to determine whenb SELECT query
      $ar_mode = explode(' ', trim($sql), 2);
      $mode = strtolower($ar_mode[0]);

      // performs the query and get returned data
      try {
        if($sqlprep = self::$conn->prepare($sql)) {
          // execute query
          if($sqlprep->execute()) {
            // if $mode is 'select', gets the result_set to return
            if($mode == 'select' || $mode == 'show') {
              $re = array();
              // if fetch() returns at least one row (not false), adds the rows in $re for return
              if(($row = $sqlprep->fetch(PDO::FETCH_ASSOC)) !== false){
                do {
                  // check each column if it has numeric value, to convert it from "string"
                  foreach($row AS $k=>$v) {
                    if(is_numeric($v)) $row[$k] = $v + 0;
                  }
                  $re[] = $row;
                }
                while($row = $sqlprep->fetch(PDO::FETCH_ASSOC));
              }
              $this->affected_rows = count($re);                   // number of returned rows
            }
          }
          else $this->eror = 'Cannot execute the sql query';
        }
        else {
          $eror = self::$conn->errorInfo();
          $this->eror = 'Error: '. $eror[2];
        }
      }
      catch(PDOException $e) {
        $this->eror = $e->getMessage();
      }
    }

    // sets to return false in case of error
    if($this->eror !== false) { echo $this->eror; $re = false; }
    return $re;
  }

  // returns true if the table exists, else, false
  public function checkTable() {
    $resql = $this->sqlExecute("SHOW TABLES IN ". DBNAME ." WHERE `Tables_in_". DBNAME ."`='$this->ctgres'");
    if($this->affected_rows > 0) return true;
    else return false;
  }

  // gets categories data saved in MySQL table, returns as two associatve arrays in JSON (for $ctgItems, and $ctgData in JS)
  public function getCtg() {
    $row = $this->sqlExecute("SELECT * FROM `$this->ctgres`");

    // if rowa from SELECT, create two arrays with data for $ctgItems and $ctgData in JS
    if($row && $this->affected_rows > 0) {
      $ctgItems = array();  $ctgData = array();
      for($i=0; $i<$this->affected_rows; $i++) {
        $ctgData[$row[$i]['id']] = array('namec'=>stripslashes($row[$i]['namec']), 'lurl'=>stripslashes($row[$i]['lurl']), 'ctg'=>$row[$i]['ctg']);

        // Set $ctgItems according to last number in 'ctg', which is ID of the parent category
        $ctg = explode(':', trim($row[$i]['ctg'], ':'));
        $parent = end($ctg);
        if($parent == '') $parent = 0;

        // if $parent not item in $ctgItems, and not Root (id 0), create it, else, append the 'id'
        if(!isset($ctgItems[$parent]) && $row[$i]['id'] > 0) $ctgItems[$parent] = array($row[$i]['id']);
        else if($row[$i]['id'] > 0) $ctgItems[$parent][] = $row[$i]['id'];
      }

      return json_encode(array($ctgItems, $ctgData));    // returns the JSON string used in JS
    }
    else return 'The table: '. $this->ctgres .' not found!<';
  }

  // saves categories data in MySQL database, returns true or false
  public function saveCtg($sdata) {
    // if table exists, empty it, else, creates it
    if($this->checkTable() === true) $this->sqlExecute("DELETE FROM `$this->ctgres`");
    else {
      $this->sqlExecute("CREATE TABLE `$this->ctgres` (`id` INT UNSIGNED PRIMARY KEY, `namec` CHAR(255) NOT NULL DEFAULT '', `lurl` VARCHAR(500) NOT NULL DEFAULT '#', `ctg` CHAR(255) NOT NULL DEFAULT ':') CHARACTER SET utf8 COLLATE utf8_general_ci");
    }

    $jsn = json_decode($sdata, true);

    // traverse the seccond array from $jsn, "ctgData", and define $values to be inserted
    $values = '';
    foreach($jsn[1] as $id=>$category) {
      $values .= '('. $id .", '". addslashes($category['namec']) ."', '". addslashes($category['lurl']) ."', '". $category['ctg'] ."'),";
    }

    // insert $values, deleting last ','
    if($this->sqlExecute("INSERT INTO `$this->ctgres` (`id`, `namec`, `lurl`, `ctg`) VALUES ". trim($values, ','))) return true;
    else return false;
  }

  // sets and returns the html code of the menu (1st array from $ctgs is $ctgItems from JS, 2nd is $ctgData)
  protected function setHtml($ctgs) {
    // recursive function to create multilevel menu list, $parentId 0 is the root
    // function addapted from: http://crisp.tweakblogs.net/blog/317/formatting-a-multi-level-menu-using-only-one-query.html
    function multilevelMenu($parentId, $ctgItems, $ctgData) {
      $html = '';       // stores and returns the html code with Menu

      // iif array items with child IDs in ctgItems
      if(isset($ctgItems[$parentId])) {
        $html = '<ul>';      // open UL

        // traverses the array with child IDs in each item, and adds them in LI tags, with their name from $ctgData
        foreach ($ctgItems[$parentId] as $childId) {
          // open LI
          $clsli = isset($ctgItems[$childId]) ? ' class="litems"' : '';       // add class to items with childs
          $html .= '<li id="ctg'.$childId.'"><a href="'. $ctgData[$childId]['lurl'] .'" title="'. $ctgData[$childId]['namec'] .'"'. $clsli .'>'. $ctgData[$childId]['namec'] .'</a>';

          $html .= multilevelMenu($childId, $ctgItems, $ctgData);     // re-calls the function to find child-items recursively

          $html .= '</li>';      // close LI
        }
        $html .= '</ul>';       // close UL
      }

      return $html;
    }

     return multilevelMenu(0, $ctgs[0], $ctgs[1]);     // returns html menu created with multilevelMenu()
  }

  // returns HTML code with the menu saved in MySQL $table
  public function menuMySQL($table) {
    $this->ctgres = $table;    // set the property with table name used in getCtg()
    $ctgs = json_decode($this->getCtg(), true);

    return $this->setHtml($ctgs);
  }

  // returns HTML code with the menu saved in JSON format, in $file
  public function menuTxt($file) {
    $re = '';

    // if file exists, gets the code, and convert it in array
    if(file_exists('menus/'.$file)) {
      $ctgs = json_decode(file_get_contents('menus/'.$file), true);
      $re = $this->setHtml($ctgs);
    }
    else $re = 'The file: '. $file .' not found!<';

    return $re;
  }
}