FUDforum: comp.lang.php » Fast/Easy way to extract a column from multi-dimensional array?

Home » Imported messages » comp.lang.php » Fast/Easy way to extract a column from multi-dimensional array?

Show: Today's Messages :: Polls :: Message Navigator

Fast/Easy way to extract a column from multi-dimensional array? [message #176831]

Fri, 27 January 2012 20:08

John Drako
Messages: 5
Registered: December 2011

Karma: 0

Junior Member

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.

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176832 is a reply to message #176831]

Fri, 27 January 2012 20:50

J.O. Aho
Messages: 194
Registered: September 2010

Karma: 0

Senior Member

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

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176833 is a reply to message #176831]

Fri, 27 January 2012 20:52

Thomas Mlynarczyk
Messages: 131
Registered: September 2010

Karma: 0

Senior Member

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)

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176834 is a reply to message #176833]

Fri, 27 January 2012 21:42

Thomas 'PointedEars'
Messages: 701
Registered: October 2010

Karma: 0

Senior Member

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

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176835 is a reply to message #176832]

Fri, 27 January 2012 21:49

M. Strobel
Messages: 386
Registered: December 2011

Karma: 0

Senior Member

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.

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176836 is a reply to message #176834]

Fri, 27 January 2012 21:55

Thomas 'PointedEars'
Messages: 701
Registered: October 2010

Karma: 0

Senior Member

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(at)94(dot)75(dot)214(dot)39>

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176837 is a reply to message #176834]

Fri, 27 January 2012 22:49

Thomas Mlynarczyk
Messages: 131
Registered: September 2010

Karma: 0

Senior Member

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)

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176838 is a reply to message #176831]

Sat, 28 January 2012 02:52

Jeff North
Messages: 58
Registered: November 2010

Karma: 0

Member

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176839 is a reply to message #176831]

Sat, 28 January 2012 08:49

Arno Welzel
Messages: 317
Registered: October 2011

Karma: 0

Senior Member

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

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176848 is a reply to message #176838]

Sat, 28 January 2012 20:28

Thomas 'PointedEars'
Messages: 701
Registered: October 2010

Karma: 0

Senior Member

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

Report message to a moderator

Re: Fast/Easy way to extract a column from multi-dimensional array? [message #176864 is a reply to message #176839]

Sun, 29 January 2012 18:54

John Drako
Messages: 5
Registered: December 2011

Karma: 0

Junior Member

On Sat, 28 Jan 2012 03:49:08 -0500, Arno Welzel wrote
(in article <4F23B684(dot)8030602(at)arnowelzel(dot)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.

Report message to a moderator

Previous Topic:	approaches to PHP-based application interface?
Next Topic:	php+html mixup in displaying multidimensional array in html tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 21:59:45 GMT 2024

Total time taken to generate the page: 0.04072 seconds