Multidimensional Assoc Array with SQL Query Results

Hi,

Is there a way to get a multidimensional associative array with the
entire result set? I would like to get a an array like this:

resultsArray['TableKey']['columnsInTable']

How can I accomplish this? Can I do something like this?

var $userArray = array(array());
for ($i=0; $i<$numResults; $i++) {
$row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
$userArray[$row['Key']] = $row;
}

This is not correct but I am hoping it is at least a starting point.
Thanks.

Kevin

"KDawg44" <KDawg44@gmail.com> wrote in message
news:0185d88d-7eca-40c5-bfe0-56fc759c95ac@a1g2000hsb.googlegroups.com...
> Hi,
>
> Is there a way to get a multidimensional associative array with the
> entire result set? I would like to get a an array like this:
>
> resultsArray['TableKey']['columnsInTable']
>
> How can I accomplish this? Can I do something like this?
>
> var $userArray = array(array());
> for ($i=0; $i<$numResults; $i++) {
> $row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
> $userArray[$row['Key']] = $row;
> }
>
> This is not correct but I am hoping it is at least a starting point.

it's not bad. what i would say in looking at it is that you only need to
initialize $userArray = array()...the inner, empty array doesn't allocate
space...it's not needed. that said, here's what i use in a mysql
implementation of an abstract db class:


public static function execute(
$sql ,
$decode = false ,
$returnNewId = false
)
{
self::$lastStatement = $sql;
$array = array();
$key = 0;
if (!($records = mysql_query($sql))){ return false; }
$fieldCount = @mysql_num_fields($records);
while ($row = @mysql_fetch_array($records, MYSQL_NUM))
{
for ($i = 0; $i < $fieldCount; $i++)
{
$value = $row[$i];
if ($decode){ $value = self::decode($value); }
$array[$key][strtoupper(@mysql_field_name($records, $i))] = $value;
}
$key++;
}
if ($returnNewId)
{
$array = array();
$array[0]['ID'] = mysql_insert_id();
}
@mysql_free_result($records);
return $array;
}

forget the 'decode' stuff since i didn't post the function. anyway, that
would be the basis for returning a single resultset. to multi-dimension it,
just make a key for the table:

$myTables['TABLE_A'] = db::execute($sql);
$myTables['TABLE_B'] = db::execute($sql);
$myTables['TABLE_C'] = db::execute($sql);

just assume that $sql reflects the proper query for each 'table'...meaning,
i'm not showing in the example above that $sql represents a new query for
each 'execute'.

hope that helps...fwiw, you're already on the right track.

cheers.

On Jul 19, 7:42*pm, "Dale" <the....@example.com> wrote:
> "KDawg44" <KDaw...@gmail.com> wrote in message
>
> news:0185d88d-7eca-40c5-bfe0-56fc759c95ac@a1g2000hsb.googlegroups.com...
>
>
>
> > Hi,
>
> > Is there a way to get a multidimensional associative array with the
> > entire result set? *I would like to get a an array like this:
>
> > resultsArray['TableKey']['columnsInTable']
>
> > How can I accomplish this? *Can I do something like this?
>
> > * * * *var $userArray = array(array());
> > for ($i=0; $i<$numResults; $i++) {
> > $row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
> > $userArray[$row['Key']] = *$row;
> > }
>
> > This is not correct but I am hoping it is at least a starting point.
>
> it's not bad. what i would say in looking at it is that you only need to
> initialize $userArray = array()...the inner, empty array doesn't allocate
> space...it's not needed. that said, here's what i use in a mysql
> implementation of an abstract db class:
>
> * public static function execute(
> * * * * * * * * * * * * * * * * * $sql * * * * * * * * ,
> * * * * * * * * * * * * * * * * * $decode * * *= false ,
> * * * * * * * * * * * * * * * * * $returnNewId = false
> * * * * * * * * * * * * * * * * )
> * {
> * * self::$lastStatement *= $sql;
> * * $array * * * * * * * *= array();
> * * $key * * * * * * * * *= 0;
> * * if (!($records = mysql_query($sql))){ return false; }
> * * $fieldCount * * * * * = @mysql_num_fields($records);
> * * while ($row = @mysql_fetch_array($records, MYSQL_NUM))
> * * {
> * * * for ($i = 0; $i < $fieldCount; $i++)
> * * * {
> * * * * $value = $row[$i];
> * * * * if ($decode){ $value = self::decode($value); }
> * * * * $array[$key][strtoupper(@mysql_field_name($records, $i))]= $value;
> * * * }
> * * * $key++;
> * * }
> * * if ($returnNewId)
> * * {
> * * * $array = array();
> * * * $array[0]['ID'] = mysql_insert_id();
> * * }
> * * @mysql_free_result($records);
> * * return $array;
> * }
>
> forget the 'decode' stuff since i didn't post the function. anyway, that
> would be the basis for returning a single resultset. to multi-dimension it,
> just make a key for the table:
>
> $myTables['TABLE_A'] = db::execute($sql);
> $myTables['TABLE_B'] = db::execute($sql);
> $myTables['TABLE_C'] = db::execute($sql);
>
> just assume that $sql reflects the proper query for each 'table'...meaning,
> i'm not showing in the example above that $sql represents a new query for
> each 'execute'.
>
> hope that helps...fwiw, you're already on the right track.
>
> cheers.

Thank you for your help. I'll give that a whirl.

Kevin

KDawg44 wrote:
> Hi,
>
> Is there a way to get a multidimensional associative array with the
> entire result set? I would like to get a an array like this:
>
> resultsArray['TableKey']['columnsInTable']
>
> How can I accomplish this? Can I do something like this?
>
> var $userArray = array(array());
> for ($i=0; $i<$numResults; $i++) {
> $row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
> $userArray[$row['Key']] = $row;
> }
>
> This is not correct but I am hoping it is at least a starting point.
> Thanks.
>
> Kevin
>

You're close, Kevin.

$userArray = array();
while ($row = mysql_fetch_assoc($resultSet))
$userArray[] = $row;

mysql_fetch_assoc is equivalent to mysql_fetch_array with MYSQL_ASSOC.

The results will now be in $userArray[0] .. [n].

$userarray[$x]['column_name'] will contain the contents of 'column_name'
for row $x. You can iterate through it with a for loop, foreach(), etc.

The only thing to remember is this can take a lot of memory - especially
if you're returning a large number of rows with a lot of data in each row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 19, 8:47*pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> KDawg44 wrote:
> > Hi,
>
> > Is there a way to get a multidimensional associative array with the
> > entire result set? *I would like to get a an array like this:
>
> > resultsArray['TableKey']['columnsInTable']
>
> > How can I accomplish this? *Can I do something like this?
>
> > * * * * var $userArray = array(array());
> > * *for ($i=0; $i<$numResults; $i++) {
> > * * * * * *$row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
> > * * * * * *$userArray[$row['Key']] = *$row;
> > * *}
>
> > This is not correct but I am hoping it is at least a starting point.
> > Thanks.
>
> > Kevin
>
> You're close, Kevin.
>
> * *$userArray = array();
> * *while ($row = mysql_fetch_assoc($resultSet))
> * * *$userArray[] = $row;
>
> mysql_fetch_assoc is equivalent to mysql_fetch_array with MYSQL_ASSOC.
>
> The results will now be in $userArray[0] .. [n].
>
> $userarray[$x]['column_name'] will contain the contents of 'column_name'
> for row $x. *You can iterate through it with a for loop, foreach(), etc..
>
> The only thing to remember is this can take a lot of memory - especially
> if you're returning a large number of rows with a lot of data in each row..
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Thanks. I was able to get this working the way I wanted. Is there a
better way to get to the data? I am making this into XML to return to
an AJAX call where I am passing in the data in an assoc array and then
processing like this:

function formatDataToXML($data) {
$XMLString = "<?xml version='1.0' encoding='utf-8'?><DataRoot>";
foreach ($data as $key => $row) {
$XMLString .= "<" . $key . ">";
foreach ($row as $col => $val) {
$XMLString .= "<" . $col . ">" . $val . "</" . $col . ">";
}
$XMLString .= "</" . $key . ">";
}
$XMLString .= "</DataRoot>";
return $XMLString;
}

Thanks.

KDawg44 wrote:
> On Jul 19, 8:47 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> KDawg44 wrote:
>>> Hi,
>>> Is there a way to get a multidimensional associative array with the
>>> entire result set? I would like to get a an array like this:
>>> resultsArray['TableKey']['columnsInTable']
>>> How can I accomplish this? Can I do something like this?
>>> var $userArray = array(array());
>>> for ($i=0; $i<$numResults; $i++) {
>>> $row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
>>> $userArray[$row['Key']] = $row;
>>> }
>>> This is not correct but I am hoping it is at least a starting point.
>>> Thanks.
>>> Kevin
>> You're close, Kevin.
>>
>> $userArray = array();
>> while ($row = mysql_fetch_assoc($resultSet))
>> $userArray[] = $row;
>>
>> mysql_fetch_assoc is equivalent to mysql_fetch_array with MYSQL_ASSOC.
>>
>> The results will now be in $userArray[0] .. [n].
>>
>> $userarray[$x]['column_name'] will contain the contents of 'column_name'
>> for row $x. You can iterate through it with a for loop, foreach(), etc.
>>
>> The only thing to remember is this can take a lot of memory - especially
>> if you're returning a large number of rows with a lot of data in each row.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> Thanks. I was able to get this working the way I wanted. Is there a
> better way to get to the data? I am making this into XML to return to
> an AJAX call where I am passing in the data in an assoc array and then
> processing like this:
>
> function formatDataToXML($data) {
> $XMLString = "<?xml version='1.0' encoding='utf-8'?><DataRoot>";
> foreach ($data as $key => $row) {
> $XMLString .= "<" . $key . ">";
> foreach ($row as $col => $val) {
> $XMLString .= "<" . $col . ">" . $val . "</" . $col . ">";
> }
> $XMLString .= "</" . $key . ">";
> }
> $XMLString .= "</DataRoot>";
> return $XMLString;
> }
>
> Thanks.
>

If that's all you're doing, you don't need to get everything into one
large array. You can use SimpleXML to build your XML as you retrieve
each row, and when you're done, write the whole works to a file. Much
easier.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================