Changing the Format of Retrieved Rows in PHP Database

So far, fetch() has been returning rows from the database as combined numerically and string-indexed arrays. This makes for concise and easy interpolation of values in double-quoted strings—but it can also be problematic. Trying to remember, for example, which column from the SELECT query corresponds to element 6 in the result array can be difficult and error-prone. Some string column names might require quoting to interpolate properly. And having the PHP engine set up numeric indexes and string indexes is wasteful if you don’t need them both. Fortunately, PDO lets you specify that you’d prefer to have each result row delivered in a different way. Pass an alternate fetch style to fetch() or fetchAll() as a first argument and you get your row back as only a numeric array, only a string array, or an object.

To get a row back as an array with only numeric keys, pass PDO::FETCH_NUM as the first argument to fetch() or fetchAll(). To get an array with only string keys, use PDO::FETCH_ASSOC (remember that string-keyed arrays are sometimes called “asso­ciative” arrays).

To get a row back as an object instead of an array, use PDO::FETCH_OBJ. The object that’s returned for each row has property names that correspond to column names.

Example 8-43 shows these alternate fetch styles in action.

Example 8-43. Using a different fetch style

// With numeric indexes only, it’s easy to join the values together

$q = $db->query(‘SELECT dish_name, price FROM dishes’);

while ($row = $q->fetch(PDO::FETCH_NUM)) {

print implode(‘, ‘, $row) . “\n”;

}

// With an object, property access syntax gets you the values

$q = $db->query(‘SELECT dish_name, price FROM dishes’);

while ($row = $q->fetch(PDO::FETCH_OBJ)) {

print “{$row->dish_name} has price {$row->price} \n”;

}

If you want to use an alternate fetch style repeatedly, you can set the default for a par­ticular statement for all queries you issue on a given connection. To set the default for a statement, call setFetchMode() on your PDOStatement object, as shown in Example 8-44.

Example 8-44. Setting a default fetch style on a statement

$q = $db->query(‘SELECT dish_name, price FROM dishes’);

// No need to pass anything to fetch(); setFetchMode()

// takes care of it

$q->setFetchMode(PDO::FETCH_NUM);

while($row = $q->fetch()) {

print implode(‘, ‘, $row) . “\n”;

}

To set the default fetch style for everything, use setAttributeQ to set the PDO::ATTR_DEFAULT_FETCH_MODE attribute on your database connection, like this:

// No need to call setFetchMode() or pass anything to fetch();

// setAttribute() takes care of it

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);

$q = $db->query(‘SELECT dish_name, price FROM dishes’);

while ($row = $q->fetch()) {

print implode(‘, ‘, $row) . “\n”;

}

$anotherQuery = $db->query(‘SELECT dish_name FROM dishes WHERE price < 5’);

// Each subarray in $moreDishes is numerically indexed, too

$moreDishes = $anotherQuery->fetchAll();

Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.

Leave a Reply

Your email address will not be published. Required fields are marked *