Webtechnologien
Wintersemester 2024
<!DOCTYPE HTML> <html lang="de"> <head> <meta charset="utf-8"> <title>PHP – Datenbankzugriff</title> <meta name="viewport" content="width=792, user-scalable=no"> <base target="_blank"> <link rel="stylesheet" href="/assets/styles/presentation.css"> </head> <body class="list"> <div class="notes"> <a href="text.html" target="_parent">♯</a> <a href="/notes/" target="_blank">♫</a> </div> <header class="caption"> <h1>PHP – Datenbankzugriff</h1> </header> <section id="php" class="slide cover"><div><h2>PHP</h2> <p>Datenbankzugriff</p> </div></section> <section class="slide" id="möglichkeiten"><div><h2>Möglichkeiten</h2> <ul> <li>PHP bietet von Hause aus verschiedene Wege, um mit Datenbanken zu kommunizieren</li> <li>Jeweils eigene Treiber für verschiedene DBS</li> </ul> <p><img src="schaubild.svg" alt="Schaubild" class="full-width" /></p> <footer> <ul> <li>MySQL, PostgreSQL oder SQLite.</li> </ul> </footer> </div></section> <section class="slide" id="verbindung-mit-mysql-datenbanken"><div><h2>Verbindung mit MySQL-Datenbanken</h2> <ul> <li>3 mögliche APIs: <ul> <li><code class="language-plaintext highlighter-rouge">mysql</code> – alte Variante</li> <li><code class="language-plaintext highlighter-rouge">mysqli</code> – verbesserte Version der alten Variante</li> <li><a href="http://php.net/manual/de/book.pdo.php"><code class="language-plaintext highlighter-rouge">PDO</code></a> – neue Variante</li> </ul> </li> <li>wichtige Funktionen: <a href="http://us2.php.net/pdo.query"><code class="language-plaintext highlighter-rouge">query</code></a>, <a href="http://us2.php.net/pdo.prepare"><code class="language-plaintext highlighter-rouge">prepare</code></a>, <a href="http://us2.php.net/manual/de/pdostatement.bindparam.php"><code class="language-plaintext highlighter-rouge">bindParam</code></a>, <a href="http://php.net/manual/de/pdostatement.execute.php"><code class="language-plaintext highlighter-rouge">execute</code></a> und <a href="http://php.net/manual/de/pdostatement.fetch.php"><code class="language-plaintext highlighter-rouge">fetch</code></a></li> </ul> <footer> <ul> <li>Probleme <ul> <li>Zu leicht, unsicheren Code zu schreiben</li> <li>Kompliziert, Datenbank zu wechseln</li> </ul> </li> </ul> </footer> </div></section> <section class="slide" id="pdo--php-database-object"><div><h2>PDO – PHP Database Object</h2> <ul> <li>Abstraktionsschicht für den Datenzugriff</li> <li>Objektorientiert (kann nicht prozedural benutzt werden)</li> </ul> <pre class="highlight language-php" data-lang="php"><code><span class="cp"><?php</span> <span class="nv">$connection</span> <span class="o">=</span> <span class="k">new</span> <span class="nc">PDO</span><span class="p">(</span><span class="s1">'sqlite:users.db'</span><span class="p">);</span> <span class="nv">$statement</span> <span class="o">=</span> <span class="nv">$connection</span><span class="o">-></span><span class="nf">query</span><span class="p">(</span><span class="s1">'SELECT * FROM users'</span><span class="p">);</span> <span class="nv">$result</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">fetchAll</span><span class="p">();</span> <span class="nb">var_dump</span><span class="p">(</span><span class="nv">$result</span><span class="p">);</span> <span class="cp">?></span> </code></pre> <footer> <ul> <li>Aufbau der Verbindung → Zugriff auf die Datenbank → Abbau der Verbindung</li> <li>Gleiche Befehle, egal welche Datenbank gerade benutzt wird</li> <li>Keine Datenbank-Abstraktion oder ORM, SQL immer noch Handarbeit</li> <li>prüfen per <code class="language-plaintext highlighter-rouge">php -i | grep PDO</code> oder <code class="language-plaintext highlighter-rouge"><?php phpinfo() ?></code></li> </ul> </footer> </div></section> <section class="slide" id="pdo--init"><div><h2>PDO – Init</h2> <pre class="highlight language-php" data-lang="php"><code><span class="cp"><?php</span> <span class="kd">class</span> <span class="nc">Storage</span> <span class="p">{</span> <span class="k">private</span> <span class="nv">$connection</span><span class="p">;</span> <span class="k">public</span> <span class="k">function</span> <span class="n">__construct</span><span class="p">()</span> <span class="p">{</span> <span class="k">try</span> <span class="p">{</span> <span class="nv">$this</span><span class="o">-></span><span class="n">connection</span> <span class="o">=</span> <span class="k">new</span> <span class="nc">PDO</span><span class="p">(</span> <span class="s2">"mysql:host=</span><span class="nv">$host</span><span class="s2">;dbname=</span><span class="nv">$name</span><span class="s2">;charset=utf8"</span><span class="p">,</span> <span class="nv">$user</span><span class="p">,</span> <span class="nv">$pass</span><span class="p">);</span> <span class="c1">// $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);</span> <span class="nv">$this</span><span class="o">-></span><span class="n">connection</span><span class="o">-></span><span class="nf">setAttribute</span><span class="p">(</span> <span class="no">PDO</span><span class="o">::</span><span class="no">ATTR_DEFAULT_FETCH_MODE</span><span class="p">,</span> <span class="no">PDO</span><span class="o">::</span><span class="no">FETCH_ASSOC</span><span class="p">);</span> <span class="p">}</span> <span class="k">catch</span> <span class="p">(</span><span class="nc">PDOException</span> <span class="nv">$e</span><span class="p">)</span> <span class="p">{</span> <span class="k">die</span><span class="p">(</span><span class="nv">$e</span><span class="o">-></span><span class="nf">getMessage</span><span class="p">());</span> <span class="p">}</span> <span class="p">}</span> <span class="k">public</span> <span class="k">function</span> <span class="n">__destruct</span><span class="p">()</span> <span class="p">{</span> <span class="nv">$this</span><span class="o">-></span><span class="n">connection</span> <span class="o">=</span> <span class="kc">null</span><span class="p">;</span> <span class="p">}</span> <span class="cp">?></span> </code></pre> <footer> <ul> <li>Gleiche Befehle, egal welche Datenbank gerade benutzt wird</li> <li>Keine Datenbank-Abstraktion oder ORM, SQL immer noch Handarbeit</li> <li>Standard-Error-Mode ist <code class="language-plaintext highlighter-rouge">silent</code>, kann aber geändert werden (<code class="language-plaintext highlighter-rouge">warning</code>, <code class="language-plaintext highlighter-rouge">exception</code>)</li> <li><code class="language-plaintext highlighter-rouge">$db->getAvailableDrivers()</code> um verfügbare Treiber aufzulisten</li> </ul> </footer> </div></section> <section class="slide" id="pdo--query"><div><h2>PDO – Query</h2> <pre class="highlight language-php" data-lang="php"><code><span class="cp"><?php</span> <span class="nv">$users</span> <span class="o">=</span> <span class="nv">$this</span><span class="o">-></span><span class="n">connection</span><span class="o">-></span><span class="nf">query</span><span class="p">(</span><span class="s1">'SELECT * FROM users'</span><span class="p">);</span> <span class="k">foreach</span> <span class="p">(</span><span class="nv">$users</span> <span class="k">as</span> <span class="nv">$user</span><span class="p">)</span> <span class="p">{</span> <span class="nb">var_dump</span><span class="p">(</span><span class="nv">$user</span><span class="p">);</span> <span class="p">}</span> <span class="cp">?></span> </code></pre> </div></section> <section class="slide" id="pdo--prepared-statements"><div><h2>PDO – Prepared Statements</h2> <pre class="highlight language-php" data-lang="php"><code><span class="cp"><?php</span> <span class="nv">$id</span> <span class="o">=</span> <span class="nv">$_GET</span><span class="p">[</span><span class="s1">'id'</span><span class="p">];</span> <span class="nv">$result</span> <span class="o">=</span> <span class="nv">$this</span><span class="o">-></span><span class="n">connection</span><span class="o">-></span><span class="nf">query</span><span class="p">(</span> <span class="s2">"SELECT * FROM users WHERE id = </span><span class="nv">$id</span><span class="s2">"</span><span class="p">);</span> <span class="c1">// BÖSE!!!</span> <span class="nv">$statement</span> <span class="o">=</span> <span class="nv">$this</span><span class="o">-></span><span class="n">connection</span><span class="o">-></span><span class="nf">prepare</span><span class="p">(</span> <span class="s1">'UPDATE answers SET votes = votes + 1 WHERE id = :id'</span><span class="p">);</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">bindParam</span><span class="p">(</span><span class="s1">':id'</span><span class="p">,</span> <span class="nv">$id</span><span class="p">,</span> <span class="no">PDO</span><span class="o">::</span><span class="no">PARAM_INT</span><span class="p">);</span> <span class="nv">$result</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">execute</span><span class="p">();</span> <span class="cp">?></span> </code></pre> <footer> <ul> <li>gibt auch <code class="language-plaintext highlighter-rouge">PDO::quote</code>, aber Prepared Statements der Erfahrung nach besser</li> <li>Enthalten entgegen üblicher SQL-Statements keine Parameter, sondern nur Platzhalter.</li> <li>Datenbank bekommt Parameter separat und prüft diese</li> <li>Platzhalter können auch <code class="language-plaintext highlighter-rouge">?</code> sein, aber benannte besser lesbar</li> <li><code class="language-plaintext highlighter-rouge">bindParam</code>: Platzhalter, Variable, Datentyp, Länge, Optionen</li> <li><code class="language-plaintext highlighter-rouge">execute</code>: Kann Array als Parameter erhalten und iteriert dann über Platzhalter (werden alle als String behandelt)</li> </ul> </footer> </div></section> <section class="slide" id="pdo--fetch"><div><h2>PDO – Fetch</h2> <pre class="highlight language-php" data-lang="php"><code><span class="cp"><?php</span> <span class="k">while</span><span class="p">(</span><span class="nv">$row</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">fetch</span><span class="p">())</span> <span class="p">{</span> <span class="nb">print_r</span><span class="p">(</span><span class="nv">$row</span><span class="p">);</span> <span class="p">}</span> <span class="nv">$result</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">fetch</span><span class="p">(</span><span class="no">PDO</span><span class="o">::</span><span class="no">FETCH_CLASS</span><span class="p">,</span> <span class="s1">'User'</span><span class="p">);</span> <span class="nv">$result</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">fetchObject</span><span class="p">(</span><span class="s1">'User'</span><span class="p">);</span> <span class="nv">$result</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-></span><span class="nf">fetchAll</span><span class="p">();</span> <span class="cp">?></span> </code></pre> <footer> <ul> <li>Modes <ul> <li><code class="language-plaintext highlighter-rouge">PDO::FETCH_ASSOC</code>: Assoziatives Array</li> <li><code class="language-plaintext highlighter-rouge">PDO::FETCH_BOTH</code>: Mischung aus numerisch indexiertem und assoziativem Array</li> <li><code class="language-plaintext highlighter-rouge">PDO::FETCH_CLASS</code>: Erzeugt Objekt zur entsprechenden Klasse</li> <li><code class="language-plaintext highlighter-rouge">PDO::FETCH_OBJ</code>: Erzeugt ein anonymes Objekt</li> <li>und mehr</li> </ul> </li> </ul> </footer> </div></section> <div class="progress"><div></div></div> <script src="/assets/scripts/shower.js"></script> <script src="/assets/scripts/shower-notes.js"></script> <script src="/assets/scripts/editor.js"></script> <script> document.querySelectorAll('[bg]').forEach(el => el.style.backgroundImage = `url(${el.attributes.bg.value})`); </script> </body> </html>