Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Modify the structure of multidimensional array outputed by SQL via $wpdb

The output that I need from $wpdb is easily achievable by running multiple queries within foreach. However, I am trying to avoid the same for performance issues.

I have two custom tables:

items
- item_id
- item_name

sub_items
- sub_item_id
- item_id
- sub_item_name

The query I am running:

$items_tbl = $wpdb->prefix . 'items';
$sub_items_tbl = $wpdb->prefix . 'sub_items';
$results = $wpdb->get_results($wpdb->prepare("SELECT * from $sub_items_tbl LEFT JOIN $items_tbl ON $items_tbl.item_id = $sub_items_tbl.item_id"), ARRAY_A);

This is the resultant output of the above query:

array(
    [0] => array(
        ['sub_item_id'] => 1
        ['item_id'] => 1
        ['sub_item_name'] => 'Lorem Ipsum'
        ['item_name'] => Some Item Name
    )
    [1] => array(
        ['sub_item_id'] => 2
        ['item_id'] => 1
        ['sub_item_name'] => 'Lorem Ipsum Ornare Parturient'
        ['item_name'] => Some Item Name
    )
    [2] => array(
        ['sub_item_id'] => 3
        ['item_id'] => 2
        ['sub_item_name'] => 'Lorem Ipsum Adipiscing Malesuada'
        ['item_name'] => Some Item Name
    )
    [3] => array(
        ['sub_item_id'] => 4
        ['item_id'] => 2
        ['sub_item_name'] => 'Lorem Ipsum Ligula'
        ['item_name'] => Some Item Name
    )
    [4] => array(
        ['sub_item_id'] => 5
        ['item_id'] => 2
        ['sub_item_name'] => 'Lorem Ipsum Sit Adipiscing'
        ['item_name'] => Some Item Name
    )
)

And this is the output I need:

array(
    [0] => array(
        ['item_id'] => 1
        ['item_name'] => Some Item Name
        ['sub_items'] =>[0] => array(
                            ['sub_item_id'] => 1
                            ['sub_item_name'] => 'Lorem Ipsum'
                        )
                        [1] => array(
                            ['sub_item_id'] => 2
                            ['sub_item_name'] => 'Lorem Ipsum Ornare Parturient'
                        )
    )
    [1] => array(
        ['item_id'] => 2
        ['item_name'] => Some Item Name
        ['sub_items'] =>[0] => array(
                            ['sub_item_id'] => 3
                            ['sub_item_name'] => 'Lorem Ipsum Adipiscing Malesuada'
                        )
                        [1] => array(
                            ['sub_item_id'] => 4
                            ['sub_item_name'] => 'Lorem Ipsum Ligula'
                        )
                        [2] => array(
                            ['sub_item_id'] => 5
                            ['sub_item_name'] => 'Lorem Ipsum Sit Adipiscing'
                        )
    )
)

I'd preferably want to modify the sql to give me the resultant output. However, if that cannot be achieved then how can the output be altered in PHP?

share|improve this question
2  
$wpdb->prepare() behaves similar to (s)printf(). It's completely worthless if you don't use the %s/%d parts and place your vars directly in the string... –  kaiser Nov 15 '12 at 14:06
    
@kaiser Were you referring to the vars I used for the tables? Other than that query above makes use of no other vars and hence in the above case $wpdb->prepare() seems of no use, though I have a habit of using it every time to avoid forgetting it. –  John Nov 15 '12 at 14:46
    
All your variables like $sub_items_tbl are unknown to WordPress. WP can only check against default, builtin tables. Everything else has to be considered unsafe. That's why you should place %s for every custom table name, like $sub_items_tbl. –  kaiser Nov 15 '12 at 14:59
    
@kaiser Point noted. I do not include the vars for table names as these are hard coded and never populated via a user input. Thx. –  John Nov 15 '12 at 15:18
1  
@kaiser I get your point. BTW, no arguments — It's always better to know the reason, understand it, and have your bases clear. Thx for the insight. –  John Nov 15 '12 at 16:02

1 Answer 1

up vote 2 down vote accepted

You can do this one of two ways, as far as I can see:

  1. Use PHP to combine the current results to the one you want. Basically:

    $records = array();

    foreach( $results as $result )
    {
        $record_id = $result['item_id'];
        if (empty($records[$record_id])) {
            $records[$record_id]['item_id'] = $result['item_id'];
            $records[$record_id]['item_name'] = $result['item_name'];
            $records[$record_id]['sub_items'] = array();
        }
        $records[$record_id]['sub_items'][] = array( 
           'sub_item_id' => $result['sub_item_id'],
           'sub_item_name' => $result['sub_item_name']  
        );
    }
    
  2. Other way is doing 2 queries. first one finds the main items. One you have the item ids of the main items, you do a secondary query where you get all the sub-items whose item_id is in the ones you queries before. Then, you use PHP to put it together in nested arrays, similar to the first option.

As far as I can tell, you can't really do nested results in mySQL like you want, so you'll have to do the legwork in PHP.

share|improve this answer
    
Thx for the detailed answer. The PHP solution works as intended. Your second solution would be a performance tax since queries would need to be run inside of a foreach loop — The very reason I asked this question in the first place. Again thx for the solution. –  John Nov 15 '12 at 15:21
    
Well, technically the second option only needs 2 queries. Also I made a correction in the answer. I suggest you use the new answer. –  Manny Fleurmond Nov 15 '12 at 16:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.