extracting arrays from multidimensional arrays based on values

[silphium]

Data retrieved from MySQL sits in a multidimensional array: $categories. I want to loop through $categories and process records where the value in the third column is "foo". Then I want to do the same where the value in the third column is "bar". So I need to create two new arrays, $foo and $bar. But I can't seem to figure out how to do this, except by using multiple MySQL queries. It must be do-able in PHP without these extra queries (some kind of nested looping, I suppose). I've looked at functions like array_filter, but they require the first argument to be an array, but since I'm dealing with a multidimensional array, I feel like I'm stuck. Anyone? Any help greatly appreciated. ~s

[Barand]

If you want to use array_filter()
[code]<?php
$categories = array (
        array (1, 11, 'foo'),
        array (2, 22, 'bar'),
        array (3, 33, 'foo'),
        array (4, 44, 'bar'),
);

function myfilter ($var) {
    global $gFilter;
    return $var[2] == $gFilter;
}

$gFilter = 'foo';
$foo = array_filter($categories, 'myfilter');
$gFilter = 'bar';
$bar = array_filter($categories, 'myfilter');

// check results
echo '<pre>', print_r($foo, true), '</pre>';
echo '<pre>', print_r($bar, true), '</pre>';
?>[/code]

Or
[code]<?php
foreach ($categories as $data) {
    switch ($data[2]) {
    case 'foo' : $foo[] = $data; break;
    case 'bar' : $bar[] = $data; break;
    }
}
?>[/code]

[roopurt18]

Why not do something like:

[code]
$cols = Array('foo', 'bar', 'qwerty', 'wysiwyg');
foreach($cols as $col){
  $tmp_col = addslashes($col);
  $sql = "SELECT * FROM table WHERE col='{$tmp_col}'";
  $q = mysql_query($sql);
  while($record = mysql_fetch_assoc($q)){
    $Results[$col] = ProcessRow($Results[$col], $record);
  }
}
[/code]
From my experience, it's best to offload as much logic into SQL as possible.  If it provides the functionality, why not take advantage of it?

[Barand]

But as the slowest bit of a script is the db access, why have 4 queries when 1 will do?

[Barand]

@roopurt18,

OTOH, if, for example, the aim was to get counts of records for "foo" and "bar" then I would agree with you, better to use MySQL and have

[code]SELECT x, COUNT(*) FROM tablename GROUP BY x[/code]

than to retrieve all records and count them with PHP

a ) still a single query
b ) more efficient query than pulling all records

but I wouldn't create extra queries just for the sake of using MySQL functionality.

[silphium]

thank you, Barand. That's exactly what I was looking for -- almost. I neglected to mention that I don't want the keys preserved, which array_filter() does (sorry, my fault). I do have a function that resets the keys, so it's not a huge deal. But out of curiosity, is there a built-in function similar to array_filter() that does not preserve the keys?

As for MySQL queries vs. PHP, generally I prefer to minimize the number of queries.

Thanks again, ~s

[Jenk]

[quote author=roopurt18 link=topic=107396.msg430949#msg430949 date=1157754396]
Why not do something like:

[code]
$cols = Array('foo', 'bar', 'qwerty', 'wysiwyg');
foreach($cols as $col){
  $tmp_col = addslashes($col);
  $sql = "SELECT * FROM table WHERE col='{$tmp_col}'";
  $q = mysql_query($sql);
  while($record = mysql_fetch_assoc($q)){
    $Results[$col] = ProcessRow($Results[$col], $record);
  }
}
[/code]
From my experience, it's best to offload as much logic into SQL as possible.  If it provides the functionality, why not take advantage of it?
[/quote]That is only true if you can execute as much of the query as possible within a single query.

What Barand suggested is best, or perhaps:

[code]<?php
foreach ($data as $row) {
    if (!isset(${$row[2]})) ${$row[2]} = array();
    ${$row[2]}[] = $row;
}
?>[/code]

If() is to avoid errors.

[Barand]

[quote author=silphium link=topic=107396.msg432096#msg432096 date=1157985091]
thank you, Barand. That's exactly what I was looking for -- almost. I neglected to mention that I don't want the keys preserved, which array_filter() does (sorry, my fault). I do have a function that resets the keys, so it's not a huge deal. But out of curiosity, is there a built-in function similar to array_filter() that does not preserve the keys?

As for MySQL queries vs. PHP, generally I prefer to minimize the number of queries.

Thanks again, ~s
[/quote]

$a = array_values($a) will reset keys.

[silphium]

Thank you Barand -- this solution works great. I have another question about dealing with multidimensional arrays (about which I'm feeling especially dense at the moment). Using the above scenario, how do you create (or extract) arrays of entire columns regardless of value? In other words, an array $foo that contains all of the values from the entire third column? Again, a second MySQL query would be easy, but I'm trying to get my brain wrapped around how to work with multidimensional arrays in PHP. I'll bet once I see see the answer, it will be so simple that I'll kick myself for not figuring it out on my own. ;-)

FWIW, once I have array $foo, I'll want to apply array_unique(), sort(), and similar functions.

thanks, ~s

[Barand]

try
[code]<?php
function extractColumn (&$array, $n) {
    $res = array();
    foreach ($array as $data)  {
        $res[] = $data[$n];
    }
    return $res;
}


$categories = array (
        array (1, 11, 'foo'),
        array (2, 22, 'bar'),
        array (3, 33, 'foo'),
        array (4, 44, 'bar'),
);

    // first col = 0, so third col is '2'
   
$foo = extractColumn($categories, 2);

    // view resultd
echo '<pre>', print_r($foo, true), '</pre>';
?>[/code]