Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #4643 > unrolled thread

Fast/Easy way to extract a column from multi-dimensional array?

Back to article view | Back to comp.lang.php

Contents

  Fast/Easy way to extract a column from multi-dimensional array? John Drako <jbravo556@gmail.removethis.com> - 2012-01-27 15:08 -0500
    Re: Fast/Easy way to extract a column from multi-dimensional array? "J.O. Aho" <user@example.net> - 2012-01-27 21:50 +0100
      Re: Fast/Easy way to extract a column from multi-dimensional array? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-01-27 22:49 +0100
    Re: Fast/Easy way to extract a column from multi-dimensional array? Thomas Mlynarczyk <thomas@mlynarczyk-webdesign.de> - 2012-01-27 21:52 +0100
      Re: Fast/Easy way to extract a column from multi-dimensional array? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2012-01-27 22:42 +0100
        Re: Fast/Easy way to extract a column from multi-dimensional array? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2012-01-27 22:55 +0100
        Re: Fast/Easy way to extract a column from multi-dimensional array? Thomas Mlynarczyk <thomas@mlynarczyk-webdesign.de> - 2012-01-27 23:49 +0100
    Re: Fast/Easy way to extract a column from multi-dimensional array? Jeff North <jnorthau@yahoo.com.au> - 2012-01-28 13:52 +1100
      Re: Fast/Easy way to extract a column from multi-dimensional array? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2012-01-28 21:28 +0100
    Re: Fast/Easy way to extract a column from multi-dimensional array? Arno Welzel <usenet@arnowelzel.de> - 2012-01-28 09:49 +0100
      Re: Fast/Easy way to extract a column from multi-dimensional array? John Drako <jbravo556@gmail.removethis.com> - 2012-01-29 13:54 -0500

#4643 — Fast/Easy way to extract a column from multi-dimensional array?

I'm looking for a way to get all the values from the column of a two 
dimensional array.

For example, I have a query on a mysql database that returns 10 rows 
from the database, I would like to quickly (read low cpu load) extract 
all the IDs from all the rows returned to reuse in another query.

So let's say I use:

$arr = mysqli_fetch_all($result, MYSQLI_ASSOC);

and $arr looks like:

RecordID, name, last name, title
RecordID, name, last name, title
RecordID, name, last name, title
RecordID, name, last name, title
RecordID, name, last name, title
RecordID, name, last name, title
etc...

I need to get all the 'RecordID' from the results to reuse in other 
queries.

Is there a built in PHP function (compiled code) to accomplish this? 
I've read the definitions of all the functions related to arrays and if 
it exists, I didn't find it.

The site is very busy and CPU cycles count. I'm trying to avoid left 
joining three gigantic tables.

[toc] | [next] | [standalone]

#4644

John Drako wrote:
> I'm looking for a way to get all the values from the column of a two
> dimensional array.
>
> For example, I have a query on a mysql database that returns 10 rows
> from the database, I would like to quickly (read low cpu load) extract
> all the IDs from all the rows returned to reuse in another query.
>
> So let's say I use:
>
> $arr = mysqli_fetch_all($result, MYSQLI_ASSOC);
>
> and $arr looks like:
>
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> etc...
>
> I need to get all the 'RecordID' from the results to reuse in other
> queries.
>
> Is there a built in PHP function (compiled code) to accomplish this?
> I've read the definitions of all the functions related to arrays and if
> it exists, I didn't find it.

There is no function which will do it, but there are ways to make it quite simple

<?php

// Assume we got the values out of the database
$arr = array(
         array('RecordID' => 1, 'name' =>'A'),
         array('RecordID' => 2, 'name' =>'B'),
         array('RecordID' => 3, 'name' =>'C'),
         array('RecordID' => 4, 'name' =>'D'),
         array('RecordID' => 5, 'name' =>'E')
);

// this is a function which will only get the RecordID
function getRecordID($item, $key, $newarray) {
         if($key == 'RecordID')
                 $newarray[]= $item;
}

// This is the array where to store the IDs
$arr_of_id = array();

// here we do fetch all the IDs
array_walk_recursive($arr, 'getRecordID',&$arr_of_id);

// Here you see we got them all
var_dump($arr_of_id);
?>


> The site is very busy and CPU cycles count. I'm trying to avoid left
> joining three gigantic tables.

I think you will need less CPU with the left join, I suggest you look at 
"having" if you want to use a smaller range in the joins.

Don't forget, if you have something you fetch often and which seldom changes, 
it can be wise to cache those somewhere (either use MySQLs built in cache or 
memcached or similar).


-- 

   //Aho

[toc] | [prev] | [next] | [standalone]

#4647

Am 27.01.2012 21:50, schrieb J.O. Aho:
> John Drako wrote:
>> I'm looking for a way to get all the values from the column of a two
>> dimensional array.
>>
>> For example, I have a query on a mysql database that returns 10 rows
>> from the database, I would like to quickly (read low cpu load) extract
>> all the IDs from all the rows returned to reuse in another query.
>>
>> So let's say I use:
>>
>> $arr = mysqli_fetch_all($result, MYSQLI_ASSOC);
>>
>> and $arr looks like:
>>
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> etc...
>>
>> I need to get all the 'RecordID' from the results to reuse in other
>> queries.
>>
>> Is there a built in PHP function (compiled code) to accomplish this?
>> I've read the definitions of all the functions related to arrays and if
>> it exists, I didn't find it.
> 
> There is no function which will do it, but there are ways to make it quite simple
> 
> <?php
> 
> // Assume we got the values out of the database
> $arr = array(
>         array('RecordID' => 1, 'name' =>'A'),
>         array('RecordID' => 2, 'name' =>'B'),
>         array('RecordID' => 3, 'name' =>'C'),
>         array('RecordID' => 4, 'name' =>'D'),
>         array('RecordID' => 5, 'name' =>'E')
> );
> 

Yes, you get an array of arrays.

> // this is a function which will only get the RecordID
> function getRecordID($item, $key, $newarray) {
>         if($key == 'RecordID')
>                 $newarray[]= $item;
> }
> 
> // This is the array where to store the IDs
> $arr_of_id = array();
> 
> // here we do fetch all the IDs
> array_walk_recursive($arr, 'getRecordID',&$arr_of_id);
> 
> // Here you see we got them all
> var_dump($arr_of_id);
> ?>
> 
> 
>> The site is very busy and CPU cycles count. I'm trying to avoid left
>> joining three gigantic tables.
> 
> I think you will need less CPU with the left join, I suggest you look at "having" if
> you want to use a smaller range in the joins.
> 
> Don't forget, if you have something you fetch often and which seldom changes, it can
> be wise to cache those somewhere (either use MySQLs built in cache or memcached or
> similar).
> 

Yes, databases are made for joining. Think twice before doing it in your script.

I am answering to your post because you write exactly what I thought when reading the
question.

/Str.

[toc] | [prev] | [next] | [standalone]

#4645

John Drako schrieb:
> I'm looking for a way to get all the values from the column of a two 
> dimensional array.

> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> etc...
> 
> I need to get all the 'RecordID' from the results to reuse in other 
> queries.

If it's the first column you're interested in:

array_map( 'current', $array )

Greetings,
Thomas

-- 
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

[toc] | [prev] | [next] | [standalone]

#4646

Thomas Mlynarczyk wrote:

> John Drako schrieb:
>> I'm looking for a way to get all the values from the column of a two
>> dimensional array.
> 
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> etc...
>> 
>> I need to get all the 'RecordID' from the results to reuse in other
>> queries.
> 
> If it's the first column you're interested in:
> 
> array_map( 'current', $array )

array_map() it is, but one should not rely on that the array cursor is at 
the first element.  Since the OP is passing MYSQLI_ASSOC, they should use

  $recordIds = array_map(
    create_function('$e', 'return $e["RecordID"]'),
    $arr
  );

for one-time use or

  public static function getRecordId($result)
  {
    return $result['RecordID'];
  }

  public function getResults()
  {
    $arr = …;

    $recordIds = array_map(array('self', 'getRecordId'), $arr);
  }

in a class.  Adjust visibility to the necessary minimum.

However, it is better to map the result of a database query to model objects 
(cf. MVC pattern):

  public function getResults()
  {
    $arr = …;

    foreach ($arr as $row)
    {
      $results[] = new Result($row);
    }

    return $results;
  }

The (example) `Result' constructor would then cause the elements of the 
array to be mapped, according to their key, to properties of `Result' 
instances, allowing for implicit range checking and type conversion through 
setters (preferably the __set() magic method calling them on access of non-
public properties from public context).


PointedEars
-- 
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee

[toc] | [prev] | [next] | [standalone]

#4648

Thomas 'PointedEars' Lahn wrote:

> array_map() it is, but one should not rely on that the array cursor is at
> the first element.  Since the OP is passing MYSQLI_ASSOC, they should use
> 
>   $recordIds = array_map(
>     create_function('$e', 'return $e["RecordID"]'),
                                                  ^
I forgot the trailing `;' in the function code.  In PHP it is mandatory.

>     $arr
>   );
> 
> for one-time use or […]


PointedEars
-- 
Sometimes, what you learn is wrong. If those wrong ideas are close to the 
root of the knowledge tree you build on a particular subject, pruning the 
bad branches can sometimes cause the whole tree to collapse.
  -- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom@94.75.214.39>

[toc] | [prev] | [next] | [standalone]

#4649

Thomas 'PointedEars' Lahn schrieb:
>> array_map( 'current', $array )
> 
> array_map() it is, but one should not rely on that the array cursor is at 
> the first element.

True. And I just remembered that the reset() function conveniently 
returns the first element of the array, thus:

array_map( 'reset', $array )

But I admit that a custom function would be more flexible.

Greetings,
Thomas


-- 
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

[toc] | [prev] | [next] | [standalone]

#4650

On Fri, 27 Jan 2012 15:08:01 -0500, in comp.lang.php John Drako
<jbravo556@gmail.removethis.com>
<jfv06v01hoa@news6.newsguy.com> wrote:

>| I'm looking for a way to get all the values from the column of a two 
>| dimensional array.
>| 
>| For example, I have a query on a mysql database that returns 10 rows 
>| from the database, I would like to quickly (read low cpu load) extract 
>| all the IDs from all the rows returned to reuse in another query.
>| 
>| So let's say I use:
>| 
>| $arr = mysqli_fetch_all($result, MYSQLI_ASSOC);
>| 
>| and $arr looks like:
>| 
>| RecordID, name, last name, title
>| RecordID, name, last name, title
>| RecordID, name, last name, title
>| RecordID, name, last name, title
>| RecordID, name, last name, title
>| RecordID, name, last name, title
>| etc...
>| 
>| I need to get all the 'RecordID' from the results to reuse in other 
>| queries.
>| 
>| Is there a built in PHP function (compiled code) to accomplish this? 
>| I've read the definitions of all the functions related to arrays and if 
>| it exists, I didn't find it.
>| 
>| The site is very busy and CPU cycles count. I'm trying to avoid left 
>| joining three gigantic tables.

Let the database do it for you - look at group_concat().

[toc] | [prev] | [next] | [standalone]

#4661

Jeff North wrote:

> John Drako wrote:
>>| I'm looking for a way to get all the values from the column of a two
>>| dimensional array.
>>| 
>>| For example, I have a query on a mysql database that returns 10 rows
>>| from the database, I would like to quickly (read low cpu load) extract
>>| all the IDs from all the rows returned to reuse in another query.
>>| 
>>| So let's say I use:
>>| 
>>| $arr = mysqli_fetch_all($result, MYSQLI_ASSOC);
>>| 
>>| and $arr looks like:
>>| 
>>| RecordID, name, last name, title
>>| RecordID, name, last name, title
>>| RecordID, name, last name, title
>>| RecordID, name, last name, title
>>| RecordID, name, last name, title
>>| RecordID, name, last name, title
>>| etc...
>>| 
>>| I need to get all the 'RecordID' from the results to reuse in other
>>| queries.
>>| 
>>| Is there a built in PHP function (compiled code) to accomplish this?
>>| I've read the definitions of all the functions related to arrays and if
>>| it exists, I didn't find it.
>>| 
>>| The site is very busy and CPU cycles count. I'm trying to avoid left
>>| joining three gigantic tables.
> 
> Let the database do it for you - look at group_concat().

GROUP_CONCAT() could only return a string of the concatenated non-NULL 
RecordIDs.  That is probably not what the OP is looking for ("reuse in
other queries").

<http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html>

I fail to see how you could suggest such error-prone and expensive an 
approach (first concatenation in MySQL, then splitting in PHP) when *the* 
solution has been presented already.

Please skip the attribution novel next time.


PointedEars
-- 
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee

[toc] | [prev] | [next] | [standalone]

#4652

John Drako, 2012-01-27 21:08:

> I'm looking for a way to get all the values from the column of a two 
> dimensional array.
> 
> For example, I have a query on a mysql database that returns 10 rows 
> from the database, I would like to quickly (read low cpu load) extract 
> all the IDs from all the rows returned to reuse in another query.
> 
> So let's say I use:
> 
> $arr = mysqli_fetch_all($result, MYSQLI_ASSOC);
> 
> and $arr looks like:
> 
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> RecordID, name, last name, title
> etc...
> 
> I need to get all the 'RecordID' from the results to reuse in other 
> queries.
[...]
> The site is very busy and CPU cycles count. I'm trying to avoid left 
> joining three gigantic tables.

You always query *all* records with every request?

And do you already use some kind of byte code cache (XCache etc.)? This
also helps a lot to reduce the CPU load and the scripts can be executed
3-4 times faster, since the interpretation will only be done once and
further requests get served by the cached byte code.


-- 
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de

[toc] | [prev] | [next] | [standalone]

#4677

On Sat, 28 Jan 2012 03:49:08 -0500, Arno Welzel wrote
(in article <4F23B684.8030602@arnowelzel.de>):

> John Drako, 2012-01-27 21:08:
> 
>> I'm looking for a way to get all the values from the column of a two 
>> dimensional array.
>> 
>> For example, I have a query on a mysql database that returns 10 rows 
>> from the database, I would like to quickly (read low cpu load) extract 
>> all the IDs from all the rows returned to reuse in another query.
>> 
>> So let's say I use:
>> 
>> $arr = mysqli_fetch_all($result, MYSQLI_ASSOC);
>> 
>> and $arr looks like:
>> 
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> RecordID, name, last name, title
>> etc...
>> 
>> I need to get all the 'RecordID' from the results to reuse in other 
>> queries.
> [...]
>> The site is very busy and CPU cycles count. I'm trying to avoid left 
>> joining three gigantic tables.
> 
> You always query *all* records with every request?
> 
> And do you already use some kind of byte code cache (XCache etc.)? This
> also helps a lot to reduce the CPU load and the scripts can be executed
> 3-4 times faster, since the interpretation will only be done once and
> further requests get served by the cached byte code.

Thank you all for the suggestions. I've learned a lot.

I've used your suggestions and I benchmarked the table joins against the 
function fetching after. One db request with a single 3-Tables join vs 
one db request following by getting all the id and then followed by two 
db requests proved that this is best done by joining the tables.

The initial database request was returning results in 0.002secs (avg), 
and after joining the three tables it was returning results in 0.0021 
seconds (avg), so the price was quite insignificant.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web