Nested queries using wpdb

Question

Im trying to display a few rows of data for each <h3><?php echo $name; ?></h3> item that appears in the loop.

With the current code it only shows one row for each item <h3><?php echo $name; ?></h3>

I have this in my functions.php file $newdb = new wpdb('login', 'pass', 'db', 'host');

<?php $result=$newdb->get_results('select tbl1.name, tbl2.col1, tbl2.col2, tbl2.col3 from tbl1, tbl2 where tbl1.name=tbl2.tbl1_name');
$names=array();
foreach($result as $row): ?>
<?php $names[$row->name][]=$row;
endforeach; ?>
<?php foreach($names as $name=>$info): ?>

    <h3><?php echo $name; ?></h3>
    <table>
       <tr><th>col1</th><th>col2</th><th>col3</th></tr>
        <?php foreach($info as $n):?>       
        <tr>
            <td><?php echo $n->col1; ?></td>
            <td><?php echo $n->col2; ?></td>
            <td><?php echo $n->col3; ?></td>    
        </tr>
        <?php endforeach; ?>
    </table>    
<?php endforeach; ?>

So the loop displays the heading followed by a few rows of records, not just one.

<h2>Name</h2>
<table>
<tr><th>col1</th><th>col2</th><th>col3</th></tr>
        col1-value1  col2-value1  col3-value1
        col1-value2  col1-value2  col1-value2
        etc.
</table>

<h2>Name</h2>
<table>
<tr><th>col1</th><th>col2</th><th>col3</th></tr>
        col1-value1  col2-value1  col3-value1
        col1-value2  col1-value2  col1-value2
        etc.
</table>

<h2>Name</h2>
<table>
<tr><th>col1</th><th>col2</th><th>col3</th></tr>
        col1-value1  col2-value1  col3-value1
        col1-value2  col2-value2  col3-value2
        etc.
</table>
.....`

The Alpha · Accepted Answer · 2012-10-23 17:17:41Z

1

It's possible using your idea but you can do it using one query like

global $wpdb;
$result=$wpdb->get_results('select a.col, b.col1, b.col2, b.col3 from a,b where a.col=b.col5');

Assume that you have tbl1 table and it has a name field and tbl2 table and it has a field for example tbl1_name which has the name value of tbl1 name field and other fields, (with no duplicates) so you can join both tables like

$result=$wpdb->get_results('select tbl1.name, tbl2.col1, tbl2.col2, tbl2.col3 from tbl1, tbl2 where tbl1.name=tbl2.tbl1_name');
$names=array();
foreach($result as $row): ?>
    $names[$row->name][]=$row;
<?php endforeach; ?>
foreach($names as $name=>$info):
    echo $name;
    ?><table><?php
    foreach($info as $n):
    ?>
            <tr>
                <td><?php echo $n->col1; ?></td>
                <td><?php echo $n->col2; ?></td>
                <td><?php echo $n->col3; ?></td>
            </tr>
    <?php
    <?php endforeach; ?>
    ?></table><?php
<?php endforeach; ?>

Also remember $wpdb is a global object and available through the template files so you dont need to use new to make an instanse, just use global keyword as I wrote in my answer.

Class Reference/wpdb and sql join and this one.

edited Oct 23, 2012 at 17:17

answered Oct 20, 2012 at 19:22

The Alpha

147k30 gold badges294 silver badges313 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

stemie Over a year ago

Thanks for your input, I tired your code but it only returns one row for each name entry from table 1 (tbl1.name). Each name entry will have a couple of rows in the html table that is returned by the foreach loop - currently it only displays one row. Im assuming there should be two queries?

stemie Over a year ago

Thanks Ive updated my question with your answer (thanks for the tip). the problem is I get 'unexpected T_ENDFOREACH' for the second last foreach (line 18 of your code).

The Alpha Over a year ago

@user1515699, sorry, it was my mistake, there was missing : after foreach statement and also $row->name should be $name. I've updated the answer, please check it again. Thanks.

Collectives™ on Stack Overflow

Nested queries using wpdb

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related