PHPSQL 5 PHPMy Admin YAML PHP My SQL

PHP+SQL 5. PHPMy. Admin, YAML PHP + My. SQL, PDO, ORM Practice: Vote system V 1. 0 OE NIK PHP/Symfony 1

YAML • „YAML is a human friendly data serialization standard for all programming languages” – Main feature: indentation – YAML 1. 2 is a superset of JSON – Less popular than JSON/XML, but it is used in some projects – ROS: message and service descriptors – Symfony: configuration • Symfony 4: most environment-dependent settings (including SMTP, DB config) are moved to the. env file DATABASE_URL=mysql: //db_user: db_password@127. 0. 0. 1: 3306/ db_name? server. Version=mariadb-10. 3. 13 V 1. 0 OE NIK PHP/Symfony 2

PHPMy. Admin V 1. 0 OE NIK PHP/Symfony 3

PHP and My. SQL • libmysqld vs mysqlnd: physical connection • ext-mysql / ext-mysqli: Direct connection to a My. SQL server, possibility to send SQL commands / receive rows • mysql_* old, only procedural, but: a little faster, more conventional, but deprecated – not suggested mysqli_* new, possibility to work with OO, supports prepared statements and the execution of multiple queries and transactions When using them the procedural style, the mysql_* and the mysqli_* commands have different parameter order! V 1. 0 OE NIK PHP/Symfony 4

Accessing databases in PHP • The physical access of the database is done by database -server-dependent modules (My. SQL/Maria. DB, MSSQL, Oracle, Sybase, Postgre. SQL, Firebird) • The physical communication of the SQL commands and the TCP communication are done using functions with different names and parameter syntax alternative: DBX (if you insist on a non-OOP solution…) • PDO: PHP Data Objects (similar to Java DAO / C# Db. Connection) – simple OO approach – Dialect-independent methods with dialect-dependent SQL strings – not real ORM (~Doctrine), but good for an entry-level webapp V 1. 0 OE NIK PHP/Symfony 5

My. SQLi commands • $conn=mysqli_connect(servername, username, passwor d, dbname); • $res=mysqli_query($conn, $query); • • $num=mysqli_insert_id($conn); // INSERT $num=mysqli_affected_rows($conn); // UPD, DEL, INS $num=mysqli_num_rows($res); $row=mysqli_fetch_assoc($res); // mysqli_fetch_object • SQL knowledge is required (DQL is based on SQL – ORM is better, but the ORM is usually based on PDO, PDO uses mysqli – must go behind the scenes if you have to optimize!) V 1. 0 OE NIK PHP/Symfony 6

Important things ! $str=mysqli_real_escape_string($conn, $input); (prepared statement would be better. Please note that addslashes() is not safe, magic_quotes_gpc is ALWAYS OFF!) • $str=mysqli_error($conn); • mysql_set_charset($conn, "utf 8"); (no mysqli_query($conn, "set names 'utf 8' "); ) V 1. 0 OE NIK PHP/Symfony 7

PDO V 1. 0 OE NIK PHP/Symfony 8

PDO • OOP Approach • public function __construct($host, $db, $user, $pass) { $conn. Str = "mysql: host={$host}; dbname={$db}"; $this->handle = new PDO($conn. Str, $user, $pass); $this->handle->set. Attribute(PDO: : ATTR_ERRMODE, PDO: : ERRMODE_EXCEPTION); } • VERY simple methods – Prepare – Execute V 1. 0 OE NIK PHP/Symfony 9

PDO • Prepared statements • private function exec. Query($query, $parameters = array()) { $result = $this->handle->prepare($query, array(PDO: : ATTR_CURSOR => PDO: : CURSOR_FWDONLY)); $result->execute($parameters); return $result; } V 1. 0 OE NIK PHP/Symfony 10

ORM • • V 1. 0 C#: Entity Framework, Java: Hibernate/JPA Python: Django ORM, SQLAlchemy Ruby on Rails PHP: Eloquent, Propel, Doctrine OE NIK PHP/Symfony 11

Active Record / Data Mapper V 1. 0 OE NIK PHP/Symfony 12

Active Record / Data Mapper • Active Record: – – – Simple Easy to learn Better suited for CRUD Coupled DB Performance bottlenecks • Data Mapper – – V 1. 0 Flexibility (class / table isn’t necessarily 1: 1) Better suited for DDD / SOLID Can be faster in SOME cases (good configuration!) Hard to configure/set-up OE NIK PHP/Symfony 13

Doctrine ORM layers • Data mapper with lazy loading capabilities • PDO: SQL data access (commands, results, parameters) • DBAL: Dialect-independent data access Could be used independently in a project • ORM: – The impression of working with an in-memory data structure represented as an object graph – Query-less application – DQL, if needed – DB/Class mapping via YAML (or annotations) V 1. 0 OE NIK PHP/Symfony 14

Installation / usage • Doctrine: installed in symfony by default • Doctrine Data Fixtures : to load demo data, usually for tests. Must be installed beforehand: composer require --dev doctrine/doctrine-fixtures-bundle • Check config/bundles. php: DoctrineBundleFixtures. Bundle Doctrine. Fixtures. Bundle : : class => ['dev' => true, 'test' => true], V 1. 0 OE NIK PHP/Symfony 15

Installation / usage • Edit/View DATABASE_URL in. env file • Use „php bin/console cache: clear” – In prod: always, after every change – In dev: often • • doctrine: database: create doctrine: schema: drop --forc e --full-database doctrine: schema: update --dump-sql doctrine: schema: update --force • To Read: Doctrine Migrations! V 1. 0 OE NIK PHP/Symfony 16

Installation / usage • composer installs and enables everything, creates src/Data. Fixtures/App. Fixtures. php • doctrine: fixtures: load -- no-interaction -vvv – Fixtures are services that are tagged with doctrine. fixture. orm – Originally, executes a DELETE FROM – this changes the ID values for every execution!!! – --purge-with-truncate is BAD, fails since 2011 (https: //github. com/doctrine/data-fixtures/issues/17 ) • Again: Officially, fixtures are there for injecting data !!!FOR TESTING !!! V 1. 0 OE NIK PHP/Symfony 17

To do • Development – Install doctrine-fixtures-bundle – Edit. env/DATABASE_URL – Create entity model classes (Question, Choice) (qu_id, qu_text) + (cho_id, cho_qu, cho_text, cho_num) – Use App. Fixtures. php to fill database with data – Execute fixtures, check data with phpmyadmin • Create Votes. Controller V 1. 0 – Templates: Questions, Choices (+votes. html. twig) – Actions: LISTQ, LISTC, VOTE – Homework: Add question and choice (add form with Text. Dto into the list action + add form to view) – Homework: Del question and choice (add link into view + add OEtwo extra actions into the controller)18 NIK PHP/Symfony

V 1. 0 OE NIK PHP/Symfony 19

OE NIK PHP/Symfony 20