Menu
I am using PDOStatement to query the database. Whenever I get a returned row, I want it to be fetched into an array, with the $row[0] as the key, and the subsequent elements in the row as the values.
I can, of course, write a combination of foreach loops and if conditionals to do the job, such as the below:
private static function GetMySQLResult($dbname, $sqlString) { $dbh = self::ConstructPDOObject($dbname); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $result=array(); foreach ($dbh->query($sqlString) as $row) { // the simplest case for 2 columns, // should add more to handle more columns $result[$row[0]][]=$row[1]; } return $result; } but I am looking for an existing method; is there such a method already exist?
Why reopened the question.
What is asked here is clearly the combination of PDO::FETCH_GROUP|PDO::FETCH_ASSOC. PDO::FETCH_KEY_PAIR only works with a 2 column result. But here the example is a 2 column result only to simplify the code in the question, not for all situations.
729
Introduction to the PHP fetch() method Therefore, the subsequent call to the fetch() method will return the next row from the result set. To fetch all rows from a result set one by one, you typically use the fetch() method in a while loop. The fetch() method accepts three optional parameters.
By default, PDO returns each row as an array indexed by the column name and 0-indexed column position in the row. To request a different return style, specify one of the following constants as the first parameter when you call the PDOStatement::fetch method: PDO::FETCH_ASSOC.
Return Values ¶ PDOStatement::fetchAll() returns an array containing all of the remaining rows in the result set. The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch.
The fetch_array() / mysqli_fetch_array() function fetches a result row as an associative array, a numeric array, or both.
Credits go to devdRew. Check the other question here.
Apparently, you can as stated in the answer. I checked as well and it works great.
$q = $db->query("SELECT `name` AS name, `value` AS value FROM `settings`;"); $r = $q->fetchAll(PDO::FETCH_KEY_PAIR); EDIT
This answer requires that you specify maximum 2 columns: 1 key and 1 value. If you need to retrieve more keys from the database, check the answer below and read @nullabilty's comment. For those who are lazy, here is his method:
$q->fetchAll(PDO::FETCH_UNIQUE); 
$stmt->fetchAll(PDO::FETCH_UNIQUE); albeit a bit of a hack but really the only way to do this since someone decided FETCH_KEY_PAIR should require 2 column result set's only.
Note: first column is used a key and is not returned in the result set in any other form.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
