Changed from mysqli to PDO, some refactorings, faster sql queries

1 files changed, 39 insertions, 30 deletions

diff --git a/includes/pages/admin_groups.php b/includes/pages/admin_groups.php
index 8e578cb2..bc33a2b0 100644
--- a/includes/pages/admin_groups.php
+++ b/includes/pages/admin_groups.php
@@ -1,5 +1,7 @@
 <?php
 
+use Engelsystem\Database\DB;
+
 /**
  * @return string
  */
@@ -14,20 +16,20 @@ function admin_groups_title()
 function admin_groups()
 {
     $html = '';
-    $groups = sql_select('SELECT * FROM `Groups` ORDER BY `Name`');
+    $groups = DB::select('SELECT * FROM `Groups` ORDER BY `Name`');
     if (!isset($_REQUEST['action'])) {
         $groups_table = [];
         foreach ($groups as $group) {
-            $privileges = sql_select("
-                SELECT *
+            $privileges = DB::select('
+                SELECT `name`
                 FROM `GroupPrivileges`
                 JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`)
-                WHERE `group_id`='" . sql_escape($group['UID']) . "'
-            ");
+                WHERE `group_id`=?
+            ', [$group['UID']]);
             $privileges_html = [];
 
-            foreach ($privileges as $priv) {
-                $privileges_html[] = $priv['name'];
+            foreach ($privileges as $privilege) {
+                $privileges_html[] = $privilege['name'];
             }
 
             $groups_table[] = [
@@ -57,33 +59,33 @@ function admin_groups()
                     return error('Incomplete call, missing Groups ID.', true);
                 }
 
-                $group = sql_select("SELECT * FROM `Groups` WHERE `UID`='" . sql_escape($group_id) . "' LIMIT 1");
-                if (count($group) > 0) {
-                    $privileges = sql_select("
+                $group = DB::select('SELECT * FROM `Groups` WHERE `UID`=? LIMIT 1', [$group_id]);
+                if (!empty($group)) {
+                    $privileges = DB::select('
                         SELECT `Privileges`.*, `GroupPrivileges`.`group_id`
                         FROM `Privileges`
                         LEFT OUTER JOIN `GroupPrivileges`
                             ON (
                                 `Privileges`.`id` = `GroupPrivileges`.`privilege_id`
-                                AND `GroupPrivileges`.`group_id`='" . sql_escape($group_id) . "'
+                                AND `GroupPrivileges`.`group_id`=?
                             )
                         ORDER BY `Privileges`.`name`
-                    ");
+                    ', [$group_id]);
                     $privileges_html = '';
                     $privileges_form = [];
-                    foreach ($privileges as $priv) {
+                    foreach ($privileges as $privilege) {
                         $privileges_form[] = form_checkbox(
                             'privileges[]',
-                            $priv['desc'] . ' (' . $priv['name'] . ')',
-                            $priv['group_id'] != '',
-                            $priv['id']
+                            $privilege['desc'] . ' (' . $privilege['name'] . ')',
+                            $privilege['group_id'] != '',
+                            $privilege['id']
                         );
                         $privileges_html .= sprintf(
                             '<tr><td><input type="checkbox" name="privileges[]" value="%s" %s /></td> <td>%s</td> <td>%s</td></tr>',
-                            $priv['id'],
-                            ($priv['group_id'] != '' ? 'checked="checked"' : ''),
-                            $priv['name'],
-                            $priv['desc']
+                            $privilege['id'],
+                            ($privilege['group_id'] != '' ? 'checked="checked"' : ''),
+                            $privilege['name'],
+                            $privilege['desc']
                         );
                     }
 
@@ -103,20 +105,27 @@ function admin_groups()
                     return error('Incomplete call, missing Groups ID.', true);
                 }
 
-                $group = sql_select("SELECT * FROM `Groups` WHERE `UID`='" . sql_escape($group_id) . "' LIMIT 1");
+                $group = DB::select('SELECT * FROM `Groups` WHERE `UID`=? LIMIT 1', [$group_id]);
                 if (!is_array($_REQUEST['privileges'])) {
                     $_REQUEST['privileges'] = [];
                 }
-                if (count($group) > 0) {
-                    list($group) = $group;
-                    sql_query("DELETE FROM `GroupPrivileges` WHERE `group_id`='" . sql_escape($group_id) . "'");
+                if (!empty($group)) {
+                    $group = array_shift($group);
+                    DB::delete('DELETE FROM `GroupPrivileges` WHERE `group_id`=?', [$group_id]);
                     $privilege_names = [];
-                    foreach ($_REQUEST['privileges'] as $priv) {
-                        if (preg_match("/^[0-9]{1,}$/", $priv)) {
-                            $group_privileges_source = sql_select("SELECT * FROM `Privileges` WHERE `id`='" . sql_escape($priv) . "' LIMIT 1");
-                            if (count($group_privileges_source) > 0) {
-                                sql_query("INSERT INTO `GroupPrivileges` SET `group_id`='" . sql_escape($group_id) . "', `privilege_id`='" . sql_escape($priv) . "'");
-                                $privilege_names[] = $group_privileges_source[0]['name'];
+                    foreach ($_REQUEST['privileges'] as $privilege) {
+                        if (preg_match("/^[0-9]{1,}$/", $privilege)) {
+                            $group_privileges_source = DB::select(
+                                'SELECT `name` FROM `Privileges` WHERE `id`=? LIMIT 1',
+                                [$privilege]
+                            );
+                            if (!empty($group_privileges_source)) {
+                                $group_privileges_source = array_shift($group_privileges_source);
+                                DB::insert(
+                                    'INSERT INTO `GroupPrivileges` (`group_id`, `privilege_id`) VALUES (?, ?)',
+                                    [$group_id, $privilege]
+                                );
+                                $privilege_names[] = $group_privileges_source['name'];
                             }
                         }
                     }