silphium Posted September 8, 2006 Share Posted September 8, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 8, 2006 Share Posted September 8, 2006 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 resultsecho '<pre>', print_r($foo, true), '</pre>';echo '<pre>', print_r($bar, true), '</pre>';?>[/code]Or[code]<?phpforeach ($categories as $data) { switch ($data[2]) { case 'foo' : $foo[] = $data; break; case 'bar' : $bar[] = $data; break; } }?>[/code] Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted September 8, 2006 Share Posted September 8, 2006 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 Link to comment Share on other sites More sharing options...
Barand Posted September 8, 2006 Share Posted September 8, 2006 But as the slowest bit of a script is the db access, why have 4 queries when 1 will do? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 8, 2006 Share Posted September 8, 2006 @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 PHPa ) still a single queryb ) more efficient query than pulling all recordsbut I wouldn't create extra queries just for the sake of using MySQL functionality. Quote Link to comment Share on other sites More sharing options...
silphium Posted September 11, 2006 Author Share Posted September 11, 2006 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 Link to comment Share on other sites More sharing options...
Jenk Posted September 11, 2006 Share Posted September 11, 2006 [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]<?phpforeach ($data as $row) { if (!isset(${$row[2]})) ${$row[2]} = array(); ${$row[2]}[] = $row;}?>[/code]If() is to avoid errors. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 11, 2006 Share Posted September 11, 2006 [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. Quote Link to comment Share on other sites More sharing options...
silphium Posted September 15, 2006 Author Share Posted September 15, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2006 Share Posted September 15, 2006 try[code]<?phpfunction 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 resultdecho '<pre>', print_r($foo, true), '</pre>';?>[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.