iwa-panda2

Manage Weather Data by International Weather Agency (Version 2)
Log | Files | Refs | README

SQLDatabase.php (3882B)

      1 <?php
      2 
      3 namespace Lollipop {
      4     use mysqli;
      5 
      6     /// this is the main database engine
      7     class SQLDatabase
      8     {
      9         public mysqli $conn;
     10 
     11         public function __construct(string $host, string $username, string $password, string $database = null, int $port = null)
     12         {
     13             $this->conn = new mysqli($host, $username, $password, $database, $port);
     14         }
     15 
     16         /// get a table by passing the desired class
     17         public function get(string $table_class)
     18         {
     19             /* this function accepts a $table_name creates a Database object with the class $table_name
     20             *  retuns a Database object
     21             */
     22             $cls = new $table_class($this);
     23             return $cls;
     24         }
     25 
     26         /// this function accepts a table name and an array[$column_name => $value]
     27         /// statement is select * from $table_name where $column_name = $value AND etc...
     28         /// returns an array of classes
     29         public function all_where(string $table_name, array $vars)
     30         {
     31             if (!sizeof($vars)) {
     32                 return [];
     33             }
     34             $cls = new $table_name($this);
     35 
     36             $sql = "SELECT * FROM {$cls->get_table()} WHERE ";
     37             $params = [];
     38 
     39             $i = 0;
     40             foreach ($vars as $key => $value) {
     41                 if ($i > 0) {
     42                     $sql .= ' AND ';
     43                 }
     44                 $sql .= " $key LIKE ?";
     45                 $params[] = $value;
     46                 $i++;
     47             }
     48 
     49             $stmt = $this->conn->prepare($sql);
     50             $stmt->execute($params);
     51             $result = $stmt->get_result();
     52 
     53             if (!$result || $result->num_rows == 0) {
     54                 return [];
     55             }
     56 
     57             $objects = [];
     58             while ($row = $result->fetch_assoc()) {
     59                 $o = new $table_name($this);
     60                 $o->setData($row);
     61                 $objects[] = $o;
     62             }
     63             return $objects;
     64         }
     65 
     66         /// returns every row in database of table
     67         public function all(string $table_name)
     68         {
     69             /* loads whole table $table_name
     70             * returns array of objects
     71             */
     72             $cls = new $table_name($this);
     73 
     74             $sql = "SELECT * FROM {$cls->get_table()}";
     75 
     76             $result = $this->conn->query($sql);
     77 
     78             if (!$result || $result->num_rows == 0) {
     79                 return [];
     80             }
     81 
     82             $objects = [];
     83             while ($row = $result->fetch_assoc()) {
     84                 $o = new $table_name($this);
     85                 $o->setData($row);
     86                 $objects[] = $o;
     87             }
     88             return $objects;
     89         }
     90 
     91         public function getDateRange(string $table_name, array $query, $order)
     92         {
     93             if($query == null) {
     94                 return [];
     95             }
     96 
     97             $cls = new $table_name($this);
     98 
     99             $sql = "SELECT * FROM {$cls->get_table()} WHERE ";
    100             $index = 0;
    101             $values = [];
    102             foreach($query as $key => $q) {
    103                 foreach ($q as $k => $value) {
    104                     if ($index > 0) {
    105                         $sql .= " AND ";
    106                     }
    107                     $sql .= "{$key} {$k} ?";
    108                     $values[] = $value;
    109                     $index++;
    110                 }
    111             }
    112 
    113             $sql .= " ORDER BY date_time " . $order;
    114             $sql .= " LIMIT 1000";
    115             $stmt = $this->conn->prepare($sql);
    116             $stmt->execute($values);
    117             $result = $stmt->get_result();
    118 
    119             if ($result->num_rows == 0) {
    120                 return [];
    121             }
    122 
    123             $objects = [];
    124             while ($row = $result->fetch_assoc()) {
    125                 $o = new $table_name($this);
    126                 $o->setData($row);
    127                 $objects[] = $o;
    128             }
    129             return $objects;
    130         }
    131     }
    132 }