add columns() function during selection queries

tadhgboyle · Nov 4, 2020 · bd89fe9 · bd89fe9
1 parent 310dba8
commit bd89fe9
Show file tree

Hide file tree

Showing 5 changed files with 56 additions and 41 deletions.
diff --git a/README.md b/README.md
@@ -1,9  1,9 @@
 ![alt](https://i.imgur.com/4FN4HlE.png)
 
 ## Roadmap
-  - Add function to select specific columns after the initial `get()` function, for use in `when()` or similar
   - Depending how advanced KossUpdateQuery gets, make KossInsertQuery to help seperate
   - Allow `where()` to take (nested) arrays
   - "Protect" double selections in `columns()`
 
 ## Documentation
 
@@ -52,6  52,9 @@ Functions which are available in both Selection and Update/Insert queries.
   - `limit(int $limit)`
     - Only return `$limit` rows.
     - Example SQL code: `LIMIT 3`
   - `columns(array $columns)`
     - Also select `$columns` as well as whatever was passed in the original `getSome()`
     - Example SQL code: `SELECT username, first_name`
 
 ### Update/Insert Functions:
   - `update(string $table, array $values)`
@@ -107,6  110,10 @@ Functions which are not in Selection or Update/Insert queries
     // Get all columns in the "users" table, and when they're logged in, limit to only the first 5 rows.
     $results = $koss->getAll('users')->when(fn() => isset($_SESSION['logged_in']), fn() => $koss->limit(5))->execute();
     // MySQL Output: SELECT * FROM `users` LIMIT 5
 
     // Get the "username" column in the "users" table, but also select the "last_name" column.
     $results = $koss->getSome('users', ['username'])->columns(['last_name'])->execute();
     // MySQL Output: SELECT `username`, `last_name` FROM `users`
     ```
 
   - Inserting information

diff --git a/src/IKossQuery.php b/src/IKossQuery.php
@@ -22,14  22,14 @@ public function __construct(PDO $pdo, string $query);
     public function when($expression, callable $callback, callable $fallback = null): IKossQuery;
 
     /**
-     * Assemble queries into MySQL statement
      * Execute repsective query and store result
      */
-    public function build(): string;
     public function execute();
 
     /**
-     * Execute repsective query and store result
      * Assemble queries into MySQL statement
      */
-    public function execute();
     public function build(): string;
 
     /**
      * Reset query strings

diff --git a/src/Koss.php b/src/Koss.php
@@ -40,7  40,7 @@ public function __construct(string $host, string $port, string $database, string
      */
     public function getAll(string $table): KossSelectQuery
     {
-        return $this->getSome($table, ['*']);
         return $this->getSome($table, array('*'));
     }
 
     /**
@@ -103,7  103,7 @@ public function execute(string $query): array
      * @param mixed $callback - Ran if $expression is true
      * @param mixed $fallback (optional) - Ran if $expression is false
      */
-    public static function when($expression, $callback, $fallback = null): void
     public static function when($expression, callable $callback, callable $fallback = null): void
     {
         if ((is_callable($expression) && $expression()) || $expression) {
             $callback();
@@ -112,6  112,24 @@ public static function when($expression, $callback, $fallback = null): void
         }
     }
 
     /**
      * Assemble all where clauses into one string using appropriate MySQL syntax
      */
     public static function assembleWhereClause(array $where): string
     {
         $first = true;
         $return = '';
         foreach ($where as $clause) {
             if ($first) {
                 $return .= 'WHERE ';
                 $first = false;
             } else $return .= 'AND ';
 
             $return .= '`' . $clause['column'] . '` ' . $clause['operator'] . ' \'' . $clause['matches'] . '\' ';
         }
         return $return;
     }
 
     /**
      * Janky workaround for when()
      */
@@ -151,22  169,4 @@ public function like(string $column, string $like): void
     {
         $this->_query_instance->like($column, $like);
     }
-
-    /**
-     * Assemble all where clauses into one string using appropriate MySQL syntax
-     */
-    public static function assembleWhereClause(array $where): string
-    {
-        $first = true;
-        $return = '';
-        foreach ($where as $clause) {
-            if ($first) {
-                $return .= 'WHERE ';
-                $first = false;
-            } else $return .= 'AND ';
-
-            $return .= '`' . $clause['column'] . '` ' . $clause['operator'] . ' \'' . $clause['matches'] . '\' ';
-        }
-        return $return;
-    }
 }
diff --git a/src/KossSelectQuery.php b/src/KossSelectQuery.php
@@ -25,16  25,24 @@ class KossSelectQuery implements IKossQuery
 
     protected array $_where = array();
 
-    public function __construct(PDO $pdo, string $query_select)
     public function __construct(PDO $pdo, string $query_select, string $query_from = null)
     {
         $this->_pdo = $pdo;
         $this->_query_select = $query_select;
         if ($query_from != null) $this->_query_from = $query_from;
     }
 
     public static function get(PDO $pdo, string $table, array $columns): KossSelectQuery
     {
         $columns = implode(', ', ($columns[0] != '*') ? array_map(fn ($string) => "`$string`", $columns) : $columns);
-        return new self($pdo, "SELECT $columns FROM `$table`");
         return new self($pdo, "SELECT $columns", "FROM `$table`");
     }
 
     public function columns(array $columns): KossSelectQuery
     {
         if (substr($this->_query_select, -1) != ',') $this->_query_select .= ', ';
         $this->_query_select .= implode(', ', array_map(fn ($string) => "`$string`", $columns));
         return $this;
     }
 
     public function where(string $column, string $operator, string $matches = null): KossSelectQuery
@@ -82,12  90,6 @@ public function when($expression, callable $callback, callable $fallback = null)
         return $this;
     }
 
-    public function build(): string
-    {
-        $this->_query_built = $this->_query_select . ' ' . $this->_query_from . ' ' . Koss::assembleWhereClause($this->_where) . ' ' . $this->_query_group_by . ' ' . $this->_query_order_by . ' ' . $this->_query_limit;
-        return $this->_query_built;
-    }
-
     public function execute(): array
     {
         if ($this->_query = $this->_pdo->prepare($this->build())) {
@@ -104,6  106,12 @@ public function execute(): array
         return null;
     }
 
     public function build(): string
     {
         $this->_query_built = $this->_query_select . ' ' . $this->_query_from . ' ' . Koss::assembleWhereClause($this->_where) . ' ' . $this->_query_group_by . ' ' . $this->_query_order_by . ' ' . $this->_query_limit;
         return $this->_query_built;
     }
 
     public function reset(): void
     {
         $this->_where = array();

diff --git a/src/KossUpdateQuery.php b/src/KossUpdateQuery.php
@@ -42,7  42,7 @@ public static function update(PDO $pdo, string $table, array $values): KossUpdat
         foreach ($values as $column => $value) {
             $values_compiled .= "`$column` = '$value', ";
         }
-        $values_compiled = substr($values_compiled, 0, -2);
         $values_compiled = rtrim($values_compiled, ',');
         return new self($pdo, "UPDATE `$table` SET $values_compiled");
     }
 
@@ -78,12  78,6 @@ public function when($expression, callable $callback, callable $fallback = null)
         return $this;
     }
 
-    public function build(): string
-    {
-        $this->_query_built = $this->_query_insert . ' ' . $this->_query_duplicate_key . ' ' . Koss::assembleWhereClause($this->_where);
-        return $this->_query_built;
-    }
-
     public function execute()
     {
         if ($this->_query = $this->_pdo->prepare($this->build())) {
@@ -97,9  91,15 @@ public function execute()
                 }
             } else die(print_r($this->_pdo->errorInfo()));
         }
-        return null;
         return -1;
     }
 
     public function build(): string
     {
         $this->_query_built = $this->_query_insert . ' ' . $this->_query_duplicate_key . ' ' . Koss::assembleWhereClause($this->_where);
         return $this->_query_built;
     }
 
     public function reset(): void
     {
         $this->_where = array();