PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Apr 25, 2017 1:34 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: mysql_fetch_all()
PostPosted: Tue Jul 21, 2009 4:02 pm 
Offline
DevNet Resident
User avatar

Joined: Wed Apr 01, 2009 1:31 pm
Posts: 1531
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
Syntax: [ Download ] [ Hide ]
<?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:
Syntax: [ Download ] [ Hide ]
<?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);
?>
Syntax: [ Download ] [ Hide ]
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.

Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Tue Jul 21, 2009 4:29 pm 
Offline
Forum Commoner

Joined: Wed Jul 15, 2009 12:22 pm
Posts: 72
Location: Overland Park, KS
And the purpose?

http://php.net/mysql_fetch_array
http://php.net/mysql_fetch_assoc

Nick


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Tue Jul 21, 2009 4:36 pm 
Offline
DevNet Resident
User avatar

Joined: Wed Apr 01, 2009 1:31 pm
Posts: 1531
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
Syntax: [ Download ] [ Hide ]
$rows = array();
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}

to
Syntax: [ Download ] [ Hide ]
$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.

Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Tue Jul 21, 2009 4:49 pm 
Offline
Forum Commoner

Joined: Wed Jul 15, 2009 12:22 pm
Posts: 72
Location: Overland Park, KS
I guess I see the benefit, if you do not already have a MySQL-class to handle your database stuff.

Nick


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Tue Jul 21, 2009 4:58 pm 
Offline
DevNet Resident
User avatar

Joined: Wed Apr 01, 2009 1:31 pm
Posts: 1531
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.

Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Tue Jul 21, 2009 6:21 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13385
Location: New York, NY, US
Moved to Code Critique. Please suggest improvements. This will make a nice Code Snippet.

_________________
(#10850)


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Wed Jul 22, 2009 7:23 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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 :)

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Wed Jul 22, 2009 7:31 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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.:
Syntax: [ Download ] [ Hide ]
....
        $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;
...

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Wed Jul 22, 2009 8:30 am 
Offline
Site Administrator
User avatar

Joined: Sun May 19, 2002 10:24 pm
Posts: 6883
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.

_________________
Image


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Wed Jul 22, 2009 8:58 am 
Offline
DevNet Resident
User avatar

Joined: Wed Apr 01, 2009 1:31 pm
Posts: 1531
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.

Syntax: [ Download ] [ Hide ]
$result_rows = array();
foreach ($result_rows as $row) ; // No warning

Syntax: [ Download ] [ Hide ]
$result_rows = null;
foreach ($result_rows as $row) ; // Warning: Invalid argument supplied for foreach()

Syntax: [ Download ] [ Hide ]
$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.

Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Wed Jul 22, 2009 3:54 pm 
Offline
Site Administrator
User avatar

Joined: Sun May 19, 2002 10:24 pm
Posts: 6883
I like to return false so I can write code like this:

Syntax: [ Download ] [ Hide ]
 
if (false === $results = $db->mysql_fetch_all("SELECT foo FROM bar")) {
    throw new Exception("There are no matching foos");
}
 

_________________
Image


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Thu Jul 23, 2009 1:49 am 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13385
Location: New York, NY, US
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)


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Thu Jul 23, 2009 10:42 am 
Offline
DevNet Master
User avatar

Joined: Mon Sep 19, 2005 6:24 am
Posts: 3587
Location: London
astions wrote:
I like to return false so I can write code like this:

Syntax: [ Download ] [ Hide ]
 
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?


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Thu Jul 23, 2009 1:45 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13385
Location: New York, NY, US
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)


Top
 Profile  
 
 Post subject: Re: mysql_fetch_all()
PostPosted: Fri Jul 31, 2009 8:19 pm 
Offline
DevNet Master
User avatar

Joined: Mon Sep 19, 2005 6:24 am
Posts: 3587
Location: London
mysql_* aren't on an object by default, either :P


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group