mysql_fetch_all()

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.

Moderator: General Moderators

User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

mysql_fetch_all()

Post by McInfo »

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.

mysql_fetch_all.func.php

Code: Select all

<?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.

Example usage:

Code: Select all

<?php
header('Content-Type: text/plain');
 
include_once 'mysql_fetch_all.func.php';
 
$dbc = mysql_connect('localhost', 'root', '');
mysql_select_db('test', $dbc);
 
$query = 'SELECT * FROM `junk`';
$result = mysql_query($query, $dbc);
$rows = mysql_fetch_all($result, MYSQL_ASSOC);
 
print_r($rows);
?>

Code: Select all

Array
(
    [0] => Array
        (
            [id] => 1
            [value] => sample junk
        )
    [1] => Array
        (
            [id] => 2
            [value] => more sample junk
        )
)
Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 3:45 pm, edited 1 time in total.
spider.nick
Forum Commoner
Posts: 72
Joined: Wed Jul 15, 2009 12:22 pm
Location: Overland Park, KS

Re: mysql_fetch_all()

Post by spider.nick »

User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: mysql_fetch_all()

Post by McInfo »

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.

It simplifies this

Code: Select all

$rows = array();
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}
to

Code: Select all

$rows = mysql_fetch_all($result, MYSQL_ASSOC);
It's a cousin to sqlite_fetch_all().

Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 3:47 pm, edited 1 time in total.
spider.nick
Forum Commoner
Posts: 72
Joined: Wed Jul 15, 2009 12:22 pm
Location: Overland Park, KS

Re: mysql_fetch_all()

Post by spider.nick »

I guess I see the benefit, if you do not already have a MySQL-class to handle your database stuff.

Nick
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: mysql_fetch_all()

Post by McInfo »

Yes. If you're using an OOP solution, you don't need this.
McInfo wrote:For anyone who needs a procedural way to fetch all rows, here it is.
Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 3:47 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: mysql_fetch_all()

Post by Christopher »

Moved to Code Critique. Please suggest improvements. This will make a nice Code Snippet.
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: mysql_fetch_all()

Post by VladSun »

arborint wrote:Please suggest improvements. This will make a nice Code Snippet.
Maybe a null value should be returned in case there are no rows. Returning an empty array is an option, but I think null is more appropriate.

@McInfo - I like your coding style :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: mysql_fetch_all()

Post by VladSun »

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 ...

I.e.:

Code: Select all

....
        $rows = array();
        while ($row = mysql_fetch_assoc($result))
        {
            $rows[$row[$result_map_field]] = $row;
            // unset($rows[$row[$result_map_field]][$row[$result_map_field]) // is this necessary
        }
        return $rows;
...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_fetch_all()

Post by Benjamin »

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.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: mysql_fetch_all()

Post by McInfo »

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.

Code: Select all

$result_rows = array();
foreach ($result_rows as $row) ; // No warning

Code: Select all

$result_rows = null;
foreach ($result_rows as $row) ; // Warning: Invalid argument supplied for foreach()

Code: Select all

$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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_fetch_all()

Post by Benjamin »

I like to return false so I can write code like this:

Code: Select all

 
if (false === $results = $db->mysql_fetch_all("SELECT foo FROM bar")) {
    throw new Exception("There are no matching foos");
}
 
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: mysql_fetch_all()

Post by Christopher »

McInfo wrote:I chose to return an empty array rather than null or false because ...
I prefer array() because you can give the result to a foreach() without an error, so don't need a if().
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: mysql_fetch_all()

Post by Jenk »

astions wrote:I like to return false so I can write code like this:

Code: Select all

 
if (false === $results = $db->mysql_fetch_all("SELECT foo FROM bar")) {
    throw new Exception("There are no matching foos");
}
 
Why not just have the fetch_all throw and exception instead?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: mysql_fetch_all()

Post by Christopher »

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.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: mysql_fetch_all()

Post by Jenk »

mysql_* aren't on an object by default, either :P
Post Reply