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(); } } function setTable($data, ...$headers){ echo "

Data output:

"; echo ""; foreach($headers as $header) { echo ""; } echo ""; foreach($data as $row) { echo ""; foreach($headers as $h) { echo ""; } echo ""; } echo "

" . $header . "
" . $row[$h] . "

"; echo "

Number of records: ".count($data)."

"; } ///////// function runQuery($db, $sql, $title) { $result = $db->run($sql); echo "

".$title."

"; echo "

SQL string:

"; echo '

' . $sql . "

"; echo "

"; } ///////// function runQueryTable($db, $sql, $title, ...$headers) { $result = $db->runFetchAll($sql); echo "

".$title."

"; echo "

SQL string:

"; echo '

' . $sql . "

"; // var_dump($result); setTable($result, ...$headers); echo "

"; } $db = new DBSqlite(); echo "

SQL Query

"; // SQL statement for creating new tables $title = 'Create table'; $statement = 'CREATE TABLE Countries( id INT PRIMARY KEY, country VARCHAR(50) NOT NULL );'; $title = 'Create table'; runQuery($db, $statement, $title); // SQL statement for creating new tables $title = 'Create table'; $statement = 'CREATE TABLE Animals( ID INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL, type VARCHAR(50) NOT NULL, country_id INT references Countriesd(id) );'; $title = 'Create table'; runQuery($db, $statement, $title); // $title = 'Insert animals'; $statement = "INSERT INTO Animals (name, type, country_id) VALUES ('Candy', 'elephant', 3), ('pop', 'horse', 1), ('vova', 'bear', 2), ('Merlin', 'lion', 1), ('Bert', 'tiger', 3);"; runQuery($db, $statement, $title); // $title = 'Insert countries'; $statement = "INSERT INTO Countries (id, country) VALUES (1, 'USA'), (2, 'Russia'), (3, 'India');"; runQuery($db, $statement, $title); // $title = "Show all Animals table"; $statement ="SELECT * FROM Animals;"; runQueryTable($db, $statement, $title, 'ID', 'name', 'type', 'country_id'); // $title = "Show all Countries table"; $statement ="SELECT * FROM Countries;"; runQueryTable($db, $statement, $title, 'id', 'country'); // $title = "Show all Animals table"; $statement ="SELECT Animals.name, Animals.type, Countries.country FROM Countries, Animals WHERE Countries.id=Animals.country_id ORDER BY Countries.country, Animals.type;"; runQueryTable($db, $statement, $title, 'name', 'type', 'country'); // $title1 = 'Insert another animal'; $statement1 = "INSERT INTO Animals (name, type, country_id) VALUES ('Slim', 'Giraffe', 1);"; runQuery($db, $statement1, $title1); $title = "Show new Animals table"; runQueryTable($db, $statement, $title, 'name', 'type', 'country');

Zoo animals db