This are some of the methods when working with PDO.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
<?php $host = 'localhost'; $user = 'root'; $pass = ''; $dbname = 'connecttutorial'; $charset = 'utf8'; $dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}"; $opt = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; try{ $pdo = new PDO($dsn, $user, $pass, $opt); } catch(PDOException $e) { die('Could not connect to the database using credentials given. Error code: '.$e->getCode()); } // CREATE ------------------------------------------------------------------------------------------- try { $sql = "INSERT INTO books (collection, title, author) VALUES (?,?,?)"; $pdo->prepare($sql)->execute(['World of Warcraft','War crimes', 'Christie Golden']); $sql = "INSERT INTO books (collection, title, author) VALUES (:collection,:title,:author)"; $pdo->prepare($sql)->execute(['collection' => 'Warcraft','title' => 'The Shattering', 'author' => 'Christie Golden']); // get id for the last insterted row $id = $pdo->lastInsertId(); } catch(PDOException $e) { die('There was a problem when inserting. Returned message was: '.$e->getMessage()); var_dump($e); } // READ -------------------------------------------------------------------------------------------- try { $stmt = $pdo->query("SELECT * FROM books ORDER BY author ASC, title ASC"); foreach($stmt as $row) { echo $row['collection'].': '.$row['title'].' - '.$row['author'].'<br />'; } } catch (PDOException $e) { die('There was a problem when trying to read from table. Returned message was: '.$e->getMessage()); } // READ ONE ROW ------------------------------------------------------------------------------------ try { $stmt = $pdo->query("SELECT * FROM books LIMIT 1"); $row = $stmt->fetch(); /** * we can also fetch() another way: * fetch(PDO::FETCH_NUM) - every field has a numeric key * fetch(PDO::FETCH_ASSOC) - every field has the column name as key (default, as set in $opt) * fetch(PDO::FETCH_BOTH) - both of the above * fetch(PDO::FETCH_OBJ) - returns object * fetch(PDO::FETCH_LAZY) - all three * fetch(PDO::FETCH_CLASS, 'Book') - the returned row is of a certain class (also works on multiple rows) */ var_dump($row); echo '<br />'; } catch (PDOException $e) { die('There was a problem when trying to read one row from table. Returned message was: '.$e->getMessage()); } // READ ONLY ONE COLUMN FROM A RETURNED ROW -------------------------------------------------------- try { $stmt = $pdo->prepare("SELECT title FROM books WHERE author LIKE ? LIMIT 1"); $stmt->execute(['Christie Golden']); $bookTitle = $stmt->fetchColumn(); echo 'Title: '.$bookTitle.'<br />'; } catch (PDOException $e) { die('There was a problem when trying to read one row column from table. Returned message was: '.$e->getMessage()); } // READ COUNT OF RESULT----------------------------------------------------------------------------- try { $count = $pdo->query("SELECT count(*) FROM books")->fetchColumn(); echo 'Total rows: '.$count.'<br />'; } catch (PDOException $e) { die('There was a problem when trying to read one row column from table. Returned message was: '.$e->getMessage()); } // UPDATE ------------------------------------------------------------------------------------------ try { $sql = "UPDATE books SET collection = ? WHERE collection = ?"; $pdo->prepare($sql)->execute(['World of Warcraft', 'Warcraft']); } catch (PDOException $e) { die('There was a problem when trying to update. Returned message was: '.$e->getMessage()); } // UPDATE WITH ROWS COUNT -------------------------------------------------------------------------- try { $stmt = $pdo->prepare("UPDATE books SET collection = ? WHERE collection = ?"); $stmt->execute(['Warcraft', 'World of Warcraft']); $affectedRows = $stmt->rowCount(); echo 'Affected rows: '.$affectedRows.'<br />'; } catch (PDOException $e) { die('There was a problem when trying to update. Returned message was: '.$e->getMessage()); } // DELETE ------------------------------------------------------------------------------------------ try { $sql = "DELETE FROM books WHERE title LIKE ?"; $pdo->prepare($sql)->execute(['%Crimes%']); } catch (PDOException $e) { die('There was a problem when trying to delete. Returned message was: '.$e->getMessage()); } // DELETE WITH ROW COUNT -------------------------------------------------------------------------- try { $count = $pdo->prepare("DELETE FROM books"); $count->execute(); $deletedRows = $count->rowCount(); echo ('Deleted rows: '.$deletedRows); } catch (PDOException $e) { die('There was a problem when trying to delete. Returned message was: '.$e->getMessage()); } |
