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.
$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'));
functionfetch_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 returningwhile ($row=mysqli_fetch_array($result,MYSQLI_ASSOC)) {$pointer= &$assoc;// Start the pointer off at the base of the arrayfor ($i=0;$i
if (!isset($row[$key_name])) {
print"Error: Key$key_nameis 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 tooforeach ($rowas$key=>$val) {$pointer[$key] =$val;
}
}// $row
/* free result set */$result->close();
return($assoc);
}?>