start database (user and post), and initial barebones home page

1 files changed, 173 insertions, 0 deletions

diff --git a/web/core/database.php b/web/core/database.php
new file mode 100644
index 0000000..b3a597b
--- /dev/null
+++ b/web/core/database.php
@@ -0,0 +1,173 @@
+<?php /* Copyright (c) 2024 Freya Murphy */
+
+class DatabaseQuery {
+
+	private $conn;
+	private $query;
+
+	private $where;
+	private $set;
+
+	private $param;
+
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->query = '';
+
+		$this->set = FALSE;
+		$this->where = FALSE;
+		$this->param = array();
+	}
+
+	private function in($array) {
+		$in = 'IN (';
+		foreach ($array as $idx => $item) {
+			if ($idx != 0) {
+				$in .= ",";
+			}
+			$in .= "?";
+			array_push($this->param, $item);
+		}
+		$in .= ")";
+		return $in;
+	}
+
+	public function select($select) {
+		$this->query .= "SELECT $select\n";
+		return $this;
+	}
+
+	public function from($from) {
+		$this->query .= "FROM $from\n";
+		return $this;
+	}
+
+	public function where($cond) {
+		if (!$this->where) {
+			$this->where = TRUE;
+			$this->query .= "WHERE ";
+		}
+		$this->query .= "$cond ";
+		return $this;
+	}
+
+	public function like($item) {
+		$this->query .= "LIKE ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function eq($item) {
+		$this->query .= "= ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function ne($item) {
+		$this->query .= "<> ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function where_in($column, $array) {
+		if (!$this->where) {
+			$this->where = TRUE;
+			$this->query .= "WHERE ";
+		}
+		if (empty($array)) {
+			$this->query .= "FALSE\n";
+			return $this;
+		}
+		$in = $this->in($array);
+		$this->query .= "$column $in\n";
+		return $this;
+	}
+
+	public function and() {
+		$this->query .= "AND ";
+		return $this;
+	}
+
+	public function or() {
+		$this->query .= "OR ";
+		return $this;
+	}
+
+	public function join($table, $on, $type = 'LEFT') {
+		$this->query .= "$type JOIN $table ON $on\n";
+		return $this;
+	}
+
+	public function limit($limit) {
+		$this->query .= "LIMIT ?\n";
+		array_push($this->param, $limit);
+		return $this;
+	}
+
+	public function offset($offset) {
+		$this->query .= "OFFSET ?\n";
+		array_push($this->param, $offset);
+		return $this;
+	}
+
+	public function rows() {
+		$stmt = $this->conn->prepare($this->query);
+		try {
+			$stmt->execute($this->param);
+		} catch (Exception $ex) {
+			echo $ex;
+			echo '<br> >> caused by <<<br>';
+			echo str_replace("\n", "<br>", $this->query);
+		}
+		return $stmt->fetchAll(PDO::FETCH_ASSOC);
+	}
+
+	public function row() {
+		$stmt = $this->conn->prepare($this->query);
+		$stmt->execute($this->param);
+		return $stmt->fetch(PDO::FETCH_ASSOC);
+	}
+}
+
+/**
+ * DatabaseHelper
+ * allows queries on the
+ * xssbook postgres database
+ */
+class DatabaseHelper {
+
+	private $conn;
+
+	function __construct() {
+		$this->conn = NULL;
+	}
+
+	private function connect() {
+		if ($this->conn === NULL) {
+			$user = getenv("POSTGRES_USER");
+			$pass = getenv("POSTGRES_PASSWORD");
+			$db	  = getenv("POSTGRES_DB");
+			$host = 'db';
+			$port = '5432';
+
+			$conn_str = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
+                $host,
+                $port,
+                $db,
+                $user,
+				$pass
+			);
+			$this->conn = new \PDO($conn_str);
+			$this->conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
+		}
+		return $this->conn;
+	}
+
+	public function select($select) {
+		$conn = $this->connect();
+		$query = new DatabaseQuery($conn);
+		return $query->select($select);
+	}
+
+}
+?>