Roll SQL name value query into two-dimensional array [message #175199] |
Mon, 22 August 2011 01:35 |
Richard A. DeVenezia
Messages: 1 Registered: August 2011
Karma: 0
|
Junior Member |
|
|
I have a query that returns name value pairs in groups.
data
g n v
1 a 1
1 b 2
1 c 3
3 a 10
3 b 9
3 c 8
want
x[0] = array('a'=>1,'b'=>2,'c'=>3);
x[1] = array('a'=>10,'b'=>9,'c'=>8);
this is close but x is ending up empty
$q = 'select g,n,v from some_view order by g,some_seq_id';
$result = db_query($q); // in drupal
$x=array();
$map=array();
$prior_g = -1;
while ($obj = db_fetch_object($result)) {
if ($rec->g != $prior_g) {
//append a new map
$map=$array();
$x[] = $map;
$prior_g = $rec->g;
}
// fill in the map as we roll through the name value pairs within
the group
$map[$rec->n] = $rec->v;
}
print "<pre>".print_r($x,1)."</pre>";
shows
Array
(
[0] => Array
(
)
[1] => Array
(
)
)
So what is going wrong ?
|
|
|
Re: Roll SQL name value query into two-dimensional array [message #175200 is a reply to message #175199] |
Mon, 22 August 2011 02:15 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 8/21/2011 9:35 PM, Richard A. DeVenezia wrote:
> I have a query that returns name value pairs in groups.
>
> data
> g n v
> 1 a 1
> 1 b 2
> 1 c 3
> 3 a 10
> 3 b 9
> 3 c 8
>
> want
> x[0] = array('a'=>1,'b'=>2,'c'=>3);
> x[1] = array('a'=>10,'b'=>9,'c'=>8);
>
> this is close but x is ending up empty
>
> $q = 'select g,n,v from some_view order by g,some_seq_id';
> $result = db_query($q); // in drupal
>
> $x=array();
> $map=array();
> $prior_g = -1;
> while ($obj = db_fetch_object($result)) {
> if ($rec->g != $prior_g) {
> //append a new map
> $map=$array();
> $x[] = $map;
> $prior_g = $rec->g;
> }
> // fill in the map as we roll through the name value pairs within
> the group
> $map[$rec->n] = $rec->v;
> }
>
> print "<pre>".print_r($x,1)."</pre>";
>
> shows
> Array
> (
> [0] => Array
> (
> )
>
> [1] => Array
> (
> )
> )
>
>
> So what is going wrong ?
You probably want $x[$rec->g] instead of $x[] - what happens if the
sequence of $rec->g ends up as 1,2,5,9, for instance?
Also, you're creating a new array $map each time you get a new value for
g, but you're inserting a copy of $map into the array then changing the
original. So the copy in the $x array never gets any values.
Personally, I wouldn't even use $map or $prior_g (which assumes the
values will be return in sorted order, which is true here - but will it
always be?).
Also, I'm not real familiar with how Drupal does things, but how about
something like (untested):
$x=array();
while ($obj = db_fetch_object($result)) {
if (!isset($x[$rec->g])) {
$x[$rec->g] = array();
}
// fill in the map as we roll through the name value pairs within
the group
$x[$rec->g][$rec->n] = $rec->v;
}
print "<pre>".print_r($x,1)."</pre>";
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
Re: Roll SQL name value query into two-dimensional array [message #175201 is a reply to message #175199] |
Mon, 22 August 2011 02:50 |
Peter H. Coffin
Messages: 245 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Sun, 21 Aug 2011 18:35:52 -0700 (PDT), Richard A. DeVenezia wrote:
> I have a query that returns name value pairs in groups.
>
> data
> g n v
> 1 a 1
> 1 b 2
> 1 c 3
> 3 a 10
> 3 b 9
> 3 c 8
>
> want
> x[0] = array('a'=>1,'b'=>2,'c'=>3);
> x[1] = array('a'=>10,'b'=>9,'c'=>8);
>
> this is close but x is ending up empty
>
> $q = 'select g,n,v from some_view order by g,some_seq_id';
> $result = db_query($q); // in drupal
>
> $x=array();
> $map=array();
> $prior_g = -1;
> while ($obj = db_fetch_object($result)) {
> if ($rec->g != $prior_g) {
> //append a new map
> $map=$array();
So you create an empty array...
> $x[] = $map;
And tuck that into your array of objects...
> $prior_g = $rec->g;
> }
and throw away $obj ?
> // fill in the map as we roll through the name value pairs within
> the group
> $map[$rec->n] = $rec->v;
> }
>
> print "<pre>".print_r($x,1)."</pre>";
>
> shows
> Array
> (
> [0] => Array
> (
> )
>
> [1] => Array
> (
> )
> )
>
>
> So what is going wrong ?
--
For their next act, they'll no doubt be buying a firewall running under
NT, which makes about as much sense as building a prison out of
meringue.
-- Tanuki
|
|
|
Re: Roll SQL name value query into two-dimensional array [message #175206 is a reply to message #175199] |
Mon, 22 August 2011 12:21 |
Jonathan Stein
Messages: 43 Registered: September 2010
Karma: 0
|
Member |
|
|
Den 22-08-2011 03:35, Richard A. DeVenezia wrote:
> want
> x[0] = array('a'=>1,'b'=>2,'c'=>3);
> x[1] = array('a'=>10,'b'=>9,'c'=>8);
Use g as the index for x:
while ($obj = db_fetch_object($result)) $x[$obj->g][$obj->n] = $obj->v;
If you want $x to be zero indexed, use:
$x = array_values($x);
Regards
Jonathan
|
|
|