Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.
SQLite has a function to fetch all rows from a result into an array, so MySQL should too. For anyone who needs a procedural way to fetch all rows, here it is.
<?php
if (!function_exists('mysql_fetch_all'))
{
/**
* Fetches all rows from a MySQL result set as an array of arrays
*
* Requires PHP >= 4.3.0
*
* @param $result MySQL result resource
* @param $result_type Type of array to be fetched
* { MYSQL_NUM | MYSQL_ASSOC | MYSQL_BOTH }
* @return mixed
*/
function mysql_fetch_all ($result, $result_type = MYSQL_BOTH)
{
if (!is_resource($result) || get_resource_type($result) != 'mysql result')
{
trigger_error(__FUNCTION__ . '(): supplied argument is not a valid MySQL result resource', E_USER_WARNING);
return false;
}
if (!in_array($result_type, array(MYSQL_ASSOC, MYSQL_BOTH, MYSQL_NUM), true))
{
trigger_error(__FUNCTION__ . '(): result type should be MYSQL_NUM, MYSQL_ASSOC, or MYSQL_BOTH', E_USER_WARNING);
return false;
}
$rows = array();
while ($row = mysql_fetch_array($result, $result_type))
{
$rows[] = $row;
}
return $rows;
}
}
?>
To make the function more accessible, place mysql_fetch_all.func.php in a folder listed in your include_path.
mysql_fetch_array() and mysql_fetch_assoc() return one row at a time. mysql_fetch_all() returns an array of all rows, which means fewer loops to write.
Suggestion #2 - When working with SQL result rows I often use a "mapping" function - that is, the user use one of the result fields as a key of the returned array (should be unique), rather than using a simple "auto increment" array.
It sounds like it should be a separate function though ...
Looks great to me. I would leave the return value as false when no rows exist. That's what I get in my own database classes when there aren't any rows. Personal preference or habit I guess.
I chose to return an empty array rather than null or false because the function is modeled after sqlite_fetch_all() which also returns an empty array. It allows you to dump the return value directly into a foreach loop without generating a warning. If the arguments are invalid, the function will return false, but a warning will be triggered anyway, so I'm not too concerned about that.
$result_rows = false;
foreach ($result_rows as $row) ; // Warning: Invalid argument supplied for foreach()
The mapping idea is an interesting concept and would be useful for certain types of queries. However, some tables use a combination of fields as their primary key and some queries do not return distinct fields. I suppose it could be optional, though.
The idea of this function is to keep it simple.
Thanks for the input.
Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 3:48 pm, edited 1 time in total.
Jenk wrote:Why not just have the fetch_all throw and exception instead?
Maybe add a third parameter that specifies to throw an exception. The mysql_* functions don't throw exceptions (I don't think), so I don't think this should by default. But it is a possible addition.