· 9 years ago · Jan 11, 2017, 12:28 PM
1PDO (PHP Data Objets) - extension for accessing databases in PHP
2----------------------------------------------------------------
3
41) Get a list of all available drivers
5***************************************
6 var_dump(PDO::getAvailableDrivers());
7
82) Connect to MySQL database (persistent connection - connection is cached)
9***************************************
10
11 define("DSN", "mysql:host=localhost;dbname=library");
12 define("USERNAME", "root");
13 define("PASSWORD", "pass");
14 $options = array(PDO::ATTR_PERSISTENT => true);
15
16 try{
17 $conn = new PDO(DSN, USERNAME, PASSWORD, $options);
18
19 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
20
21 echo "connection successful";
22 }catch(PDOException $ex){
23 echo 'A databse error occurred: ' . $ex->getMessage(); // development
24 // echo 'A database error occurred '; // production code
25 }
26
273) Create table with PDO
28***************************************
29
30 include_once 'connect.php';
31
32 $table = "CREATE TABLE IF NOT EXISTS books (
33 id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
34 name VARCHAR(25) NOT NULL UNIQUE,
35 description VARCHAR(255) NOT NULL,
36 created_at TIMESTAMP)";
37 try{
38 $conn->query($table);
39 echo "<br>Table created";
40 }catch(PDOException $ex){
41 echo "<br>An error occurred: " . $ex->getMessage();
42 }
43
444) Create
45***************************************
46
47 include_once 'connect.php';
48
49 $insertQuery = "INSERT INTO books(name, description, created_at)
50 VALUES('Introduction to Java', 'Learn all about Java', now())";
51
52 try{
53 $result = $conn->exec($insertQuery);
54 echo "$result record inserted <br>";
55 }catch(PDOException $ex){
56 echo "An error occurred: " . $ex->getMessage();
57 }
58
595) Read
60***************************************
61
62 include_once 'connect.php';
63
64 $selectQuery = "SELECT * FROM books";
65
66 try{
67 $stmt = $conn->query($selectQuery);
68
69 while($row = $stmt->fetch()){
70 echo "Name: " . $row['name'] . " - " . $row['description'] . '<br>';
71 }
72 }catch(PDOException $ex){
73 echo "An error occurred: " . $ex->getMessage();
74 }
75
766) Update
77***************************************
78 include_once 'connect.php';
79
80 $updateQuery = "UPDATE books SET name = 'Introduction to Java 2' WHERE id = 1";
81
82 try{
83 $result = $conn->exec($updateQuery);
84 echo "$result record updated <br>";
85 }catch(PDOException $ex){
86 echo "An error occurred: " . $ex->getMessage();
87 }
88
897) Delete
90***************************************
91
92 include_once 'connect.php';
93
94 $deleteQuery = "DELETE FROM books WHERE id = 1";
95
96 try{
97 $result = $conn->exec($deleteQuery);
98 echo "$result record deleted <br>";
99 }catch(PDOException $ex){
100 echo "An error occurred: " . $ex->getMessage();
101 }
102
1038) Prepared Statements with named parameters
104***************************************
105
106 include_once 'connect.php';
107
108 $name = "PHP PDO";
109 $description = "Build a basic task list";
110
111 try{
112 $insertQuery = "INSERT INTO books (name, description, created_at)
113 VALUES(:name, :description, now())";
114
115 // prepare the query
116 $stmt = $conn->prepare($insertQuery);
117
118 // execute the statement
119 $stmt->execute(array(":name" => $name, ":description" => $description));
120
121 echo "Record created";
122 }catch(PDOException $ex){
123 echo "An error occurred: " . $ex->getMessage();
124 }
125
1269) Prepared Statements with unnamed parameters
127***************************************
128
129 include_once 'connect.php';
130
131 $name = "PHP PDO 2";
132 $description = "Build a basic task list 2";
133
134 try{
135 $insertQuery = "INSERT INTO books (name, description, created_at)
136 VALUES(?, ?, now())";
137
138 // prepare the query
139 $stmt = $conn->prepare($insertQuery);
140
141 // execute the statement
142 $stmt->execute(array($name, $description));
143
144 echo "Record created";
145 }catch(PDOException $ex){
146 echo "An error occurred: " . $ex->getMessage();
147 }
148
14910) Binding parameters to Prepared Statements
150***************************************
151
152 include_once 'connect.php';
153
154 try{
155 // prepare the query
156 $stmt = $conn->prepare("INSERT INTO books (name, description, created_at)
157 VALUES(:name, :description, now())");
158
159 // execute the statement
160 $stmt->bindParam(":name", $name);
161 $stmt->bindParam(":description", $description);
162
163 // crate first record
164 $name = "Objects and Patterns";
165 $description = "Software crafting";
166 $stmt->execute();
167
168 // crate second record
169 $name = "Objects and Patterns 1";
170 $description = "Software crafting 1";
171 $stmt->execute();
172
173 echo "Record created";
174 }catch(PDOException $ex){
175 echo "An error occurred: " . $ex->getMessage();
176 }
177
17811) Get last inserted id
179***************************************
180
181 include_once 'connect.php';
182
183 $name = "Learn Bootstrap";
184 $description = "Front-end framework";
185
186 try{
187 // build the query
188 $insertQuery = "INSERT INTO books (name, description, created_at)
189 VALUES(:name, :description, now())";
190
191 // prepare the statement
192 $stmt = $conn->prepare($insertQuery);
193
194 // execute the statement
195 $stmt->execute(array(":name"=>$name, ":description"=>$description));
196
197 echo "Record with ID: " . $conn->lastInsertId() . " created.";
198
199 }catch(PDOException $ex){
200 echo "An error occurred: " . $ex->getMessage();
201 }
202
20312) Number of affected rows
204***************************************
205
206 include_once 'connect.php';
207
208 $updateQuery = "UPDATE books SET name = :name, description = :description WHERE id = :id";
209
210 try{
211 $stmt = $conn->prepare($updateQuery);
212
213 $stmt->execute(array(":name"=>"Python for newbies", ":description"=>"Very good", ":id"=>4));
214
215 echo $stmt->rowCount() . " record updated";
216 }catch(PDOException $ex){
217 echo "An error occurred: " . $ex->getMessage();
218 }
219
22013) Transactions
221***************************************
222 include_once 'connect.php';
223
224 try{
225 $name = "My Book";
226 $description = "My book description";
227
228 // begin transaction
229 $conn->beginTransaction();
230
231 $sql1 = "INSERT INTO books (name,description, created_at)
232 VALUES(:name, :description, now())";
233 $stmt = $conn->prepare($sql1);
234 $stmt->execute(array(":name"=>$name, ":description"=>$description));
235 if($stmt){
236 echo "record inserted";
237 }
238
239 $sql2 = "DELETE FROM books where id = :id";
240 $stmt = $conn->prepare($sql2);
241 $stmt->execute(array(":id"=>6));
242
243 $conn->commit(); // make changes permanent
244
245 echo "Operation succeeded";
246
247 }catch(PDOException $ex){
248 $conn->rollBack(); // if error, roll back transaction
249 echo "An error occurred: " . $ex->getMessage();
250 }
251
25214) PDO fetching modes
253***************************************
254
255 $stmt = $conn->query($selectQuery);
256
257 $stmt->setFetchMode(PDO::FETCH_OBJ); // set fetch mode
258
259 // pass fetch mode as constant
260 while($row = $stmt->fetch(PDO::FETCH_BOTH)){ // default
261 echo "Name: " . $row['name'] . " - " . $row['description'] . '<br>';
262 echo "Name: " . $row[1] . " - " . $row[2] . '<br>';
263 }
264------------------------------------------
265 while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
266 echo "Name: " . $row['name'] . " - " . $row['description'] . '<br>';
267 }
268------------------------------------------
269 while($row = $stmt->fetch(PDO::FETCH_NUM)){
270 echo "Name: " . $row[1] . " - " . $row[2] . '<br>';
271 }
272------------------------------------------
273 while($row = $stmt->fetch(PDO::FETCH_OBJ)){
274 echo "Name: " . $row->name . " - " . $row->description . '<br>';
275 }
276------------------------------------------
277 class Book{
278 public $name;
279 public $description;
280 }
281 include_once 'Book.php';
282
283 $stmt->setFetchMode(PDO::FETCH_CLASS, "Book");
284
285 while($row = $stmt->fetch()){
286 echo "Name: " . $row->name . " - " . $row->description . '<br>';
287 }
288------------------------------------------
289
29015) Send data from form to database
291***************************************
292 index.php
293 ---------
294 <form id="create-task" method="post">
295 <div class="form-group">
296 <label for="name" class="col-md-2 control-label">Name</label>
297 <div class="col-md-10">
298 <input type="text" class="form-control" name="name" id="name">
299 </div>
300 </div>
301
302 <div class="form-group">
303 <label for="description" class="col-md-2 control-label">Description</label>
304 <div class="col-md-10">
305 <textarea class="form-control" rows="3" name="description" id="description"></textarea>
306 </div>
307 </div>
308
309 <button type="submit" name="createBtn" class="btn btn-success pull-right">
310 Create Task <i class="fa fa-plus"></i>
311 </button>
312 </form>
313
314 app.js
315 ------
316 $(document).ready(function(){
317 $('form#create-task').submit(function(event){
318 event.preventDefault();
319
320 var form = $(this);
321 var formData = form.serialize();
322
323 $.ajax({
324 url: 'create.php',
325 method: 'POST',
326 data: formData,
327 success: function(data){
328 $('#ajax_msg').css('display', 'block').delay(3000).slideUp(300).html(data);
329 document.getElementById('create-task').reset();
330 }
331 });
332 });
333 });
334
335 create.php
336 ----------
337 include_once 'connect.php';
338
339 if(isset($_POST['name']) && isset($_POST['description'])){
340 $name = $_POST['name'];
341 $description = $_POST['description'];
342
343 try{
344 $createQuery = "INSERT INTO tasks(name, description, created_at)
345 VALUES(:name, :description, now())";
346
347 $stmt = $conn->prepare($createQuery);
348 $stmt->execute(array(":name"=>$name, ":description"=>$description));
349
350 if($stmt){
351 echo "Record Inserted";
352 }
353
354 }catch(PDOException $ex){
355 echo "An error occurred: " . $ex->getMessage();
356 }
357 }
358
359
36016) Load php file with jQuery
361***************************************
362 $('#task-list').load('read.php');