pdo = new PDO('sqlite::memory'); // echo "Db connection okay"; } catch(PDOException $e) { throw new PDOException($e->getMessage(), (int)$e->getCode()); } } public function run($sql) { $this->pdo->query($sql); } public function runFetchAll($sql) { return $this->pdo->query($sql)->fetchall(); } } $db = new DBSqlite(); echo "

SQL Query

"; // SQL statement for creating new tables $statement = ' CREATE TABLE APARTMENTS( id INT NOT NULL, city VARCHAR(50) NOT NULL, address VARCHAR(100) NOT NULL, price DECIMAL(6,2) NOT NULL, status VARCHAR(50) NOT NULL, PRIMARY KEY(id) );'; // execute SQL statements $db->run($statement); echo "

Create table

"; echo "

" . $statement . "

"; echo "

"; // SQL statement for inserting data $statement = " INSERT INTO APARTMENTS(id, city, address, price, status) VALUES (1, 'las vegas', '732 hall street',1000, 'not rented'), (2, 'marlboro', '985 hantz lane', 800, 'not rented'), (3, 'moretown', '3757 wines lane', 700, 'not rented'), (4, 'owatonna', '314 pritchard court', 500, 'rented'), (5, 'grayslake', '3234 cunningham court', 600, 'rented'), (6, 'great neck', '1927 romines mill road', 900, 'not rented');"; // execute SQL statements $db->run($statement); echo "

Insert data

"; echo "

" . $statement. "

"; echo "

"; $statement = ' SELECT * FROM APARTMENTS;'; $result = $db->runFetchAll($statement); echo "

Show all the apartments

"; echo "

" . $statement . "

"; // var_dump($result); echo "" ; foreach($result as $row) { echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } echo ""; echo "

Id	City	Address	Price	Status
" . $row['id'] . "	" . $row['city'] . "	" . $row['address'] . "	" . $row['price'] . "	" . $row['status'] . "

"; echo "

"; $statement = " SELECT * FROM APARTMENTS WHERE price > (SELECT AVG(price) FROM APARTMENTS) AND status='not rented' ORDER BY price;"; $result = $db->runFetchAll($statement); echo "

Show the apartments whose prices are greater than the average and are also not rented, sorted by the 'Price'

"; echo "

" . $statement . "

"; // var_dump($result); echo "" ; foreach($result as $row) { echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } echo ""; echo "

Id	City	Address	Price	Status
" . $row['id'] . "	" . $row['city'] . "	" . $row['address'] . "	" . $row['price'] . "	" . $row['status'] . "

"; echo "

SQL query (oop)