note 104189 added to mysqli-result.fetch-array

Here is a function to return an associative array with multiple columns as keys to the array. This is a rough approximation of the perl DBI->fetchall_hashref function - something I find myself using quite a bit. Given a simple mySQL table: mysql> select * from city; +----------------+----------------+------------------+------------+ | country | region | city | hemisphere | +----------------+----------------+------------------+------------+ | South Africa | KwaZulu-Natal | Durban | South | | South Africa | Gauteng | Johannesburg | South | | South Africa | Gauteng | Tshwane | South | | South Africa | KwaZulu-Natal | Pietermaritzburg | South | | United Kingdom | Greater London | City of London | North | | United Kingdom | Greater London | Wimbledon | North | | United Kingdom | Lancashire | Liverpool | North | | United Kingdom | Lancashire | Manchester | North | +----------------+----------------+------------------+------------+ *Note* - this is a simple function that makes no attempt to keep multiple values per key, so you need to specify all the unique keys you require. <?php $link = mysqli_connect("localhost", "username", "password", "test"); $result = mysqli_query($link, "select * from city"); $results_arr = fetch_all_assoc($result,array('hemisphere','country','region','city')); function fetch_all_assoc(& $result,$index_keys) { // Args : $result = mysqli result variable (passed as reference to allow a free() at the end // $indexkeys = array of columns to index on // Returns : associative array indexed by the keys array $assoc = array(); // The array we're going to be returning while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { $pointer = & $assoc; // Start the pointer off at the base of the array for ($i=0; $i<count($index_keys); $i++) { $key_name = $index_keys[$i]; if (!isset($row[$key_name])) { print "Error: Key $key_name is not present in the results output.\n"; return(false); } $key_val= isset($row[$key_name]) ? $row[$key_name] : ""; if (!isset($pointer[$key_val])) { $pointer[$key_val] = ""; // Start a new node $pointer = & $pointer[$key_val]; // Move the pointer on to the new node } else { $pointer = & $pointer[$key_val]; // Already exists, move the pointer on to the new node } } // for $i // At this point, $pointer should be at the furthest point on the tree of keys // Now we can go through all the columns and place their values on the tree // For ease of use, include the index keys and their values at this point too foreach ($row as $key => $val) { $pointer[$key] = $val; } } // $row /* free result set */ $result->close(); return($assoc); } ?> ---- Server IP: 69.147.83.197 Probable Submitter: 196.38.151.163 ---- Manual Page -- http://www.php.net/manual/en/mysqli-result.fetch-array.php Edit -- https://master.php.net/note/edit/104189 Del: integrated -- https://master.php.net/note/delete/104189/integrated Del: useless -- https://master.php.net/note/delete/104189/useless Del: bad code -- https://master.php.net/note/delete/104189/bad+code Del: spam -- https://master.php.net/note/delete/104189/spam Del: non-english -- https://master.php.net/note/delete/104189/non-english Del: in docs -- https://master.php.net/note/delete/104189/in+docs Del: other reasons-- https://master.php.net/note/delete/104189 Reject -- https://master.php.net/note/reject/104189 Search -- https://master.php.net/manage/user-notes.php