pdo fetch

1
2<?php
3$sth = $dbh->prepare("SELECT name, colour FROM fruit");
4$sth->execute();
5
6/* Exercise PDOStatement::fetch styles */
7print("PDO::FETCH_ASSOC: ");
8print("Return next row as an array indexed by column name\n");
9$result = $sth->fetch(PDO::FETCH_ASSOC);
10print_r($result);
11print("\n");
12
13print("PDO::FETCH_BOTH: ");
14print("Return next row as an array indexed by both column name and number\n");
15$result = $sth->fetch(PDO::FETCH_BOTH);
16print_r($result);
17print("\n");
18
19print("PDO::FETCH_LAZY: ");
20print("Return next row as an anonymous object with column names as properties\n");
21$result = $sth->fetch(PDO::FETCH_LAZY);
22print_r($result);
23print("\n");
24
25print("PDO::FETCH_OBJ: ");
26print("Return next row as an anonymous object with column names as properties\n");
27$result = $sth->fetch(PDO::FETCH_OBJ);
28print $result->name;
29print("\n");
30?>
31
32

1
2It should be mentioned that this method can set even non-public properties. It may sound strange but it can actually be very useful when creating an object based on mysql result.
3Consider a User class:
4
5<?php
6class User {
7   // Private properties
8   private $id, $name;
9
10   private function __construct () {}
11
12   public static function load_by_id ($id) {
13      $stmt = $pdo->prepare('SELECT id, name FROM users WHERE id=?');
14      $stmt->execute([$id]);
15      return $stmt->fetchObject(__CLASS__);
16   }
17   /* same method can be written with the "name" column/property */
18}
19
20$user = User::load_by_id(1);
21var_dump($user);
22?>
23
24fetchObject() doesn't care about properties being public or not. It just passes the result to the object. Output is like:
25
26object(User)#3 (2) {
27  ["id":"User":private]=>
28  string(1) "1"
29  ["name":"User":private]=>
30  string(10) "John Smith"
31}
32

1 while ($row = mysqli_fetch_assoc( $result)) {}

1$dbhost = @mysql_connect($host, $user, $pass) or die('Unable to connect to server');
2
3@mysql_select_db('divebay') or die('Unable to select database');
4$search = $_GET['searchdivebay'];
5$query = trim($search);
6
7$sql = "SELECT * FROM auction WHERE name LIKE '%" . $query . "%'";
8
9
10
11if(!isset($query)){
12echo 'Your search was invalid';
13exit;
14} //line 18
15
16$result = mysql_query($trim);
17$numrows = mysql_num_rows($result);
18mysql_close($dbhost);

1$pdo = new PDO('mysql:host=$host; dbname=$database;', $user, $pass);
2$stmt = $pdo->prepare('SELECT * FROM auction WHERE name = :name');
3$stmt->bindParam(':name', $_GET['searchdivebay']);
4$stmt->execute(array(':name' => $name);

1$stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit, :offset");$stmt->execute(['limit' => $limit, 'offset' => $offset]); $data = $stmt->fetchAll();// and somewhere later:foreach ($data as $row) {    echo $row['name']."<br />\n";}